Re: Using indexes for partial index builds

On Mon, Mar 11, 2013 at 9:13 PM, Greg Stark <stark(at)mit(dot)edu> wrote:
> On Thu, Mar 7, 2013 at 12:51 AM, Jim Nasby <jim(at)nasby(dot)net> wrote:
>> Something worth considering on this... I suspect it's possible to use an
>> index-only scan to do this, regardless of whether the heap page is all
>> visible. The reason is that the newly created index would just use the same
>> access methodology as the original index, so any dead rows would be ignored.
>
> This is actually quite clever. I wonder how many other cases can use
> similar logic.

I actually just dealt with a case where this would have been helpful.
The case is finding rows from a huge table where a foreign key
reference matches one from a list of IDs and the last change date is
larger than some specific value. The best plan for this is to build
bitmaps for both conditions. The performance issue is that the bitmap
for the IDs can get pretty large and expensive to construct. Solution
for that is to keep a partial index on the foreign key predicated on a
recent timestamp covering most queries, removing 99% of tuples from
consideration. This index needs to be periodically replaced with one
that has a newer timestamp. A full table scan could be avoided if the
index could be built using the previous partial index. Now this was on
9.1, so I didn't consider index only, but on 9.2+ I would add the
timestamp to the foreign key index, then the new index could be
constructed using an index only scan.

I have a feeling this is an increasingly widespread pattern with a
proliferation of mobile devices that need syncing.

Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de