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)
)
;
[/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
