Re: Re: Re: LIKE and indexes?

Alexander Jerusalem <alexander(dot)jerusalem(at)pop(dot)chello(dot)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