Re: [HACKERS] 'LIKE' enhancement suggestion

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...

---[snip]---
#!/bin/sh
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) =
'MAIN';"

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

outputs...

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

0.01user 0.01system 0:00.72elapsed 2%CPU (0avgtext+0avgdata
0maxresident)k
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
0maxresident)k
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.

jb

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.