LIKE and indexes?

When in doubt, try the explain command

Not exactly sure about postgres but in general LIKE can
only use an index in the case of LIKE "Something%"

LIKE "%Something" or LIKE "%Something%"
won't use an index since it would have to scan the entire
index to find all matches.

Alex.

On Wed, 14 Mar 2001, Alexander Jerusalem wrote:

Thanks for your answer, Alex!

I've done an EXPLAIN and I saw that no index was used although my SQL
statement had a wild card only at the end as you pointed out ('blah%'). My
guess is that this is because of the locale support.

Regards,
Alexander Jerusalem

At 01:22 15.03.01, adb wrote:

From: "Alexander Jerusalem" <alexander.jerusalem@pop.chello.at>

Thanks for your answer, Alex!

I've done an EXPLAIN and I saw that no index was used although my SQL
statement had a wild card only at the end as you pointed out ('blah%'). My
guess is that this is because of the locale support.

Try like '^blah%' - maybe the additional hint will help (but maybe not). How
many rows did PG think it was going to access in the explain?

- Richard Huxton

Regards,
Alexander Jerusalem

At 01:22 15.03.01, adb wrote:

When in doubt, try the explain command

Not exactly sure about postgres but in general LIKE can
only use an index in the case of LIKE "Something%"

LIKE "%Something" or LIKE "%Something%"
won't use an index since it would have to scan the entire
index to find all matches.

Alex.

On Wed, 14 Mar 2001, Alexander Jerusalem wrote:

Hi,

Can anyone telle me if and when a LIKE query uses an index? I've

compiled

postgres with locale support. Does that have any influence indexes?

thanks,

Alexander Jerusalem

---------------------------(end of

broadcast)---------------------------

"Richard Huxton" wrote:

I've done an EXPLAIN and I saw that no index was used although my SQL
statement had a wild card only at the end as you pointed out ('blah%'). My
guess is that this is because of the locale support.

Try like '^blah%' - maybe the additional hint will help (but maybe not). How
many rows did PG think it was going to access in the explain?

No, Richard, you can't mix regular expressions with SQL LIKE. For LIKE, '^'
is an ordinary character to match.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Blessed are the poor in spirit, for theirs is the
kingdom of heaven...Blessed are they which do hunger
and thirst after righteousness, for they shall be
filled...Blessed are the pure in heart, for they shall
see God." Matthew 5:3,6,8

3/15/2001 3:39:27 AM, Alexander Jerusalem <alexander.jerusalem@pop.chello.at>
wrote:

Thanks for your answer, Alex!

I've done an EXPLAIN and I saw that no index was used although my SQL
statement had a wild card only at the end as you pointed out ('blah%'). My
guess is that this is because of the locale support.

How large is the index in question? I recall reading that PostgreSQL doesn't
utilize an index if the overhead of using it was calculated as higher than a
sequential scan.

Brent

Hi,

The query I'm analyzing is this one:

SELECT count(*) from Person WHERE Person.pc_Id in (select pcpc.pc_fromid
from pcpc inner join corporation on pcpc.pc_toid = corporation.pc_id where
corporation.crp_name1 ilike 'Uni%');

Aggregate (cost=622544.96..622544.96 rows=1 width=0)
-> Seq Scan on person (cost=0.00..622526.04 rows=7565 width=0)
SubPlan
-> Materialize (cost=82.27..82.27 rows=1 width=36)
-> Nested Loop (cost=0.00..82.27 rows=1 width=36)
-> Seq Scan on corporation (cost=0.00..80.24
rows=1 width=12)
-> Index Scan using i_pcp_pc_toid on
pcpc (cost=0.00..2.02 rows=1 width=24)

The query takes over 3 seconds without any other load on the same machine
(Pentium III, 1 GHZ, 512 MB RAM) and I'm not sure why because on
MSSQLServer takes only a fraction. The tables are fairly small: the person
table has 7565 rows, the corporation table has 3059 and the relation table
(pcpc) has 2271 rows.

thanks,

Alexander Jerusalem

Alexander,

My guess is that MSSQL does a better job of optimizing the in clause.

In postgres an in clause will not ( currently ) use an index, so it
forces a sequential scan. However, you can change your query a bit and
use exists which will use an existing index.

SELECT count(*)
FROM Person
WHERE EXISTS (
SELECT pcpc.pc_fromid
FROM pcpc, corporation
WHERE pcpc.pc_toid = corporation.pc_id AND
Person.pc_Id = pcpc.pc_toid AND
corporation.crp_name1 like 'Uni%' AND
);

That will allow the query to use an index on Person.pc_Id and
pcpc.pc_toid assuming they exist.

--
Bill

On Thu, Mar 15, 2001 at 11:16:47PM +0100, Alexander Jerusalem wrote:

Hi,

The query I'm analyzing is this one:

SELECT count(*) from Person WHERE Person.pc_Id in (select pcpc.pc_fromid
from pcpc inner join corporation on pcpc.pc_toid = corporation.pc_id where
corporation.crp_name1 ilike 'Uni%');

Aggregate (cost=622544.96..622544.96 rows=1 width=0)
-> Seq Scan on person (cost=0.00..622526.04 rows=7565 width=0)
SubPlan
-> Materialize (cost=82.27..82.27 rows=1 width=36)
-> Nested Loop (cost=0.00..82.27 rows=1 width=36)
-> Seq Scan on corporation (cost=0.00..80.24
rows=1 width=12)
-> Index Scan using i_pcp_pc_toid on
pcpc (cost=0.00..2.02 rows=1 width=24)

The query takes over 3 seconds without any other load on the same machine
(Pentium III, 1 GHZ, 512 MB RAM) and I'm not sure why because on
MSSQLServer takes only a fraction. The tables are fairly small: the person
table has 7565 rows, the corporation table has 3059 and the relation table
(pcpc) has 2271 rows.

thanks,

Alexander Jerusalem

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

--
_____
/ ___/___ | Bill Huff / bhuff@colltech.com
/ /__ __/ | Voice: (512) 263-0770 x 262
/ /__/ / | Fax: (512) 263-8921
\___/ /ollective | Pager: 1-800-946-4646 # 1406217
\/echnologies |------[ http://www.colltech.com ] ------

It's your in subquery. Check out the FAQ:

http://postgresql.readysetnet.com/docs/faq-english.html#4.23

On Thu, 15 Mar 2001, Alexander Jerusalem wrote:

Alexander Jerusalem <alexander.jerusalem@pop.chello.at> writes:

The query I'm analyzing is this one:

SELECT count(*) from Person WHERE Person.pc_Id in (select pcpc.pc_fromid
from pcpc inner join corporation on pcpc.pc_toid = corporation.pc_id where
corporation.crp_name1 ilike 'Uni%');

^^^^^

Case-insensitive compares cannot use indexes in Postgres, because our
indexes are case-sensitive.

You could make an index on lower(crp_name1) and then do

... where lower(corporation.crp_name1) like 'uni%'

Actually, though, I don't believe that the lack of an indexscan on
corporation is the problem here. That's a tiny table and it's only
going to be scanned once in this plan. The real problem is the WHERE
... IN at the top level. Try changing to a WHERE EXISTS (see the PG
FAQ).

regards, tom lane

Thanks a lot Bill, that did it! Now it takes only a quarter of a second or
so :-)

Alexander Jerusalem

At 23:33 15.03.01, Bill Huff wrote:

Thank you all, I'm sorry I didn't read this FAQ first...

Alexander Jerusalem