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

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@colltech.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@postgresql.org)