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

Re: [PGSQL 8.3.5] Use of a partial indexes

From: "Picavet Vincent" <Vincent(dot)Picavet(at)mediapost(dot)fr>
To: "Reg Me Please" <regmeplease(at)gmail(dot)com>,"Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
Cc: "Scott Ribe" <scott_ribe(at)killerbytes(dot)com>,"Gauthier, Dave" <dave(dot)gauthier(at)intel(dot)com>,<pgsql-general(at)postgresql(dot)org>
Subject: Re: [PGSQL 8.3.5] Use of a partial indexes
Date: 2008-12-30 16:20:05
Message-ID: EB18254270D1FD429047C987937D4A1203AA8599@s92e07497.ad.mediapost.fr (view raw or flat)
Thread:
Lists: pgsql-general
Hello,
Why do you index a boolean of your condition ?
Isn't it better to have the partial index like the following ?
:
CREATE INDEX i_gm_t_movimenti_magazzini_partial
  ON gm_t_movimenti_magazzini( ap_prod_unic )
  WHERE gm_moma_flag AND gm_moma_vali>='2008-01-01'::TIMESTAMPTZ;

Hope this helps,
Vincent


> -----Message d'origine-----
> De : pgsql-general-owner(at)postgresql(dot)org 
> [mailto:pgsql-general-owner(at)postgresql(dot)org] De la part de Reg 
> Me Please
> Envoyé : mardi 30 décembre 2008 17:09
> À : Scott Marlowe
> Cc : Scott Ribe; Gauthier, Dave; pgsql-general(at)postgresql(dot)org
> Objet : Re: [GENERAL] [PGSQL 8.3.5] Use of a partial indexes
> 
> Here it comes:
> 
> -- DDL
> 
> CREATE TABLE gm_t_movimenti_magazzini
> (
>   gm_movi_unic INT8 NOT NULL REFERENCES gm_t_movimenti,
>   gm_moti_unic TEXT NOT NULL REFERENCES gm_t_movimenti_tipi,
>   ap_prod_unic INT8 NOT NULL REFERENCES ap_t_prodotti,
>   gm_maga_unic TEXT NOT NULL REFERENCES gm_t_magazzini,
>   gm_moma_qnta NUMERIC NOT NULL,
>   gm_moma_flag BOOL NOT NULL,
>   gm_moma_vali TIMESTAMPTZ NOT NULL
> );
> 
> CREATE INDEX i_gm_t_movimenti_magazzini_gm_movi_unic
>   ON gm_t_movimenti_magazzini( gm_movi_unic );
> 
> CREATE INDEX i_gm_t_movimenti_magazzini_gm_moti_unic
>   ON gm_t_movimenti_magazzini( gm_moti_unic );
> 
> CREATE INDEX i_gm_t_movimenti_magazzini_ap_prod_unic
>   ON gm_t_movimenti_magazzini( ap_prod_unic );
> 
> CREATE INDEX i_gm_t_movimenti_magazzini_gm_maga_unic
>   ON gm_t_movimenti_magazzini( gm_maga_unic );
> 
> CREATE INDEX i_gm_t_movimenti_magazzini_gm_moma_flag
>   ON gm_t_movimenti_magazzini( gm_moma_flag );
> 
> CREATE INDEX i_gm_t_movimenti_magazzini_gm_moma_vali
>   ON gm_t_movimenti_magazzini( gm_moma_vali );
> 
> CREATE INDEX i_gm_t_movimenti_magazzini_partial
>   ON gm_t_movimenti_magazzini( (gm_moma_flag AND
> gm_moma_vali>='2008-01-01'::TIMESTAMPTZ) )
>   WHERE gm_moma_flag AND gm_moma_vali>='2008-01-01'::TIMESTAMPTZ;
> 
> -- DML
> 
> SELECT SUM( gm_moma_qnta )
>   FROM gm_t_movimenti_magazzini
>   WHERE
>     gm_moma_flag AND
>     gm_moma_vali >= '2008-01-01'::TIMESTAMPTZ AND
>     ap_prod_unic = <a value>
> ;
> 
> where <a value> changes from query to query.
> 
> --
> Fahrbahn ist ein graues Band
> weisse Streifen, grüner Rand
> 
> On Tuesday December 30 2008 15:12:33 Scott Marlowe wrote:
> > On Tue, Dec 30, 2008 at 2:02 AM, Reg Me Please 
> <regmeplease(at)gmail(dot)com>
> wrote:
> > > Only one question remains in my mind:
> > >
> > > why the planner is not using the partial index?
> > >
> > > The partial index is covering 2 predicates out of the 3 
> used in the 
> > > where condition. Actually there is a boolean flag (to exclude 
> > > "disabled" rows), a timestamp (for row age) and an int8 
> (a FK to another table).
> > > The first two are in the partial index in order to exclude 
> > > "disabled" and older rows. The int8 is the "random" key I 
> mentioned earlier.
> > >
> > > So the WHERE condition reads like:
> > >
> > > flag AND tstz >= '2008-01-01'::timestamptz and thekey=42
> > >
> > > I can see in the EXPLAIN that there is no mention to the 
> partial index.
> > > Please keep in mind that the table has 8+M rows, few of which are 
> > > flagged, about 70% don't match the age limit and few dozens match 
> > > the key. In my opinion the partial index should help a lot.
> >
> > Can you show us the DDL for the index creation and the 
> select query as 
> > well?
> 
> 
> 
> --
> Sent via pgsql-general mailing list 
> (pgsql-general(at)postgresql(dot)org) To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 

Responses

pgsql-general by date

Next:From: Dan ArmbrustDate: 2008-12-30 16:32:07
Subject: Re: Slow Vacuum was: vacuum output question
Previous:From: Scott MarloweDate: 2008-12-30 16:19:00
Subject: Re: Slow Vacuum was: vacuum output question

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