Re: [HACKERS] Worthwhile optimisation of position()?
On Fri, Mar 24, 2006 at 02:58:54PM +0800, Christopher Kings-Lynne wrote: Yeah. AFAICS the transformation Chris suggested is valid. I'm really dubious that it's worth expending planner cycles to look for it though. LIKE is something that everybody and his brother uses, but who uses this position()=0 locution? One of our junior developers :) Which is why I noticed it. Sounds like time to bust out the cluebat. :) -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Worthwhile optimisation of position()?
Christopher Kings-Lynne wrote:
Is it worth allowing this:
select count(*) from users_users where position('ch' in username) = 0;
To be able to use an index, like:
select count(*) from users_users where username like 'ch%';
At the moment the position() syntax will do a seqscan, but the like
syntax will use an index.
You must compare position('ch' in username) to '%ch%' instead of 'ch%' in this
respect.
The position function must look for 'ch' everywhere in the string so there's no way it can
use an index.
Regards,
Thomas Hallgren
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Worthwhile optimisation of position()?
Thomas Hallgren wrote:
Christopher Kings-Lynne wrote:
Is it worth allowing this:
select count(*) from users_users where position('ch' in username) = 0;
To be able to use an index, like:
select count(*) from users_users where username like 'ch%';
At the moment the position() syntax will do a seqscan, but the like
syntax will use an index.
You must compare position('ch' in username) to '%ch%' instead of 'ch%'
in this respect.
The position function must look for 'ch' everywhere in the string so
there's no way it can use an index.
I think the '= 0' bit is what Chris was suggesting could be the basis
for an optimisation.
Tim
--
---
Tim Allen [EMAIL PROTECTED]
Proximity Pty Ltd http://www.proximity.com.au/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Worthwhile optimisation of position()?
Tim Allen [EMAIL PROTECTED] writes: Thomas Hallgren wrote: The position function must look for 'ch' everywhere in the string so there's no way it can use an index. I think the '= 0' bit is what Chris was suggesting could be the basis for an optimisation. Yeah. AFAICS the transformation Chris suggested is valid. I'm really dubious that it's worth expending planner cycles to look for it though. LIKE is something that everybody and his brother uses, but who uses this position()=0 locution? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Worthwhile optimisation of position()?
Tom Lane wrote:
Tim Allen [EMAIL PROTECTED] writes:
Thomas Hallgren wrote:
The position function must look for 'ch' everywhere in the string so
there's no way it can use an index.
I think the '= 0' bit is what Chris was suggesting could be the basis
for an optimisation.
Yeah. AFAICS the transformation Chris suggested is valid. I'm really
dubious that it's worth expending planner cycles to look for it though.
LIKE is something that everybody and his brother uses, but who uses this
position()=0 locution?
regards, tom lane
The documentation says: position('om' in 'Thomas') == 3 so i assumed
that the returned index was 1-based and that a zero meant 'not found'.
If I'm wrong ,perhaps the docs need to be updated?
Regards,
Thomas Hallgren
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Worthwhile optimisation of position()?
Yeah. AFAICS the transformation Chris suggested is valid. I'm really dubious that it's worth expending planner cycles to look for it though. LIKE is something that everybody and his brother uses, but who uses this position()=0 locution? One of our junior developers :) Which is why I noticed it. Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Worthwhile optimisation of position()?
Thomas Hallgren wrote:
Tom Lane wrote:
Tim Allen [EMAIL PROTECTED] writes:
Thomas Hallgren wrote:
The position function must look for 'ch' everywhere in the string so
there's no way it can use an index.
I think the '= 0' bit is what Chris was suggesting could be the basis
for an optimisation.
Yeah. AFAICS the transformation Chris suggested is valid. I'm really
dubious that it's worth expending planner cycles to look for it though.
LIKE is something that everybody and his brother uses, but who uses this
position()=0 locution?
regards, tom lane
The documentation says: position('om' in 'Thomas') == 3 so i assumed
that the returned index was 1-based and that a zero meant 'not found'.
If I'm wrong ,perhaps the docs need to be updated?
The docs are correct so my initial point was correct. position('ch' in user) = 0 is
equivalent to user NOT LIKE '%ch%' and there's no way you can index that.
Regards,
Thomas Hallgren
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Worthwhile optimisation of position()?
The docs are correct so my initial point was correct. position('ch' in
user) = 0 is equivalent to user NOT LIKE '%ch%' and there's no way
you can index that.
Well = 1 then.
Chris
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
