On Mon, Mar 11, 2013 at 9:13 PM, Greg Stark <st...@mit.edu> wrote: > On Thu, Mar 7, 2013 at 12:51 AM, Jim Nasby <j...@nasby.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers