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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
