Bruce Momjian wrote:
> Heikki Linnakangas wrote:
>> Jim C. Nasby wrote:
>>> Granted, you'd want to periodically ensure that you scan the entire
>>> index, but that shouldn't be horribly hard to set up.
>> Well, it seems to be. A vacuum can't evaluate index expressions because
>> it's not in a real transaction.
>>
>> The DBA could set up a cron job to do "SELECT * FROM foo WHERE bar > 0"
>> etc. with enable_seqscan=false? That would work, but we can't depend on
>> an additional administrative task like. And we might as well just
>> disable the optimization that's causing us problems.
>
> Why can't the C code just do a full index scan that touches the heap,
> sets those expired bits, and then do a vacuum? My point is that the
> bits can be set outside the normal vacuum process, so you don't have to
> be doing heap lookups from the index inside vacuum.
The point of the optimization that's causing problems was to reduce the
effect of long-running vacuum transactions. If we're going to have
another long running transaction instead, we're back to square one.
AFAICS, we could disable the optimization and use a full-blown
transaction when vacuuming a table with a functional block index.
Granted, that's annoying, but not a show-stopper I think.
> Assuming the heap is mostly in index order, the full index scan
> shouldn't take much longer than a heap scan, and if the heap doesn't
> match index order, a block index shouldn't be used anyway.
It introduces one more full heap scan for each block index on a table.
That's expensive.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com