Skip site navigation (1) Skip section navigation (2)

Re: please help with the explain analyze plan

From: Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk>
To: pgsql-performance(at)postgresql(dot)org, Rajesh Kumar Mallah <mallah(dot)rajesh(at)gmail(dot)com>
Subject: Re: please help with the explain analyze plan
Date: 2009-02-11 11:55:54
Message-ID: 423333.16218.qm@web23604.mail.ird.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(dot)rajesh(at)gmail(dot)com> wrote:

> From: Rajesh Kumar Mallah <mallah(dot)rajesh(at)gmail(dot)com>
> Subject: [PERFORM] please help with the explain analyze plan
> To: pgsql-performance(at)postgresql(dot)org
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance


      

In response to

Responses

pgsql-performance by date

Next:From: Rajesh Kumar MallahDate: 2009-02-11 12:37:55
Subject: Re: please help with the explain analyze plan
Previous:From: Rajesh Kumar MallahDate: 2009-02-11 10:58:00
Subject: please help with the explain analyze plan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group