i've got a table with about 420,000 rows in it and two indexes:
Table = logins
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| nick | varchar() not null | 30 |
| channum | int4 not null | 4 |
| hostname | varchar() not null | 120 |
| dttime | datetime not null default now ( | 8 |
| logtype | char() not null default 'I' | 1 |
+----------------------------------+----------------------------------+-------+
Indices: logins_hostname_idx logins_nick_idx
logins_hostname_idx is on, oddly enough, logins.hostname
logins_nick_idx is on logins.nick
neither index is unique.
table is VACUUM ANALYZE'd every night. so, why would this query not be
using the index on logins.nick?
---[ CUT ]---
ircbot=> explain select nick,hostname,dttime,logtype from logins where
nick=lower('anick') ORDER BY dttime desc;
NOTICE: QUERY PLAN:
Sort (cost=19913.31 size=0 width=0) -> Seq Scan on logins (cost=19913.31 size=42498 width=44)
---[ CUT ]---
but when removing the lower(), it uses the index:
---[ CUT ]---
ircbot=> explain select nick,hostname,dttime,logtype from logins where
nick='anick' ORDER BY dttime desc;
NOTICE: QUERY PLAN:
Sort (cost=15.68 size=0 width=0) -> Index Scan using logins_nick_idx on logins (cost=15.68 size=214
width=44)
---[ CUT ]---
shouldn't the optimizer convert lower('anick') to lowercase first, then
use that value in searching the logins_nick_idx index ?
btw, this is using postgres 6.4.2 (system is a debian box, p2-350 with
256m, all uw scsi </brag> )
---
Howie <caffeine@toodarkpark.org> URL: http://www.toodarkpark.org
"The distance between insanity and genius is measured only by success."
