Re: Indexes not used in 7.1RC4: Bug?

Thomas Lockhart wrote:
>
> The parser does not know that your int4 constant "0" can be represented
> as an int2. Try
>
> SELECT * FROM access_log WHERE method_num = int2 '0';

hmmm, but its still a sequentiell scan:

logger=# explain SELECT * FROM access_log
WHERE method_num = int2 '0';
Seq Scan on access_log (cost=0.00..16443.71 rows=559371 width=89)

But:
Now I realised: the number of rows! :)
If I make "WHERE method_num = int2 '2', then the index is used,
interesting -- so it seems that the optimizer uses the value of the
WHERE clause to check what might be faster and guesses, that an index
scan is more overhead and slower. Nice!

> For the other cases, PostgreSQL is estimating the query cost to be lower
> with a sequential scan.

hm, OK, but I guess, that he is estimating wrong ;)

After re-reading the using-explain chapter in the docs I guess I
understand the problems of estimating the number of rows ...

Do you have any hints how to optimize the ..._cost-Values?

Perhaps it is possible to write a test program, which checks out some
good ..._cost-Values -- I'm volunteer, but I guess it should possible
for this to force some optimizer results to measure the real time some
different methods cost.

> For the "SELECT 1" subselect case, it may be that the optimizer does not cheat and > determine that there will be only
> one row returned, or that the query can be reformulated to use a simple
> constant.

yes, it was only an example -- i hope nobody is really so stupid and
uses a "select 1" subselect ;)

It might be an optimization, that the hole subselect is performed before
the outer select is called, so the result of the subselect can be used
in the query planer.

Ciao
Alvar

--
AGI
Magirusstrasse 21B, 70469 Stuttgart
Fon +49 (0)711.228 74-50, Fax +49 (0)711.228 74-88
+++news+++news+++news+++
Beste Image-Website 2001 kommt von AGI
http://www.agi.de/tagebuch
http://www.agi.com/diary (english)