Re: Re: Re: LIKE and indexes?

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:

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