Re: 7.1.3 not using index

Daniel Kalchev <daniel@digsys.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.


>      attname     | attdispersion | starelid | staattnum | staop | stanullfrac 
> | 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.

7.1 does not have any info about column values other than the most
common, and will arbitrarily estimate their frequencies at (IIRC)
one-tenth of the most common value's.  That's probably still too much
to trigger an indexscan; the crossover point is usually 1% or even
less selectivity.

> 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)

7.2 will do better on this sort of example: it should correctly select
an indexscan when looking for 16 or 32, otherwise a seqscan.

> 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.
        regards, tom lane