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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
