optimizer woes ?

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

Responses

Browse pgsql-sql by date

  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?