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
This new table holds 4 to 5 partitions and each partition has up to ~90 million rows.
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.
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)