Postgresql – Adding a CHECK constraint doesn’t improve performance of ATTACH PARTITION on postgres table

ddlpartitioningperformancepostgresqlpostgresql-performance

I'm attempting to merge a table into a partition, the merge works but the performance isn't good. I've read this link, in particular this quote….

Before running the ATTACH PARTITION command, it is recommended to create a CHECK constraint on the table to be attached describing the desired partition constraint. That way, the system will be able to skip the scan to validate the implicit partition constraint. Without such a constraint, the table will be scanned to validate the partition constraint while holding an ACCESS EXCLUSIVE lock on the parent table. One may then drop the constraint after ATTACH PARTITION is finished, because it is no longer necessary.

Here's the SQL I'm using….

ALTER TABLE mess_201811 ADD CONSTRAINT const_201811 CHECK ( loadedtime >=  DATE '2018-11-01' AND loadedtime < DATE '2018-11-23' );
ALTER TABLE mess ATTACH PARTITION mess_201811 FOR VALUES FROM ('2018-11-01') TO ('2018-11-23'); 
ALTER TABLE mess_201811 DROP CONSTRAINT const_201811 ;

As close as possible I've copied my SQL from the link mentioned. The three commands all work without problem, the issue is the time it takes. Adding the constraint takes about 60 seconds (fair enough, there are about 2 million records in the table). However the ATTACH PARTITION command also takes about 60 seconds, this suggests to me that table is being scanned a second time validate the partition. My understand was that with the CHECK constraint in place the ATTACH should be a simple DDL statement and would only take a fraction of a second.

The problem is that ATTACH PARTITION requires an exclusive lock on the table mess which prevents inserting records for 60 seconds.

I'm using postgres v10.5. the definition for table mess ends with

PARTITIONED BY loadedtime;

At the time of my testing there were no other users on the database and no lock issues.

Following jjanes solution, I've checked the original documentation, the example quoted does use a NOT NULL field constraint although it make no mention of this being mandatory. I'm hoping that the additional constraint won't have a noticeable effect on normal inserts to this table.

Best Answer

I can only reproduce this if mess_201811.loadedtime is nullable, in which case it has to be scanned for NULL values upon attachment. If the column is NOT NULL, then the scan upon attachment is skipped.

Related Solutions

Mysql – Why is MySQL order by performance poor within a single partition of a partitioned table

Assuming that you range partition your table by the (auto incrementing) sequence and you limit your query by another value (day number), you might see, that a lot of partitions will be touched when you use:

EXPLAIN PARTITIONS SELECT * FROM table_name WHERE day_number=X ORDER BY...

The reason for that is, that there is no linkage between the partitioning attribute and the data filter. Since MySQL uses a different index for each partition, it needs to open all partitions to find all data matching day_number.

If you know the partition name, maybe by querying INFORMATION_SCHEMA.PARTITIONS before the real select, and you use MySQL-5.6+, you can use partition selection like

SELECT * FROM table_name PARTITION(partition_name) WHERE day_number=X ORDER BY...

But this is just chitchat based on a lot of assumtions. To give you a real answer, you need to post the query plan and more information about table structure - like Phil said before.

Postgresql – Slow window function query with big table

Is there any way I can speed this up?

Yes. Don't use a varchar column for an integer number. Use integer or bigint if you burn that many IDs - much smaller in table and index and faster to process. Since you are ranking 10 million rows in your test, this is going to make a substantial difference.

~~player_id VARCHAR(200) NOT NULL,~~
player_id int NOT NULL,

Or a uuid if you must (I doubt that):

Postgres uuid: Use as primary key, or in addition to SERIAL -for disconnected app-

Your query ranks 10 million rows. This is going to take some time, even when read from the index directly and no sort step.

Side note: If you bulk-insert rows first and add index and PK constraint (and FK constraint) after, that's going to be much faster, plus you get perfect indexes without bloat without running REINDEX or VACUUM FULL. Do make sure ANALYZE has been run on the table before testing performance, though.

What you didn't ask

.. but, going out on a limb here, what are probably looking for.

The EXPLAIN output reveals that you filter the top 20 rows: (t.rank <= 20). Your presented query does not show that. The query actually matching your EXPLAIN output would be:

SELECT * FROM (
   SELECT player_id
        , rank() OVER (ORDER BY value DESC, player_id ASC) AS rank
   FROM   player_stat
   WHERE  stat_id = 1
   ) t
WHERE t.rank <= 20;

Which can be improved dramatically:

SELECT row_number() OVER (ORDER BY value DESC, player_id ASC) AS rank
     , player_id
FROM   player_stat
WHERE  stat_id = 1
ORDER  BY value DESC, player_id
LIMIT  20;

Explanation

The important part for performance is the LIMIT clause in combination with ORDER BY matching the index: now the query reads exactly 20 rows from the top to the index, where it had to read 10000000 in your original version. We only use player_id and value, so we can still have an index-only scan. The rest is peanuts.
That's all due to the sequence of events in a SELECT query: window functions are applied before LIMIT. Only if the sort order agrees, we don't have to consider the rest of the applicable 10000000 rows.
We can use LIMIT 20 because the top 20 ranks are guaranteed to span no more than 20 rows. The PK on (player_id, stat_id) guarantees unique player_id per stat_id and since that is included in the ORDER BY, each rank is only assigned once - which also means we can use the slightly cheaper row_number() instead.