Re: again on index usage (7.1.3)

On Tue, 12 Feb 2002, Daniel Kalchev wrote:

> I had one more frustrating exprience with the 7.1.3 optimizer handling
> index/scan selection.
>
> Here is the schema
>
> RADIUS=# \d attrib
> Table "attrib"
> Attribute | Type | Modifier
> -----------+----------------+---------------------
> user_name | character(32) | not null default ''
> attr | character(32) | not null default ''
> value | character(128) |
> op | character(2) |
> Index: uattr
>
> RADIUS=# \d uattr
> Index "uattr"
> Attribute | Type
> -----------+---------------
> user_name | character(32)
> attr | character(32)
> op | character(2)
> btree
>
>
> (this is for use by gnu-radius).
>
> RADIUS=# select count(*) from attrib;
> count
> --------
> 396117
> (1 row)
>
> RADIUS=# select count(distinct user_name) from attrib;
> count
> -------
> 62713
> (1 row)
>
>
> each username has more or less the same number of attributes.
>
> SELECT * FROM attrib WHERE user_name = 'xyz';
>
> always results in sequential scan.
>
> As you can see, there is sufficient number of different user_name values - why
> the sequential scan?
>
> Needless to say that turning off sequential scans results is measurably faster
> index scan.

Let's start with the standard set of things. Have you vacuum analyzed,
what does explain show for the query, is there one value that is more
common than all others?