Re: Bad Query Plans on 10.3 vs 9.6

On 29 March 2018 at 18:26, Cory Tucker <cory(dot)tucker(at)gmail(dot)com> wrote:
> Hello all. I'm migrating a database from PG 9.6 to 10.3 and have noticed a
> particular query that is performing very badly compared to its 9.6
> counterpart.
>
> The plan on 9.6 v 10.3 are effectively identical except in 9.6 the planner
> decides to use an index only scan on the primary key and in 10.3 it does a
> sequential scan. The problem is the sequential scan is for a table of 75M
> rows and 25 columns so its quiet a lot of pages it has to traverse.

How certain are you that all the indexes match on each instance?

It would be useful to see psql's \d output for each table in question.

Another option for you to consider would be to get rid of the OR
clause in the query entirely and have two separate CTEs and INSERT
INTO your orphaned_matches table having SELECTed from both CTEs with a
UNION ALL. A record already deleted won't appear in the 2nd branch of
the UNION ALL result. However, that still might not fix your issue
with the index not being used, but you may find the anti-joined
version of the query is faster anyway.

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