Re: Will partial index creation use existing index?

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@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)