Re: Re: Re: LIKE and indexes?

Thanks a lot Bill, that did it! Now it takes only a quarter of a second or
so :-)

Alexander Jerusalem

At 23:33 15.03.01, Bill Huff wrote:

> 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 ] ------
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)