Optimizer Question/Suggestion

The optimizer seems to know about dead rows in tables (ie. it will use an
index it would not ordinarily use if vacuum-full had been run, apparently
because it knows the table has many dead rows, and only a few valid ones.

I was wondering if there would any value in letting the optimizer replace a
sequential scan with a dummy index scan (eg. on PK) in cases where it knew
that the table was full of dead rows.

This comes about because we have a table with 800 rows, one more more of
which are updated every second of most days. The result in normal usage is
that the table contains about 10000 tuples one hour after vacuuming. Also,
the databases tries to be 24x7, and the table concerned is a core table, so
vacuum/full once per hour is not an option.

To give some numbers:

mail=# explain select * from often_updated;
Seq Scan on often_updated (cost=0.00..49273.50 rows=750 width=205)

mail=# explain select * from often_updated where id between '-10000' and
'10000';
Index Scan using often_updated_id on often_updated (cost=0.00..3041.80
rows=741 width=205)

(the IDs range from 0 to about 1200).

Creating a table by selecting rows from the first table, defining an index
then analyzing results in:

mail=# explain select * from bu where id between '-10000' and '10000';
Seq Scan on bu (cost=0.00..33.25 rows=741 width=205)

...which is perfectly reasonable.

ISTM that if a table has a PK, then a bogus index scan should be introduced
if a table has more than a 'RandomPageCost/SequentialPageCost' ratio of
dead:live tuples. Or we should always add a PK scan into the list of
strategies considered.

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