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?

Answer 1

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.