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