Re: Indexes not always used after inserts/updates/vacuum

Hi,

On Fri, 1 Mar 2002 at 09:37, Tom Lane wrote:

> Reinhard Max <max(at)suse(dot)de> writes:
>
> > I'll tell my colleague (it's his test database, after all) that he
> > should take more realistic test data before complaining about bad
> > performance...
>
> Actually, is it unrealistic test data?

maybe not from the Database's point of view, but certainly from the
application's. It is unrealistic insofar as it doesn't match the
scenario very good it was meant to be a test case for.

I think customer IDs usually appear in a more or less strict ascending
order and foreign keys that reference them are likely to be rather
equally distributed over the IDs or at least not that much biassed
towards one end of the ID range.

When I thought about the structure of this test data and experimented
with an ascending ordered copy of the address table, I rememered a
feature I've once seen in Informix. I think they call it "clustering"
or something the like. I don't remember the precise syntax, but it
was possible to order a table's rows physically by a given column.

Do you think it would be worth the effort to add support for such a
thing to the VACUUM command? I could imagine it to improve situations
where long tables have to be joined very often.

The syntax I have in mind is something like: "VACUUM foo ORDER BY id"
or simply "VACUUM foo(id)".

Another way would be to enhance the DDL so that the table itself could
be told which column(s) to order by and then a "VACUUM ORDER" would
physically re-order the tables by that column(s).

> I have committed changes for 7.3 that do this. It's probably too
> big a change to risk back-patching for 7.2.1, but if you care to
> experiment with CVS tip then you could try it out.

Hopefully I find some time to have a look at it when SuSE Linux 8.0 is
done...

Thanks for all your help, Tom.

Greetings from Nuremberg,

Reinhard