Hi All One of my query treating performance issue on my production server. Once i run query on my parent table with specific condition(hard coded value) its uses only proper child table and its index on explain plan , but once i am using table conditions (instead of hard coded value), query planner is going all the child tables, Can i know where i am worng
Postgresql version 9.2.2 Please find details below ========================== XXX_db=> select id from xxx where d_id = '5'; id ------- 5 45 (2 rows) XXX_db=> explain analyze SELECT * FROM xxx_parent_table WHERE id in (5,45) and ( sts = 1 or status is null ) order by creation_time limit 40 ; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- - Limit (cost=12.21..12.21 rows=3 width=251) (actual time=6.585..6.585 rows=0 loops=1) -> Sort (cost=12.21..12.21 rows=3 width=251) (actual time=6.582..6.582 rows=0 loops=1) Sort Key: public.xxx_parent_tables.creation_time Sort Method: quicksort Memory: 25kB -> Result (cost=0.00..12.18 rows=3 width=251) (actual time=6.571..6.571 rows=0 loops=1) -> Append (cost=0.00..12.18 rows=3 width=251) (actual time=6.569..6.569 rows=0 loops=1) -> Seq Scan on xxx_parent_tables (cost=0.00..0.00 rows=1 width=324) (actual time=0.003..0.003 rows=0 loops=1) Filter: ((id = ANY ('{5,45}'::bigint[])) AND ((status = 1) OR (status IS NULL))) -> Bitmap Heap Scan on xxx_parent_tables_table_details_ xxx_parent_tables (cost=4.52..6.53 rows=1 width=105) (actual ti me=0.063..0.063 rows=0 loops=1) Recheck Cond: ((status = 1) OR (status IS NULL)) Filter: (id = ANY ('{5,45}'::bigint[])) -> BitmapOr (cost=4.52..4.52 rows=1 width=0) (actual time=0.059..0.059 rows=0 loops=1) -> Bitmap Index Scan on xxx_parent_tables_table_details__status_idx (cost=0.00..2.26 rows=1 width=0) (actual time=0.038..0.038 rows=0 loops=1) Index Cond: (status = 1) -> Bitmap Index Scan on xxx_parent_tables_table_details__status_idx (cost=0.00..2.26 rows=1 width=0) (actual time=0.019..0.019 rows=0 loops=1) Index Cond: (status IS NULL) -> Bitmap Heap Scan on xxx_parent_tables_table_details_det xxx_parent_tables (cost=2.52..5.65 rows=1 width=324) (actual ti me=6.502..6.502 rows=0 loops=1) Recheck Cond: (id = ANY ('{5,45}'::bigint[])) Filter: ((status = 1) OR (status IS NULL)) -> Bitmap Index Scan on xxx_parent_tables_table_details_id_idx (cost=0.00..2.52 rows=2 width=0) (actua l time=6.499..6.499 rows=0 loops=1) Index Cond: (id = ANY ('{5,45}'::bigint[])) Total runtime: 6.823 ms (22 rows) XXX_db => explain analyze SELECT * FROM xxx_parent_tables WHERE cp_id in (select id from xxx where d_id = '5') and ( status = 1 or status is null ) order by creation_time limit 40 ; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------ Limit (cost=3.66..6067.89 rows=40 width=105) (actual time=70479.596..70479.596 rows=0 loops=1) -> Nested Loop Semi Join (cost=3.66..4587291.92 rows=30258 width=105) (actual time=70479.593..70479.593 rows=0 loops=1) Join Filter: (public.xxx_parent_tables.cp_id = cp_info.cp_id) Rows Removed by Join Filter: 1416520 -> Merge Append (cost=3.66..4565956.68 rows=711059 width=105) (actual time=67225.964..69635.016 rows=708260 loops=1) Sort Key: public.xxx_parent_tables.creation_time -> Sort (cost=0.01..0.02 rows=1 width=324) (actual time=0.018..0.018 rows=0 loops=1) Sort Key: public.xxx_parent_tables.creation_time Sort Method: quicksort Memory: 25kB -> Seq Scan on xxx_parent_tables (cost=0.00..0.00 rows=1 width=324) (actual time=0.011..0.011 rows=0 loops=1) Filter: ((status = 1) OR (status IS NULL)) -> Index Scan using xxx_parent_tables_automobiles_carwale_creation_time_idx on xxx_parent_tables_automobiles_carwale xxx_parent_tables (co st=0.00..649960.44 rows=17 width=105) (actual time=10219.559..10219.559 rows=0 loops=1) Filter: ((status = 1) OR (status IS NULL)) Rows Removed by Filter: 3102241 -> Index Scan using xxx_parent_tables_automobiles_sulekha_creation_time_idx on xxx_parent_tables_automobiles_sulekha xxx_parent_tables (co st=0.00..1124998.57 rows=1 width=105) (actual time=17817.577..17817.577 rows=0 loops=1) Filter: ((status = 1) OR (status IS NULL)) Rows Removed by Filter: 4016234 -> Index Scan using xxx_parent_tables_automobiles_verse_creation_time_idx on xxx_parent_tables_automobiles_verse xxx_parent_tables (cost=0 .00..24068.88 rows=1 width=103) (actual time=675.291..675.291 rows=0 loops=1) Filter: ((status = 1) OR (status IS NULL)) Rows Removed by Filter: 420616 -> Index Scan using xxx_parent_tables_automobiles_yolist_creation_time_idx on xxx_parent_tables_automobiles_yolist xxx_parent_tables (cost =0.00..25.05 rows=2 width=324) (actual time=0.016..0.016 rows=0 loops=1) Filter: ((status = 1) OR (status IS NULL)) -> Index Scan using xxx_parent_tables_deals_bagittoday_creation_time_idx on xxx_parent_tables_deals_bagittoday xxx_parent_tables (cost=0.0 0..23882.78 rows=1 width=105) (actual time=234.672..234.672 rows=0 loops=1) Filter: ((status = 1) OR (status IS NULL)) Rows Removed by Filter: 84988 -> Index Scan using xxx_parent_tables_deals_bindaasbargain_creation_time_idx on xxx_parent_tables_deals_bindaasbargain xxx_parent_tables ( cost=0.00..25.05 rows=2 width=324) (actual time=0.016..0.016 rows=0 loops=1) Filter: ((status = 1) OR (status IS NULL)) -> Index Scan using xxx_parent_tables_deals_buzzr_creation_time_idx on xxx_parent_tables_deals_buzzr xxx_parent_tables (cost=0.00..11435.4 1 rows=1 width=105) (actual time=109.466..109.466 rows=0 loops=1) Filter: ((status = 1) OR (status IS NULL)) Rows Removed by Filter: 33750 -> Index Scan using xxx_parent_tables_deals_dealdrums_creation_time_idx on xxx_parent_tables_deals_dealdrums xxx_parent_tables (cost=0.00. .51.61 rows=1 width=105) (actual time=0.917..0.917 rows=0 loops=1) Filter: ((status = 1) OR (status IS NULL)) Rows Removed by Filter: 941 -> Index Scan using xxx_parent_tables_deals_dealsandyou_creation_time_idx on xxx_parent_tables_deals_dealsandyou xxx_parent_tables (cost=0 .00..25.05 rows=2 width=324) (actual time=0.012..0.012 rows=0 loops=1) Filter: ((status = 1) OR (status IS NULL)) -> Index Scan using xxx_parent_tables_deals_foodiebay_creation_time_idx on xxx_parent_tables_deals_foodiebay xxx_parent_tables (cost=0.00. .25.05 rows=2 width=324) (actual time=0.024..0.024 rows=0 loops=1) Filter: ((status = 1) OR (status IS NULL)) -> Index Scan using xxx_parent_tables_deals_futurebazaar_creation_time_idx on xxx_parent_tables_deals_futurebazaar xxx_parent_tables (cost =0.00..30.37 rows=1 width=109) (actual time=0.348..0.348 rows=0 loops=1) Filter: ((status = 1) OR (status IS NULL)) -> Index Scan using xxx_parent_tables_jobs_jobsa1_creation_time_idx on xxx_parent_tables_jobs_jobsa1 xxx_parent_tables (cost=0.00..25.05 r ows=2 width=324) (actual time=0.020..0.020 rows=0 loops=1) Filter: ((status = 1) OR (status IS NULL)) -> Index Scan using xxx_parent_tables_jobs_jobsinnigeria_creation_time_idx on xxx_parent_tables_jobs_jobsinnigeria xxx_parent_tables (cost =0.00..25.05 rows=2 width=324) (actual time=0.013..0.013 rows=0 loops=1) Filter: ((status = 1) OR (status IS NULL)) -> Index Scan using xxx_parent_tables_jobs_khojle_creation_time_idx on xxx_parent_tables_jobs_khojle xxx_parent_tables (cost=0.00..25.05 r ows=2 width=324) (actual time=0.013..0.013 rows=0 loops=1) Filter: ((status = 1) OR (status IS NULL)) -> Index Scan using xxx_parent_tables_jobs_midday_creation_time_idx on xxx_parent_tables_jobs_midday xxx_parent_tables (cost=0.00..25.05 r ows=2 width=324) (actual time=0.011..0.011 rows=0 loops=1) Filter: ((status = 1) OR (status IS NULL)) -> Index Scan using xxx_parent_tables_jobs_monsterindia_creation_time_idx on xxx_parent_tables_jobs_monsterindia xxx_parent_tables (cost=0 .00..31569.68 rows=81849 width=105) (actual time=279.393..544.467 rows=78622 loops=1) Filter: ((status = 1) OR (status IS NULL)) Rows Removed by Filter: 155151 -> Index Scan using xxx_parent_tables_jobs_mprc_creation_time_idx on xxx_parent_tables_jobs_mprc xxx_parent_tables (cost=0.00..25.05 rows= 2 width=324) (actual time=0.016..0.016 rows=0 loops=1) Filter: ((status = 1) OR (status IS NULL)) -> Index Scan using xxx_parent_tables_jobs_myjobsintanzania_creation_time_idx on xxx_parent_tables_jobs_myjobsintanzania xxx_parent_tables (cost=0.00..25.05 rows=2 width=324) (actual time=0.012..0.012 rows=0 loops=1) Filter: ((status = 1) OR (status IS NULL)) -> Index Scan using xxx_parent_tables_mobiles_verse_creation_time_idx on xxx_parent_tables_mobiles_verse xxx_parent_tables (cost=0.00..25. 05 rows=2 width=324) (actual time=0.015..0.015 rows=0 loops=1) Filter: ((status = 1) OR (status IS NULL)) -> Index Scan using xxx_parent_tables_mobileseeker_quikr_creation_time_idx on xxx_parent_tables_mobileseeker_quikr xxx_parent_tables (cost =0.00..13.30 rows=1 width=105) (actual time=0.111..0.111 rows=0 loops=1) Filter: ((status = 1) OR (status IS NULL)) Rows Removed by Filter: 61 Filter: ((status = 1) OR (status IS NULL)) -> Materialize (cost=0.00..3.47 rows=2 width=8) (actual time=0.000..0.000 rows=2 loops=708260) -> Seq Scan on cp_info (cost=0.00..3.46 rows=2 width=8) (actual time=0.028..0.060 rows=2 loops=1) Filter: (domain_id = 5::bigint) Rows Removed by Filter: 115 Total runtime: 70481.560 ms (xxx rows)