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