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
