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.
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)
Execution plan on old standalone table
Execution plan on new partitioned table
✓ Extra quality
ExtraProxies brings the best proxy quality for you with our private and reliable proxies
✓ Extra anonymity
Top level of anonymity and 100% safe proxies – this is what you get with every proxy package
✓ Extra speed
1,ooo mb/s proxy servers speed – we are way better than others – just enjoy our proxies!
USA proxy location
We offer premium quality USA private proxies – the most essential proxies you can ever want from USA
Our proxies have TOP level of anonymity + Elite quality, so you are always safe and secure with your proxies
Use your proxies as much as you want – we have no limits for data transfer and bandwidth, unlimited usage!
Superb fast proxy servers with 1,000 mb/s speed – sit back and enjoy your lightning fast private proxies!
99,9% servers uptime
Alive and working proxies all the time – we are taking care of our servers so you can use them without any problems
No usage restrictions
You have freedom to use your proxies with every software, browser or website you want without restrictions
Perfect for SEO
We are 100% friendly with all SEO tasks as well as internet marketing – feel the power with our proxies
Buy more proxies and get better price – we offer various proxy packages with great deals and discounts
We are working 24/7 to bring the best proxy experience for you – we are glad to help and assist you!