Will partial index creation use existing index?

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: Will partial index creation use existing index?
Date: 2007-07-18 21:16:44
Message-ID: 469E833C.4000401@pinpointresearch.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Does PostgreSQL use an existing index, if possible, when creating a
partial index?

By way of background, we have some nightly bulk processing that includes
a couple of 15-30 million row tables. Most of the processing is only
looking at prior-day data (up to ~200,000 rows) and for efficiency
requires several indexes. Except for this one process, the indexes are
useless and I'd rather not constantly maintain them.

There is an index on the timestamp column so I have considered creating
the indexes on a temporary basis with something like:
create index foo_bar on foo (bar)
where timestamp_col > current_date - interval '1 day';

(Yes this is simplified, I am aware of the Daylight Saving Time
off-by-an-hour implications.)

It seems that creating this partial index would be more efficient if the
existing index were used but "explain create index..." just gives me an
error and the query seems to run way too long to be processing only the
one day data. For comparison, on a relatively large 225,000 row day I
can create temporary table ondeay... on the same criteria and create 10
indexes and analyze the table in well under 10 seconds which is way
faster than creating even a single partial index on the full table.

Cheers,
Steve

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gregory Stark 2007-07-18 21:28:07 Re: Update of table lags execution of statement by >1 minute?
Previous Message Michael Glaesemann 2007-07-18 21:14:24 Re: Need help optimizing this query