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 | 52
b | 2.86564e-05 | 8160023 | 2 | 97 | 0
| 0.0001432 | 4971 | 1 | 12857
c | 0.000520834 | 8160023 | 3 | 97 | 0
| 0.0025776 | 1 | 1 | 11309
d | 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(at)digsys(dot)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 the right 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 and staattnum = attnum
> and relname = 'r';
>
> regards, tom lane