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
