Re: Optimizer Question/Suggestion

At 09:36 AM 2/11/2002 -0500, Tom Lane wrote:

>Why not do frequent non-full vacuums on only that table, perhaps every
>five minutes or so? That's certainly the direction that development is
>headed in (we just haven't automated the vacuuming yet).

Done this now, and I'll wait for a new high load time to see how big the
table gets.

Definitely looking forward integrated on-line vacuum!

>Ideally we should never let a table get so overloaded with dead space
>that this strategy would be profitable.

I suspect it would be more common that you might hope, both because of
incompetance/changed database usage (as in this case) and archival
strategies (ie. deleting data periodically, but *not* doing a full vacuum).
I come from a background where pre-allocating unused space for table data
is a good strategy, not a performance killer, and I'm probably not alone.

If it was not hard, I thought adding a PK scan as a possible strategy when
considering seqscan was an interesting option. I suppose the other option
in this case would be to modify seqscan to only look at pages we know have
records (if we keep that data?).

>BTW, the system does not actually have any stats about dead tuples.
>What it knows about are live tuples and total disk pages occupied by
>the table.

So what made it choose the index scan? Does it make guesses about tuple
sizes, and predict empty space?

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/