Re: Re: Re: LIKE and indexes?

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