NOT IN >2hrs vs EXCEPT < 2 sec.

2 questions:

1) Different costs for same actions. Doing an explain on 2 nearly identical
queries both involving the same Index scan on same table has 2 widely
different costs for same Index scan 303375872.86 vs. 12576.70

2) Simple query using NOT IN (subquery)was killed after 2 hrs, using the
same query (query) except (query) ran in < 2 sec.

Summary:

On devel box (Unix PG version 8.3.5) with no other database activity or
system activity after immediately completing a vacuum analyze.

The original query (below) was running for over 2 hrs and was killed.:

select distinct ciknum into tmpnocikinowner from cik where ciknum not in
(select cik from owner_cik_master);

est total cost: 303375872.86, for Index Scan: 303375616.75

Simple query broken down: explain select distinct ciknum into
tmpnocikinowner from cik ;

est total cost: 12576.70, for Index Scan: 12064.49

and

select cik from owner_cik_master

est total cost: 2587.36, for Index Scan: N/A

Actual time, the query was killed after 2hrs,

However, we ran:

select distinct ciknum into tmpnocikinowner from cik ; - actual time 861.487
ms
(select ciknum from tmpnocikinowner) except (select cik from
owner_cik_master); - actual time 1328.094 ms

##### Console log below with details ######

devel=# explain select distinct ciknum into tmpnocikinowner from cik where
ciknum not in (select cik from owner_cik_master);
QUERY
PLAN
------------------------------------------------------------------------------------------------
Unique (cost=3506.21..303375872.86 rows=71946 width=8)
-> Index Scan using cik_ciknum_idx on cik (cost=3506.21..303375616.75
rows=102444 width=8)
Filter: (NOT (subplan))
SubPlan
-> Materialize (cost=3506.21..6002.40 rows=186019 width=4)
-> Seq Scan on owner_cik_master (cost=0.00..2684.19
rows=186019 width=4)
(6 rows)

Time: 0.723 ms
devel=# explain select ciknum into tmpnocikinowner from cik where ciknum not
in (select cik from owner_cik_master);
QUERY
PLAN
--------------------------------------------------------------------------------------
Seq Scan on cik (cost=3506.21..303367660.13 rows=102444 width=8)
Filter: (NOT (subplan))
SubPlan
-> Materialize (cost=3506.21..6002.40 rows=186019 width=4)
-> Seq Scan on owner_cik_master (cost=0.00..2684.19 rows=186019
width=4)
(5 rows)

Time: 0.588 ms
devel=# explain select ciknum::int into tmpnocikinowner from cik where
ciknum::int not in (select cik::int from owner_cik_master);
QUERY
PLAN
--------------------------------------------------------------------------------------
Seq Scan on cik (cost=3506.21..303368428.46 rows=102444 width=8)
Filter: (NOT (subplan))
SubPlan
-> Materialize (cost=3506.21..6002.40 rows=186019 width=4)
-> Seq Scan on owner_cik_master (cost=0.00..2684.19 rows=186019
width=4)
(5 rows)

Time: 0.918 ms
devel=# explain select ciknum into tmpnocikinowner from cik
;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on cik (cost=0.00..4107.87 rows=204887 width=8)
(1 row)

Time: 0.438 ms
devel=# explain select distinct ciknum into tmpnocikinowner from cik ;
QUERY
PLAN
-----------------------------------------------------------------------------------------
Unique (cost=0.00..12576.70 rows=143891 width=8)
-> Index Scan using cik_ciknum_idx on cik (cost=0.00..12064.49
rows=204887 width=8)
(2 rows)

Time: 0.468 ms
devel=# select distinct ciknum into tmpnocikinowner from cik ;
SELECT
Time: 861.487 ms

devel=# explain select ciknum from tmpnocikinowner where ciknum not in
(select cik from owner_cik_master);
QUERY
PLAN
--------------------------------------------------------------------------------------
Seq Scan on tmpnocikinowner (cost=3506.21..261092922.31 rows=88168
width=8)
Filter: (NOT (subplan))
SubPlan
-> Materialize (cost=3506.21..6002.40 rows=186019 width=4)
-> Seq Scan on owner_cik_master (cost=0.00..2684.19 rows=186019
width=4)
(5 rows)

Time: 0.629 ms

devel=# explain select cik from owner_cik_master;
QUERY PLAN
------------------------------------------------------------------------
Seq Scan on owner_cik_master (cost=0.00..2684.19 rows=186019 width=4)
(1 row)

Time: 0.415 ms
devel=# explain select ciknum from tmpnocikinowner;;
QUERY PLAN
-----------------------------------------------------------------------
Seq Scan on tmpnocikinowner (cost=0.00..2587.36 rows=176336 width=8)
(1 row)

Time: 0.413 ms
devel=# explain (select ciknum from tmpnocikinowner) except (select cik
from owner_cik_master);
QUERY
PLAN
------------------------------------------------------------------------------------------------
SetOp Except (cost=47309.23..49121.00 rows=36236 width=8)
-> Sort (cost=47309.23..48215.12 rows=362355 width=8)
Sort Key: "*SELECT* 1".ciknum
-> Append (cost=0.00..8895.10 rows=362355 width=8)
-> Subquery Scan "*SELECT* 1" (cost=0.00..4350.72
rows=176336 width=8)
-> Seq Scan on tmpnocikinowner (cost=0.00..2587.36
rows=176336 width=8)
-> Subquery Scan "*SELECT* 2" (cost=0.00..4544.38
rows=186019 width=4)
-> Seq Scan on owner_cik_master (cost=0.00..2684.19
rows=186019 width=4)
(8 rows)

Time: 0.625 ms
devel=# (select ciknum from tmpnocikinowner) except (select cik from
owner_cik_master);
ciknum
--------
(0 rows)

Time: 1328.094 ms