Query performance

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)