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

Re: [HACKERS] 'LIKE' enhancement suggestion

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: JB <jimbag(at)kw(dot)igs(dot)net>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] 'LIKE' enhancement suggestion
Date: 2000-03-08 00:32:10
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
JB <jimbag(at)kw(dot)igs(dot)net> writes:
> 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.

This makes no sense to me at all.  The latter query should be far
slower, because AFAIK there is no optimization for it, whereas there is
an optimization for "foo LIKE 'bar%'".

What version are you running, and what plan does EXPLAIN show for
each of these queries?

			regards, tom lane

In response to


pgsql-hackers by date

Next:From: Tom LaneDate: 2000-03-08 00:55:17
Subject: Re: [HACKERS] DROP TABLE inside a transaction block
Previous:From: Tom LaneDate: 2000-03-08 00:22:14
Subject: Re: [HACKERS] pg_pwd trigger to be removed

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