From: | Justin Pryzby <pryzby(at)telsasoft(dot)com> |
---|---|
To: | Henrique Montenegro <typoon(at)gmail(dot)com> |
Cc: | Sebastian Dressler <sebastian(at)swarm64(dot)com>, Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Sudden insert performance degradation |
Date: | 2020-07-15 20:03:26 |
Message-ID: | 20200715200326.GE23581@telsasoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Vishwa Kalyankar | 2020-07-16 15:43:45 | Same query taking less time in low configuration machine |
Previous Message | Henrique Montenegro | 2020-07-15 18:49:16 | Re: Sudden insert performance degradation |