Re: [PGSQL 8.3.5] Use of a partial indexes

Well it should look like the one you suggest.
But maybe I've missed some important concept in the partial indexes theory!
:-)

As soon as I read your posting I understood the problem. I was thinking to
create a big cut on the index containing the flag and the timestamp, while
the concept is to cut the other index using the flag+timestamp part of the
condition.

Thanks a lot for the "satori".

--
Fahrbahn ist ein graues Band
weisse Streifen, grüner Rand

On Tuesday December 30 2008 17:20:05 Picavet Vincent wrote:
> 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