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
