Re: Slow query - index not used

> 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 | 10068
Component | 1200
CustTicket | 15009
Deliver | 1201
Download | 1999
GroupProv | 464
IP_News | 5950
IP_Publish_Request | 4000
IP_Usage | 2000
KnowledgeBase | 15002
LevelInfo | 10
OtherParam | 4000
Request | 4501
TestMethod | 4050
VerTech | 4000
Version | 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.83 rows=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.