Slow query - index not used

Поиск
Список
Период
Сортировка
От Michael Brusser
Тема Slow query - index not used
Дата
Msg-id DEEIJKLFNJGBEMBLBAHCOEGKECAA.michael@synchronicity.com
обсуждение исходный текст
Ответы Re: Slow query - index not used  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-hackers
I'm running Postgres v.7.3.4 on Solaris.

Here's a bare-bone version of the query that runs unreasonably slow:
SELECT count (*) from note_links_aux WHERE nt_note_name='KnowledgeBase';

Query Plan shows that db runs through the entire table:Aggregate  (cost=1983.58..1983.58 rows=1 width=0)  ->  Seq Scan
onnote_links_aux  (cost=0.00..1970.18 rows=5360 width=0)        Filter: (nt_note_name = 'KnowledgeBase'::character
varying)

even though field nt_note_name is indexed:
Indexes: nla_nl_id_fk_i btree (nl_id),        nla_nt_note_name_fk_i btree (nt_note_name), ... ...

Running the same query against another varchar field on this table
I see that index is being used:   ->  Index Scan using ...
This query runs much faster.

Analyzing table did not help.
Using "DEFAULT_STATISTICS_TARGET = 100" made db use index for 
field = 'abc', but field = 'xyz' still results in the table scan.

Also 'rows' number reported in the plan is way off.
What can be done here?

Thanks,
Mike.



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

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Regarding development and the submittal of patches
Следующее
От: Peter Eisentraut
Дата:
Сообщение: Re: Allow backend to output result sets in XML