Dear friends,
I have explain analyze of two queries
explain analyze SELECT count(*) from general.rfis where 1=1 and
inquiry_type = 'BUY' and receiver_uid=1320721;
(7 ms)
http://pastebin.com/m5297e03c
explain analyze SELECT count(*) from general.rfis where 1=1 and
inquiry_type = 'BUY' and receiver_uid=1320721 generated_date >=
2251 and ;
(80 secs)
http://pastebin.com/d1e4bdea7
The table general.rfis is partitioned on generated_date and the
condition generated_date >= 2251
was added with the intention to limit the number of (date based)
partitions that would be searched
by the query using the constraint exclusion facility. however as
evident the query has become very
slow as a result of this condition (even on primed caches).
can anyone kindly explain why the result was so counter intuitive ?
In particular where is most of the (80828.438 ms) spent on the plan
http://pastebin.com/d1e4bdea7 (reference to actual line number is appreciated)
structure of a typical partition (abridged)
Table "rfi_partitions.rfis_part_2009_01"
Column | Type |
Modifiers
-----------------------+------------------------+---------------------------------------------------------------
rfi_id | integer | not null default
nextval('general.rfis_rfi_id_seq'::regclass)
sender_uid | integer | not null
receiver_uid | integer | not null
subject | character varying(100) | not null
message | text | not null
inquiry_type | character varying(50) | default
'BUY'::character varying
inquiry_source | character varying(30) | not null
generated_date | integer | not null default
general.current_date_id()
Indexes:
"rfis_part_2009_01_pkey" PRIMARY KEY, btree (rfi_id)
"rfis_part_2009_01_generated_date" btree (generated_date)
"rfis_part_2009_01_receiver_uid" btree (receiver_uid) CLUSTER
"rfis_part_2009_01_sender_uid" btree (sender_uid)
Check constraints:
"rfis_part_2009_01_generated_date_check" CHECK (generated_date >=
3289 AND generated_date <= 3319)
"rfis_part_2009_01_rfi_id_check" CHECK (rfi_id >= 12344252 AND
rfi_id <= 12681399)
Inherits: rfis
regds
rajesh kumar mallah.