Re: how to make query more efficient? - Mailing list pgsql-general

On Wed, 19 Mar 2003, Johnson, Shaunn wrote:

> Howdy:
>
> Running PostgreSQL 7.2.1 on RedHat Linux 7.2.
>
> I'm trying to run a query and it seems by using EXPLAIN
> that it will take a long time.  Is there a way to get the
> results in a more efficient manner?  Basically, I want
> a list of records that are NOT in the table ref_person.
>
> [snip query]
> explain
>
> select
> x.contract,
> x.mbr_num,
> x.pref_contract,
> x.pref_mbr_num,
> x.person_id
> --into
> --t_refperson_diff
> from
> ref_person x
> where
> x.contract not in
> (
>         select
>         r.contract
>         --r.mbr_num
>         from
>         ref_person r, db2_cn1pmemb d
>         where
>         (d.c_contract_num, d.c_mbr_num) =
>         (r.contract, r.mbr_num)
> )
> ;

Are you sure that's the correct query? I'm not going to try it but I'm
surprised that works at all. That's an awful lot of rows it thinks it wants to
return, perhaps that's a feature of this odd syntax you've used.

Anyway, apart from that the main thing is to rewrite this so it avoids the IN
clause. Try doing something like:

select
  x.contract,
  x.mbr_num,
  x.pref_contract,
  x.pref_mbr_num,
  x.person_id
 from
  ref_person x
 where
  not exists (
         select
        1
         from
             ref_person r, db2_cn1pmemb d
         where
        r.contract = x.contract
        and
             d.c_contract_num = r.contract
        and
         d.c_mbr_num = r.mbr_num
  )


>
> [/snip]
>
> [snip explain results]
>
> psql:./tom_refperson_diff.sql:25: NOTICE:  QUERY PLAN:
>
> Seq Scan on ref_person x  (cost=0.00..1425041431903.38 rows=886436 width=48)
>   SubPlan
>     ->  Materialize  (cost=803803.88..803803.88 rows=2167192 width=44)
>           ->  Merge Join  (cost=762748.49..803803.88 rows=2167192 width=44)
>                 ->  Sort  (cost=371783.06..371783.06 rows=1772872 width=22)
>                       ->  Seq Scan on ref_person r  (cost=0.00..37959.72
> rows=1772872 width=22)
>                 ->  Sort  (cost=390965.43..390965.43 rows=1646129 width=22)
>                       ->  Seq Scan on db2_cn1pmemb d  (cost=0.00..82307.29
> rows=1646129 width=22)
>
> EXPLAIN
>
> [/snip explain results]
>
> [snip index]
>
> bcn=> \d db2_cn1pmemb_i
>      Index "db2_cn1pmemb_i"
>      Column     |     Type
> ----------------+---------------
>  c_contract_num | character(12)
>  c_mbr_num      | character(2)
> btree
>
> --
>
> bcn=> \d ref_person_pkey
>  Index "ref_person_pkey"
>   Column  |     Type
> ----------+---------------
>  contract | character(12)
>  mbr_num  | character(2)
> unique btree (primary key)
>
> [/snip index | primary key]
>
>
> Suggestions?  Thanks!
>
>
> -X
>

--
Nigel J. Andrews