Re: 7.1.3 not using index

Tom,

You may be correct that sequential scan is preferable, but I can never get 
version 7.1.3 to use index scan on almost any table. Here is the output of 
your query:
    attname     | attdispersion | starelid | staattnum | staop | stanullfrac 
| stacommonfrac | stacommonval | staloval | stahival
-----------------+---------------+----------+-----------+-------+-------------+
---------------+--------------+----------+----------a               |      0.978655 |  8160023 |         1 |    97 |
      0 
 
|      0.988079 | 1            | 1        | 52b               |   2.86564e-05 |  8160023 |         2 |    97 |
0 
 
|     0.0001432 | 4971         | 1        | 12857c               |   0.000520834 |  8160023 |         3 |    97 |
   0 
 
|     0.0025776 | 1            | 1        | 11309d               |      0.104507 |  8160023 |         4 |    97 |
   0 
 
|      0.257437 | 8            | 1        | 32

In fact, field 'd' has only few values - usually powers of 2 (history). Values 
are respectively 1,2,4,8. 16 and 32 and are spread like:

person_type | count 
-------------+-------          1 |  8572          2 |  3464          4 |  8607          8 |  7191         16 |     3
    32 |    96
 
(6 rows)

Some estimates are weird, such as:

db=# explain select * from r where d = 16;
NOTICE:  QUERY PLAN:

Seq Scan on r  (cost=0.00..527.16 rows=719 width=16)

I also tried the same query where the value exists only once in the table - 
one would expect this is the perfect use of index...

I also note very slow response to any queries that access systems tables, such 
as \d in psql.

Daniel


>>>Tom Lane said:> Daniel Kalchev <daniel@digsys.bg> writes:> > (table has ~30k rows)> > EXPLAIN SELECT * FROM r where
d= 8;> > The result is > > NOTICE: QUERY PLAN:> > Seq Scan on r (cost=0.00...3041.13 rows=7191 width=4)> > Seqscan is
theright plan to retrieve 7k rows out of a 30k table.> So the question is whether that estimate is in the right
ballpark>or not.  How many rows are there really with d=8?  If it's way off,> what do you get from> > select
attname,attdispersion,s.*>from pg_statistic s, pg_attribute a, pg_class c> where starelid = c.oid and attrelid = c.oid
andstaattnum = attnum> and relname = 'r';> >             regards, tom lane