From: | Howie <caffeine(at)toodarkpark(dot)org> |
---|---|
To: | pgsql-sql(at)postgreSQL(dot)org |
Cc: | caffeine(at)toodarkpark(dot)org |
Subject: | optimizer woes ? |
Date: | 1999-06-15 17:11:22 |
Message-ID: | Pine.LNX.3.96.990615170128.15049T-100000@rabies.toodarkpark.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)toodarkpark(dot)org> URL: http://www.toodarkpark.org
"The distance between insanity and genius is measured only by success."
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 1999-06-15 17:13:18 | Re: [SQL] optimizer woes ? |
Previous Message | stevew | 1999-06-15 17:09:00 | Function security? |