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.
Daniel