Re: [PGSQL 8.3.5] Use of a partial indexes

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
>