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.
