Skip site navigation (1) Skip section navigation (2)

'LIKE' enhancement suggestion

From: JB <jimbag(at)kw(dot)igs(dot)net>
To: pgsql-hackers(at)postgresql(dot)org
Subject: 'LIKE' enhancement suggestion
Date: 2000-03-08 00:04:16
Message-ID: 38C59900.2A4469C8@kw.igs.net (view raw or flat)
Thread:
Lists: pgsql-hackers
I have a 50MB +- table with about 70,000 records which on which I was
doing LIKE selects. It was taking approx 20 secs to complete the search.
The table is something like...

CREATE TABLE info (
  lastname char(50),
  street_name char(50),
  ...(etc omitted)
);

CREATE INDEX nx_info1 ON info (lastname);
CREATE INDEX nx_info2 ON info (street_name);

on which I was doing...

SELECT * FROM info WHERE street_name LIKE 'MAIN%';

...this would take about 20 secs to complete. Because the wildness only
happens at the end of the search string, I changed the query to...

SELECT * FROM info WHERE substring( street_name from 1 to 4 ) = 'MAIN';

...this takes under 2 secs. I wrote a piece of code in python to do this
automatically for me but it seems to me that the parser/optimizer could
take a look at this case and re-write the query with the '=' instead of
the 'LIKE'. I've looked through the 'C' code to see where this could
happen but it is too thick for me to sort out with my schedule, so I
thought I'd make the suggestion here.

cheers
jim
-- 
If everything is coming your way then you're in the wrong lane.

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2000-03-08 00:09:21
Subject: Re: [HACKERS] sqgfault on initdb with current CVS
Previous:From: Bruce MomjianDate: 2000-03-08 00:00:16
Subject: Re: [HACKERS] Proposal for Grand Unified Configuration scheme

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group