Re: 7.1.3 not using index - Mailing list pgsql-hackers

>>>Tom Lane said:> Daniel Kalchev <daniel@digsys.bg> writes:> > You may be correct that sequential scan is preferable,
butI can never get     > > version 7.1.3 to use index scan on almost any table.> > That's a fairly large claim to make,
especiallyon 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
orabout 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
indeedbe something broken in your installation, but you've> shown me no concrete evidence of it so far.  On this query,
7.1is> 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