Postgresql Slow performance query after table partitioning

partitioningperformancepostgresqlpostgresql-performance

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)

Execution plan on old standalone table

Execution plan on new partitioned 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?