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   |/