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