Both queries are using your uid index on each of the partitions not generated_date, it's doing the generated_date with a filter on most of the partitions.
This is except for on partition part_2006_02 in the second query where it uses your generated date index - and that takes the 80 secs.
-> Index Scan using rfis_part_2006_02_generated_date on rfis_part_2006_02 rfis (cost=0.00..6.45 rows=1 width=0) (actual time=80827.207..80827.207 rows=0 loops=1)
Also the second query appears to go through a few more partitions than the first, i.e. part_2001_2004 and part_1005
--- On Wed, 11/2/09, Rajesh Kumar Mallah <mallah.rajesh@xxxxxxxxx> wrote:
> From: Rajesh Kumar Mallah <mallah.rajesh@xxxxxxxxx>
> Subject: please help with the explain analyze plan
> To: pgsql-performance@xxxxxxxxxxxxxx
> Date: Wednesday, 11 February, 2009, 10:58 AM
> 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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
