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

Re: [HACKERS] 'LIKE' enhancement suggestion

From: JB <jimbag(at)kw(dot)igs(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: [HACKERS] 'LIKE' enhancement suggestion
Date: 2000-03-08 01:24:03
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
I'm running 6.5.2 on RH6.1, 128mb ram, 27gb, P350. I don't understand
this either so please excuse my ignorance. I looked up EXPLAIN and
here's what came out...

psql -c "EXPLAIN SELECT * FROM info WHERE substring(stname from 1 for 4)
= 'MAIN';"
time psql -c "SELECT * FROM info WHERE substring(stname from 1 for 4) =

psql -c "EXPLAIN SELECT * FROM info WHERE stname LIKE 'MAIN%';"
time psql -c "SELECT * FROM info WHERE stname LIKE 'MAIN%';"


Seq Scan on info  (cost=3829.93 rows=15454 width=420)

0.01user 0.01system 0:00.72elapsed 2%CPU (0avgtext+0avgdata
0inputs+0outputs (198major+25minor)pagefaults 0swaps

Index Scan using nx_info1 on info  (cost=1531.12 rows=30 width=420)

0.01user 0.01system 0:00.64elapsed 3%CPU (0avgtext+0avgdata
0inputs+0outputs (198major+25minor)pagefaults 0swaps

Obviously the numbers don't support me. I'm quite confused. I was told
that the engine didn't use indexes with 'LIKE' by someone equally
informed as I, and thus the 'substring' change. This worked remarkably
faster so I assumed it to be true. Apparently it is not. There must be
something with the bigger system that I need to look into (mem usage,
etc). My apologies for chewing up bandwidth. 


Tom Lane wrote:
> 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
> ************

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

In response to


pgsql-hackers by date

Next:From: Tom LaneDate: 2000-03-08 01:28:02
Subject: Re: [HACKERS] library policy question
Previous:From: The Hermit HackerDate: 2000-03-08 01:19:09
Subject: Re: [HACKERS] xlog.c.patch for cygwin port.

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