Postgres 11 execution plan - Querying partitioned table without partition key in where clause

Question

I am trying out partitioning for a table(PRT_T1) with size > 1 TB. I have chosen 2 partition keys - entity_id_1 and entity_id_2 with hash partitioning. I want to understand postgres behavior when both partition keys are not part of where clause, or let's say only one of the partition key is part of where clause.

I checked explain plan for -

select * 
from PRT_T1 as T1 
where T1.entity_id_1=173.

Note that there is index on entity_id_1 and entity_id_2 columns The execution plan shows all partitions were scanned using Bitmap Heap Scan first and then BitMap Index scan. I've attached the screenshot of same.

Question is are these partitions scanned sequentially or parallely?

Please proof read your question. You have listed entity_id_1 as being part of the partition key (twice!) and also as being not part of the partition key. It is hard to figure out what you are really asking here. — jjanes
– jjanes, Commented Jul 2, 2020 at 22:54

Laurenz Albe · Accepted Answer · 2020-07-03 06:18:19Z

2

Hash partitioning is pretty useless unless you want to distribute I/O load randomly across multiple tablespaces or you want to improve autovacuum's performance on that table.

Any query that does not have the complete partitioning key with an equality operator in the WHERE condition will have to scan all partitions. No query can become more efficient with hash partitioning than without partitioning.

All these partitions are scanned sequentially. If a PostgreSQL execution plan uses parallel query, you can always tell by the presence of a “Gather” node.

edited Jul 3, 2020 at 6:18

answered Jul 3, 2020 at 5:53

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bhushan Over a year ago

Distributing I/O is one of objective. For legacy reasons table doesn't have timestamps that I could use for range partitioning. List partitioning is NA in my case as well, so this is the only option left to explore, depending upon performance benefit of partitioning compared to non-partitioned will take a call. Thanks about "Gather" node info was not aware. Is there any way I can influence to make these partition scans go parallel?

Laurenz Albe Over a year ago

I don't think you can. There is force_parallel_mode - you can play with it and see what you get.

Collectives™ on Stack Overflow

Postgres 11 execution plan - Querying partitioned table without partition key in where clause

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related