Re: Re: LIKE and indexes? - Mailing list pgsql-general

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