Re: Indexes not used in 7.1RC4: Bug?
От | Alvar Freude |
---|---|
Тема | Re: Indexes not used in 7.1RC4: Bug? |
Дата | |
Msg-id | 3AD3374A.E87BDAD6@huitzilopochtli обсуждение исходный текст |
Ответ на | Indexes not used in 7.1RC4: Bug? (Alvar Freude <alvar@agi.de>) |
Список | pgsql-hackers |
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)
В списке pgsql-hackers по дате отправления: