Re: Understanding query planner cpu usage

Lucas Fairchild-Madar <lucas(dot)madar(at)gmail(dot)com> writes:
> I'm having an perplexing issue in PG 10.1 wherein deleting a large amount
> of rows from a table causes query planning time to spike dramatically for a
> while. This happens with or without autovacuums so vacuuming isn't the
> issue.

Would the deleted rows happen to be the extremal values of some indexed
column that is a join key in the slowly-planned queries?

If so, this might be some manifestation of a problem we've seen before:
the planner tries to find out the current live max value of the column
by scanning the index, and that's really slow if there are a lot of
recently-dead entries at the index end, because each of them has to be
inspected and then hinted dead. You'd pay that overhead at some point
anyway, of course. The cases where it becomes a problem are where the
planner inspects these values but *can't* hint them dead, such as when
the deletion hasn't committed yet, or they're newly inserted rows that
likewise aren't committed. Then each incoming query does the work
over again until the transient state is resolved.

We've done various things to ameliorate this, but maybe you've found
some new way to cause it to be a pain point. Is there anything special
about the way you're deleting the rows? Maybe there's a long-running
transaction in the background that can still see the deleted rows?

Or I might be barking up the wrong tree entirely. But this sure
sounds reminiscent of that class of problems.

regards, tom lane