Re: More Performance

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Matthias Urlichs" <smurf(at)noris(dot)net>
Cc: Mike Mascari <mascarm(at)mascari(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: More Performance
Date: 2000-05-21 04:45:58
Message-ID: 26882.958884358@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

"Matthias Urlichs" <smurf(at)noris(dot)net> writes:
> I've found another one of these performance problems in the benchmark,
> related to another ignored index.
> The whole thing works perfectly after a VACUUM ANALYZE on the
> table.
> IMHO this is somewhat non-optimal. In the absence of information
> to the contrary, PostgreSQL should default to using an index if
> it might be appropriate, not ignore it.

Just FYI: Postgres absolutely does not "ignore" an index in the absence
of VACUUM ANALYZE stats. However, the default assumptions about
selectivity stats create cost estimates that are not too far different
for index and sequential scans. On a never-vacuumed table you will
get an indexscan for "WHERE col = foo". If the table has been vacuumed
but never vacuum analyzed, it turns out that you get varying results
depending on the size of the table and the average tuple size (since the
planner now has non-default info about the table size, but still nothing
about the actual selectivity of the WHERE condition).

The cost estimation code is under active development, and if you check
the pgsql list archives you will find lively discussions about its
deficiencies ;-). But I'm having a hard time mustering much concern
about whether it behaves optimally in the vacuum-but-no-vacuum-analyze
case.

regards, tom lane

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Matthias Urlichs 2000-05-21 06:25:32 Re: MySQL's "crashme" (was Re: Performance)
Previous Message Matthias Urlichs 2000-05-21 04:34:13 Re: MySQL crashme test and PostgreSQL

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert B. Easter 2000-05-21 05:57:27 Re: Thus spoke SQL3 (on OO)
Previous Message Matthias Urlichs 2000-05-21 04:34:13 Re: MySQL crashme test and PostgreSQL