Re: [PGSQL 8.3.5] Use of a partial indexes - Mailing list 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@postgresql.org
> [mailto:pgsql-general-owner@postgresql.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@postgresql.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@gmail.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@postgresql.org) To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>