Re: Re: Re: LIKE and indexes?

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(at)colltech(dot)com
/ /__ __/ | Voice: (512) 263-0770 x 262
/ /__/ / | Fax: (512) 263-8921
\___/ /ollective | Pager: 1-800-946-4646 # 1406217
\/echnologies |------[ http://www.colltech.com ] ------