Postgresql Slow performance query after table partitioning

Question

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

Answer 1

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?