again on index usage (7.1.3)
От | Daniel Kalchev |
---|---|
Тема | again on index usage (7.1.3) |
Дата | |
Msg-id | 200202121236.OAA28220@dcave.digsys.bg обсуждение исходный текст |
Ответы |
Re: again on index usage (7.1.3)
|
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: