Обсуждение: Temporarily very slow planning time after a big delete

On Tue, 21 May 2019 at 12:44, Walter Smith <walter@carezone.com> wrote:
>
> We had a mysterious (to us) slowdown today that I'm hoping someone can explain just based on PG's principles of
operation.It got better by itself so it seems like it was "normal" behavior -- I just don't know what behavior it was
exhibiting.
>
> We have a table of user notifications containing about 80 million rows. It gets a lot of inserts continually, and is
cleanedup once a day. There are no updates. In all history there have been about 330 million rows created.
 
>
> Today we deleted about 15 million rows in one transaction from this table. Immediately afterwards, a particular
SELECTstarted 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
queryis what had gotten slow. The query itself was still executing in <1ms.
 

It would be good to know which version you're running here.  It
basically sounds very much like get_actual_variable_range() will be
the culprit.  Basically, if a constant value that's being used by the
planner to determine row estimates with falls outside the statistic's
histogram and a btree index exists that we can use to look up the
actual bound of the data, then we do so in that function.  If you've
just deleted a bunch of rows then that index scan may have to traverse
a bunch of dead tuples before finding that first live tuple.  This
code has changed a few times in recent times, see fccebe421 and
3ca930fc3, which is why your version is of interest.

Apart from that, if you want to confirm that's the issue and you just
want it fixed, just VACUUM the table. You should likely be doing that
anyway directly after your bulk delete.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

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

On Tue, 21 May 2019 at 14:04, Walter Smith <walter@carezone.com> wrote:
> I'm so sorry -- I meant to give the version, of course. It's 9.6.13.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3ca930fc3
has been applied since then.

It would be good if you could confirm the problem is resolved after a
vacuum.  Maybe run VACUUM VERBOSE on the table and double check
there's not some large amount of tuples that are "nonremovable".

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On Tue, May 21, 2019 at 11:12 AM Walter Smith <walter@carezone.com> wrote
> I did a VACUUM overnight and got the following. The thing that stands out to me is that one index
(index_unproc_notifications_on_notifiable_type)took 100x longer to scan than the others. That's not the index used in
theslow query, though.
 

What columns are indexed by
index_unproc_notifications_on_notifiable_type, and what are their
datatypes?

-- 
Peter Geoghegan

On Tue, May 21, 2019 at 11:16 AM Walter Smith <walter@carezone.com> wrote:
> It occurs to me that is a somewhat unusual index -- it tracks unprocessed notifications so it gets an insert and
deletefor every row, and is normally almost empty.
 

Is it a very low cardinality index? In other words, is the total
number of distinct keys rather low? Not just at any given time, but
over time?

-- 
Peter Geoghegan

On Tue, May 21, 2019 at 11:27 AM Walter Smith <walter@carezone.com> wrote:
> Very low. Probably less than ten over all time. I suspect the only use of the index is to rapidly find the
processed=falserows, so the notifiable_type value isn’t important, really. It would probably work just as well on any
othercolumn. 

This problem has been fixed in Postgres 12, which treats heap TID as a
tiebreaker column within B-Tree indexes. It sounds like you have the
right idea about how to work around the problem.

VACUUM will need to kill tuples in random locations in the low
cardinality index, since the order of tuples is unspecified between
duplicate tuples -- it is more or less random. VACUUM will tend to
dirty far more pages than is truly necessary in this scenario, because
there is no natural temporal locality that concentrates dead tuples in
one or two particular places in the index. This has a far more
noticeable impact on VACUUM duration than you might expect, since
autovacuum is throttled by delays that vary according to how many
pages were dirtied (and other such factors).

--
Peter Geoghegan