pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedoptimizer woes ?
4+ messages / 3 participants
[nested] [flat]
* optimizer woes ?
@ 1999-06-15 17:11 Howie <caffeine@toodarkpark.org>
1999-06-15 17:13 ` Re: [SQL] optimizer woes ? Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Howie @ 1999-06-15 17:11 UTC (permalink / raw)
To: pgsql-sql; +Cc: caffeine@toodarkpark.org
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."
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] optimizer woes ?
1999-06-15 17:11 optimizer woes ? Howie <caffeine@toodarkpark.org>
@ 1999-06-15 17:13 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-06-15 19:55 ` Re: [SQL] optimizer woes ? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Bruce Momjian @ 1999-06-15 17:13 UTC (permalink / raw)
To: Howie <caffeine@toodarkpark.org>; +Cc: pgsql-sql
> 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, Pennsylvania 19026
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] optimizer woes ?
1999-06-15 17:11 optimizer woes ? Howie <caffeine@toodarkpark.org>
1999-06-15 17:13 ` Re: [SQL] optimizer woes ? Bruce Momjian <maillist@candle.pha.pa.us>
@ 1999-06-15 19:55 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-06-15 20:09 ` Re: [SQL] optimizer woes ? Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 1999-06-15 19:55 UTC (permalink / raw)
To: Bruce Momjian <maillist@candle.pha.pa.us>; +Cc: Howie <caffeine@toodarkpark.org>; pgsql-sql
>> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] optimizer woes ?
1999-06-15 17:11 optimizer woes ? Howie <caffeine@toodarkpark.org>
1999-06-15 17:13 ` Re: [SQL] optimizer woes ? Bruce Momjian <maillist@candle.pha.pa.us>
1999-06-15 19:55 ` Re: [SQL] optimizer woes ? Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-06-15 20:09 ` Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Bruce Momjian @ 1999-06-15 20:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Howie <caffeine@toodarkpark.org>; pgsql-sql
> 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, Pennsylvania 19026
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~1999-06-15 20:09 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-15 17:11 optimizer woes ? Howie <caffeine@toodarkpark.org>
1999-06-15 17:13 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-06-15 19:55 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-06-15 20:09 ` Bruce Momjian <maillist@candle.pha.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox