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
