Query performance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux