Thread: optimizer woes ?
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."
> 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> ) > We have on our TODO list: * Use index with constants on functions Seems we have not implemented it yet. It may be done in 6.5, but I don't think so. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
>> shouldn't the optimizer convert lower('anick') to lowercase first, then >> use that value in searching the logins_nick_idx index ? The system only knows how to use qualifications like "var rel constant" as index-scan restrictions. "var rel func(constant)" is not of that form ... but it could be if there were a preprocessing step that recognized "func(constant)" as a constant subexpression and replaced it by its result. Right now, we don't have any such step. I've been thinking about it for 6.6 though. Bruce Momjian <maillist@candle.pha.pa.us> writes: > We have on our TODO list: > * Use index with constants on functions The TODO entry is insufficiently ambitious: it should read "implement a general-purpose constant-subexpression-reduction step". (Actually, I think that TODO entry might refer to something completely different ... wasn't the complaint that you couldn't make an index on "date_part('date', field)"? ) regards, tom lane
> The TODO entry is insufficiently ambitious: it should read "implement > a general-purpose constant-subexpression-reduction step". (Actually, Changed: * Convert function(constant) into a constant for index use > I think that TODO entry might refer to something completely different > ... wasn't the complaint that you couldn't make an index on > "date_part('date', field)"? ) This is a separate issue. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026