Re: Slow query - index not used - Mailing list 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.