On Jul 18, 2007, at 2:16 PM, Steve Crawford wrote:
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.
Check the source code, but I'm 99% certain that CREATE INDEX doesn't
consider any existing indexes. While what you're describing is
theoretically possible, it's not a very common use-case, so it's
rather unlikely to get worked on unless other folks show up with
*real life* examples of where this would be useful.
You might also want to consider partitioning the table.
--
Jim Nasby jim@xxxxxxxxx
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
