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@xxxxxxxxxxxxxx > > [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] De la part de Reg > > Me Please > > Envoyé : mardi 30 décembre 2008 17:09 > > À : Scott Marlowe > > Cc : Scott Ribe; Gauthier, Dave; pgsql-general@xxxxxxxxxxxxxx > > Objet : Re: [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@xxxxxxxxx> > > > > 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@xxxxxxxxxxxxxx) To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
