I have a query which was executing in 30 secs before one of its table parititioned and after partitioning the query is ever running.
Table that is recently partitioned is ysm_yield_session_master
.
This new table holds 4 to 5 partitions and each partition has up to ~90 million rows.
yr_yield_result
and ywd_yield_website_details
are other partitioned tables (each partition is very large, millions of rows) which were partitioned long ago.
SELECT
ovp_ota_name AS ota_name,
COALESCE(t.pending_count, 0) AS pending_count
FROM ovp_ota_vendor_profile
LEFT JOIN
(
SELECT
ovp_ovp_key as ota_key,
COUNT(*) AS pending_count
FROM ysm_yield_session_master
LEFT JOIN ywd_yield_website_details ON ysm_ysm_key=ywd_ysm_key
LEFT JOIN yr_yield_result ON yr_ywd_key=ywd_ywd_key
LEFT JOIN wm_abc ON ywd_wm_key=wm_wm_key
LEFT JOIN wmo_abc ON wmo_wm_group=wm_group
LEFT JOIN ovp_abc ON ovp_ovp_key = wmo_ovp_key
WHERE ( ysm_yield_date = 'now'::text::date
OR ysm_yield_date = 'yesterday'::text::date)
AND (GREATEST(now(), ysm_yield_time) - LEAST(now(), ysm_yield_time))
< '06:00:00'::interval
AND ( ywd_yield_date = 'now'::text::date
OR ywd_yield_date = 'yesterday'::text::date)
AND ( yr_yield_date IS NULL
OR yr_yield_date = 'now'::text::date
OR yr_yield_date = 'yesterday'::text::date)
AND yr_yr_key IS NULL
AND ovp_ovp_key IS NOT NULL
GROUP BY ovp_ovp_key) t ON ovp_ovp_key = ota_key
ORDER BY ovp_ota_name;
I still have the original table, and I see the query runs much faster when I replace the partitioned table with the old table. I compared the execution plan of the query before and after partitioning and they are very different and the one with partition is expensive.
I ensured the stats are updated after partitioning. Not sure what else I could do.
Any thoughts..?
FYI,
The total number of rows for all partitions is approx 360 million records.
The total number of rows of standalone table is approx 2 billion records (however it is much faster on this table)
Best Answer
In the new plan, you can see seq. scan on the partitioned table and it's very expensive - cost about 18mln when the total cost of the old query is only 6mln in total.
I saw the same effect when I didn't use correct conditions in where or join. What is the partition key? Can your query use only one of the partition_sub_table or not. Do you have indices on sub-tables?