Greetings Postgres Developers,
I've recently started taking advantage of the PARTITION BY HASH feature for
my database system. It's a really great fit since my tables can get quite
large (900M+ rows for some) and splitting them up into manageable chunks
should let me upload to them without having to update an enormous index
every time. What's more, since each partition has a write lock independent
of the parent table, it should theoretically be possible to perform a
parallelized insert operation, provided the data to be added is partitioned
beforehand.
What has been disappointing is that the query planner doesn't seem to
recognize this potential. For example, if I have a large list of input
data, and I want to perform a select operation across the target table:
-- target table is hashed on 'textfield' & has a unique index on
'textfield'
select * from temp_data td left join target tg on td.textfield =
tg.textfield;
I would expect to get a query plan like this:
partition temp_data
parallel scan on
target_p0 using target_p0_textfield_uniq_idx against temp_data_p0
target_p1 using target_p1_textfield_uniq_idx against temp_data_p1
target_p2 using target_p2_textfield_uniq_idx against temp_data_p2
...
Instead, I get a seemingly terrible plan like this:
hash temp_data
sequential scan on
target_p0 against temp_data
target_p1 against temp_data
target_p2 against temp_data
...
It doesn't even make use of the index on the textfield! Instead, it opts to
hash all of temp_data and perform a sequential scan against it.
It doesn't help if I partition temp_data by textfield beforehand either
(using the same scheme as the target table). It still opts to concatenate
all of temp_data, hash it, then perform a sequential scan against the
target partitions.
On insert the behaviour is better but it still opts for a sequential insert
instead of a parallel one.
Does the query planner know something I don't? It's my intuition that it
should be faster to do a rough counting sort (partition by hash) first, and
then do N smaller more accurate sorts in parallel afterwards.
Currently I am creating a custom script(s) to emulate my desired behaviour,
but it would be nice if there was a way to get the query planner to do this
automatically. Any tricks to do this would be much appreciated!
-Ben
