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@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?