Re: 7.1.3 not using index

>>>Tom Lane said:
> Daniel Kalchev <daniel(at)digsys(dot)bg> writes:
> > 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.
>
> That's a fairly large claim to make, especially on the evidence of this
> one table.

I tend to make it after waiting for almost two calendar days for an join query
to complete (which takes at most under 10 seconds on 7.0). :-) (and of course,
after spending few more days to understand what is going on)

> > attname | attdispersion | starelid | staattnum | staop | stanullf
rac
> > | stacommonfrac | stacommonval | staloval | stahival
> > 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).
>
> What you've got here is that 8 is recorded as the most common value in
> column d, with a frequency of 0.25 or about 1/4th of the table. So
> searches for d = 8 will correctly estimate the selectivity at about 0.25
> and will (correctly) decide not to use the index.

This I understand and this is why I gave the other examples... Your
explanation on how 7.1 would handle this situation sort of explains the
unfortunate siguation...

Am I correct in assuming that it will be better to delete the index on such
fields? (for 7.1)

> > I also note very slow response to any queries that access systems
> > tables, such as \d in psql.
>
> There might indeed be something broken in your installation, but you've
> shown me no concrete evidence of it so far. On this query, 7.1 is
> behaving as designed.

If you are going to tell me 7.1 will only use index scan on PRIMARY KEY
columns, I will spend some more time with the 7.2 betas (who knows, this may
be the secret plan <grin>)

Here is another table:

CREATE TABLE "persons" (
"personid" integer DEFAULT nextval('personid_seq'::text),
"name" text,
"title" text,
[...]
);

CREATE INDEX "persons_personid_idx" on "persons" using btree ( "personid"
"int4_ops" );

db=# select count(*) from persons;

count
-------
14530
(1 row)

(part of the statistics for this row)
attname | attdispersion | starelid | staattnum | staop | stanullfrac |
stacommonfrac | stacommonval | staloval |
stahival
-------------+---------------+----------+-----------+-------+-------------+----
-----------+------------------------+------------------------+-----------------
---------
personid | 4.1328e-05 | 19795 | 1 | 97 | 0 |
0.000206469 | 2089 | 1 | 12857

now, EXPLAIN again gives me:

db=# explain select * from persons where personid = 1;
NOTICE: QUERY PLAN:

Seq Scan on persons (cost=0.00..490.62 rows=1 width=177)

(note, personid is not unique - there are some 'duplicate' rows that mark
archived records - but there are no more than 4-5 occurrences of the same
personid and this is rare)

If this is problem with my installation (I especially installed new BSD/OS 4.2
to test on clean 7.1.3 with my production database). It has locale eanbled,
but nowhere in the queries there is text involved...

How about this query (using my previous table r, that has poiner to the
personid on persons):

db=# explain select * from persons, r where r.d = 1 and r.a = persons.personid;
NOTICE: QUERY PLAN:

Merge Join (cost=0.00..nan rows=299 width=193)
-> Index Scan using persons_personid_idx on persons (cost=0.00..nan
rows=14530 width=177)
-> Index Scan using r_a_idx on representatives (cost=0.00..nan rows=719
width=16)

Why would it do index scans on r.a?

Daniel