Re: 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)

On Thu, Jun 13, 2013 at 12:49 AM, K P Manoj <kpmanojpg(at)gmail(dot)com> wrote:
> 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

From the docs:

"Constraint exclusion only works when the query's WHERE clause
contains constants (or externally supplied parameters). For example, a
comparison against a non-immutable function such as CURRENT_TIMESTAMP
cannot be optimized, since the planner cannot know which partition the
function value might fall into at run time."

http://www.postgresql.org/docs/9.2/static/ddl-partitioning.html#DDL-PARTITIONING-CAVEATS

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray(dot)ru(at)gmail(dot)com