Re: Queries not using Index

Поиск
Список
Период
Сортировка
От Christopher Kings-Lynne
Тема Re: Queries not using Index
Дата
Msg-id GNELIHDDFBOCMGBFGEFOKEEPCDAA.chriskl@familyhealth.com.au
обсуждение исходный текст
Ответ на Re: Queries not using Index  (Daryl Herzmann <akrherz@iastate.edu>)
Ответы Re: Queries not using Index  (Daryl Herzmann <akrherz@iastate.edu>)
Список pgsql-sql
Have you tried playing with the statistics gatherer?

>From the ANALYZE docs:

"The extent of analysis can be controlled by adjusting the per-column
statistics target with ALTER TABLE ALTER COLUMN SET STATISTICS (see ALTER
TABLE). The target value sets the maximum number of entries in the
most-common-value list and the maximum number of bins in the histogram. The
default target value is 10, but this can be adjusted up or down to trade off
accuracy of planner estimates against the time taken for ANALYZE and the
amount of space occupied in pg_statistic. In particular, setting the
statistics target to zero disables collection of statistics for that column.
It may be useful to do that for columns that are never used as part of the
WHERE, GROUP BY, or ORDER BY clauses of queries, since the planner will have
no use for statistics on such columns. "

Just a thought...

Also, what is the result of:

select indexdef from pg_indexes where indexname='t2002_06_station_idx';

> Any thoughts?  I am sorry to be causing all this trouble.  I just want my
> queries to voom-voom!!  Interestingly enough, I see that the SEQ SCAN is
> now estimated at 1730.63, when I first posted to this list, it
> was 3900.00
> or so. Errrr

It's no trouble.  Cases where the planner fails are essential to improving
the planner.  Ideally this query should use your index automatically...

Chris



В списке pgsql-sql по дате отправления:

Предыдущее
От: Daryl Herzmann
Дата:
Сообщение: Re: Queries not using Index
Следующее
От: Daryl Herzmann
Дата:
Сообщение: Re: Queries not using Index