Walter Smith <walter@carezone.com> writes:
> Today we deleted about 15 million rows in one transaction from this table.
> Immediately afterwards, a particular SELECT started running very slowly --
> 500 to 3000 ms rather than the usual <1ms.
> We did an EXPLAIN ANALYZE on this select and it was still doing an index
> scan as usual. The *planning time* for the query is what had gotten slow.
> The query itself was still executing in <1ms.
> Over the next few hours the time slowly improved, until it returned to the
> former performance. You can see a graph at https://imgur.com/a/zIfqkF5.
Were the deleted rows all at one end of the index in question?
If so, this is likely down to the planner trying to use the index to
identify the extremal live value of the column, which it wants to know
in connection with planning mergejoins (so I'm assuming your problem
query involved a join on the indexed column --- whether or not the
final plan did a mergejoin, the planner would consider this). As
long as there's a live value near the end of the index, this is pretty
cheap. If the planner has to trawl through a bunch of dead entries
to find the nearest-to-the-end live one, not so much.
Subsequent vacuuming would eventually delete the dead index entries
and return things to normal; although usually the performance comes
back all-of-a-sudden at the next (auto)VACUUM of the table. So I'm
a bit intrigued by your seeing it "gradually" improve. Maybe you
had old open transactions that were limiting VACUUM's ability to
remove rows?
We've made a couple of rounds of adjustments of the behavior to try
to avoid/reduce this penalty, but since you didn't say what PG version
you're running, it's hard to tell whether an upgrade would help.
regards, tom lane
