On Wed, Jul 15, 2020 at 02:49:16PM -0400, Henrique Montenegro wrote:
> Any idea why the actual time is in the 3ms range? If I query that partition
> directly, like this:
>
> As you can see, the `actual_time` when querying the partition table
> directly goes to 0.002 which is almost 2000x faster.
Because querying parents of 1000s of tables is slow.
That's improved in v12. You can read a previous discussion about it here:
https://www.postgresql.org/message-id/20200223151038.GW31889@telsasoft.com
But I think you need to know more about partitioning. It doesn't magically
make things faster for you, and if you just guess, then it's likely to perform
worse for reading and/or writing.
Partitioning only helps for INSERTs if nearly all the insertions happening at a
given time go into a small number of partitions. Like inserting data
partitioned by "timestamp", where all the new data goes into a partition for
the current date. Otherwise instead of one gigantic index which doesn't fit in
shared_buffers or RAM, you have some hundreds of indexes which also don't
simultaneously fit into RAM. That doesn't help writes, and hurts planning
time.
--
Justin