Thread: Bad Query Plans on 10.3 vs 9.6
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.
This is the query:
explain verbose
WITH removed AS (
DELETE FROM match m
WHERE
NOT EXISTS (
SELECT 1
FROM build.household h -- This is the table that has 70M rows and does a full table scan in 10.3
WHERE h.household_id = m.household_id
) OR (
m.property_id IS NOT NULL AND
NOT EXISTS (
SELECT 1
FROM build.property p
WHERE p.household_id = m.household_id AND p.property_id = m.property_id
)
)
RETURNING *
)
INSERT INTO orphaned_matches (household_id, account_id, candidate_id, matched_at, full_name, first_name, last_name, match_reason, property_id, owner_id)
SELECT
removed.household_id,
removed.account_id,
removed.candidate_id,
removed.created_at,
removed.full_name,
removed.first_name,
removed.last_name,
removed.match_reason,
removed.property_id,
removed.owner_id
FROM removed;
What's worse is that in 10.3, the number of rows is actually much smaller than in 9.6 because I am doing this query on a partitioned table (table name "match") with a reduced data set.
Query plans for both are attached, plus the query.
thanks
--Cory
Attachment
On 29 March 2018 at 18:26, Cory Tucker <cory.tucker@gmail.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
David Rowley <david.rowley@2ndquadrant.com> writes:
> On 29 March 2018 at 18:26, Cory Tucker <cory.tucker@gmail.com> wrote:
>> 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?
Another possibility is that 10.3 sees the index-only scan as too expensive
because it thinks most of the table isn't all-visible. Comparing
pg_class.relallvisible values might be informative.
regards, tom lane
Another possibility is that 10.3 sees the index-only scan as too expensive
because it thinks most of the table isn't all-visible. Comparing
pg_class.relallvisible values might be informative.
regards, tom lane
I'm happy to try to dig into this one more, however, I'm not familiar with this value. What should I be looking for here?
'On 30 March 2018 at 03:21, Cory Tucker <cory.tucker@gmail.com> wrote: >> Another possibility is that 10.3 sees the index-only scan as too expensive >> because it thinks most of the table isn't all-visible. Comparing >> pg_class.relallvisible values might be informative. > I'm happy to try to dig into this one more, however, I'm not familiar with > this value. What should I be looking for here? Each table in your database has an entry in the pg_class table. Something like: SELECT relallvisible from pg_class where oid = 'build.household'::regclass; would show you the value, however, I think a problem here is unlikely since that would just control the likelihood of an index-only-scan vs an index-scan. You're getting a Seq-scan, which I imagine is going to be quite a bit more expensive than even an index scan. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Each table in your database has an entry in the pg_class table. Something like:
SELECT relallvisible from pg_class where oid = 'build.household'::regclass;
would show you the value, however, I think a problem here is unlikely
since that would just control the likelihood of an index-only-scan vs
an index-scan. You're getting a Seq-scan, which I imagine is going to
be quite a bit more expensive than even an index scan.
relallvisible has a value of 0 for that table on both databases.
Cory Tucker <cory.tucker@gmail.com> writes:
> relallvisible has a value of 0 for that table on both databases.
That would result in IOS being estimated at the same cost as a regular
indexscan, I believe, or very close to that anyway.
Is the 10.3 plan parallelized at all? It's possible that the planner
thinks a parallel seqscan is faster than a nonparallel indexscan
(AFAIR, v10 doesn't have parallel indexscan).
The other likely explanation is simply that indexscanning a partitioned
table is not considered, or not correctly costed. I'm not very sure what
the state of that code is, but certainly all the v10 partitioning logic is
still pretty wet behind the ears.
regards, tom lane
Is the 10.3 plan parallelized at all? It's possible that the planner
thinks a parallel seqscan is faster than a nonparallel indexscan
(AFAIR, v10 doesn't have parallel indexscan).
Its not because its a delete operation, which doesn't support parallel anything. Interestingly, the plain select version of the same tries to do a parallel sequential scan on household instead of using the index.
Also, If I change the query to be just regular INNER JOIN with "match" as the driving table, it still does a parallel sequential scan. However, if I change the INNER JOIN to have "household" as the driving table then it will do an index only scan of household. That unfortunately does not help me because I am doing a NOT EXISTS clause and I cannot think of a way to change the query to support that.
The other likely explanation is simply that indexscanning a partitioned
table is not considered, or not correctly costed. I'm not very sure what
the state of that code is, but certainly all the v10 partitioning logic is
still pretty wet behind the ears.
regards, tom lane
Could be the partitioning code path, however, I am actually querying the child partition tables directly (which have no further child partitions), so I'd assume it would be the normal code flow. To test, I created another table from the results of the select of the child partitioned table and the query plan looks exactly the same.