Re: Slow query - index not used
От | Michael Brusser |
---|---|
Тема | Re: Slow query - index not used |
Дата | |
Msg-id | DEEIJKLFNJGBEMBLBAHCOEHBECAA.michael@synchronicity.com обсуждение исходный текст |
Ответ на | Re: Slow query - index not used (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Slow query - index not used
(Dennis Bjorklund <db@zigo.dhs.org>)
Re: Slow query - index not used (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-hackers |
> What's the actual distribution of values in these columns? > Are you searching for values that are particularly common > or uncommon? This column always has a predefined set of values. Usually the app. would search for one of the existing values. --------------------------- Total records: 74654 --------------------------- nt_note_name | count --------------------+------Collection | 10068Component | 1200CustTicket | 15009Deliver | 1201Download | 1999GroupProv | 464IP_News | 5950IP_Publish_Request | 4000IP_Usage | 2000KnowledgeBase | 15002LevelInfo | 10OtherParam | 4000Request | 4501TestMethod | 4050VerTech | 4000Version | 1200 --------------------------- I started from scratch: took out param DEFAULT_STATISTICS_TARGET from config file, restarted db, ran vacuum analyze, then got this statistics: ------------------------------------------------------------------------ # explain select count (1) from note_links_aux where nt_note_name = 'KnowledgeBase'; Aggregate (cost=1982.68..1982.68 rows=1 width=0) -> Seq Scan on note_links_aux (cost=0.00..1970.18 rows=5002 width=0) Filter: (nt_note_name = 'KnowledgeBase'::character varying) ------------------------------------------------------------------------ # explain select count (1) from note_links_aux where nt_note_name = 'OtherParam'; Aggregate (cost=1984.78..1984.78 rows=1 width=0) -> Seq Scan on note_links_aux (cost=0.00..1970.18 rows=5840 width=0) Filter: (nt_note_name = 'OtherParam'::character varying) ------------------------------------------------------------------------ # explain select count (1) from note_links_aux where nt_note_name = 'LevelInfo'; Aggregate (cost=58.91..58.91 rows=1 width=0) -> Index Scan using nla_nt_note_name_fk_i on note_links_aux (cost=0.00..58.87 rows=15 width=0) Index Cond: (nt_note_name = 'LevelInfo'::character varying) ------------------------------------------------------------------------ # explain select count (1) from note_links_aux where nt_note_name = 'NoSuchThing'; Aggregate (cost=5.83..5.83 rows=1 width=0) -> Index Scan using nla_nt_note_name_fk_i on note_links_aux (cost=0.00..5.83rows=1 width=0) Index Cond: (nt_note_name = 'NoSuchThing'::character varying) ------------------------------------------------------------------------ So 'rows' values are incorrect. Also looking at queries with 'KnowledgeBase' and 'OtherParam' - does seq. scan make sense? I mean 'rows' has value of about 5000 records from the total of 75000 records on the table. This ratio does not seem high enough to assume that index scan won't be benefitial. And even if we consider the real number of records - 5000, this is still only 20% of the total. Would an index scan be still faster? Sorry if I put here more info than you need. Thanks, Mike.
В списке pgsql-hackers по дате отправления: