Re: funny view/temp table problem with query

all explains:

Query without view:

QUERY PLAN
------------------------------------------------------------------------------------------------------------
Aggregate (cost=94419553.37..94419553.38 rows=1 width=16)
-> Sort (cost=94269553.37..94294553.37 rows=10000000 width=12)
Sort Key: ss.id, (((subplan))[i.i])
-> Nested Loop (cost=93414.56..92953067.54 rows=10000000 width=12)
-> Function Scan on generate_series i
(cost=0.00..12.50 rows=1000 width=4)
-> Materialize (cost=93414.56..93514.56 rows=10000 width=8)
-> Subquery Scan ss (cost=93279.56..93404.56
rows=10000 width=8)
-> Limit (cost=93279.56..93304.56
rows=10000 width=8)
-> Sort (cost=93279.56..95779.56
rows=1000000 width=8)
Sort Key: (random())
-> Seq Scan on accounts
(cost=0.00..21841.00 rows=1000000 width=8)
SubPlan
-> Limit (cost=9.25..9.27 rows=5 width=8)
-> Sort (cost=9.25..9.50 rows=100 width=8)
Sort Key: (random())
-> Result (cost=0.00..7.59 rows=100 width=8)
One-Time Filter: ($0 > (-1))
-> Seq Scan on packages
(cost=0.00..7.34 rows=100 width=8)
Filter: ((id >= 1) AND (id <= 100))
(19 rows)

With view used (notice, it is a bit different plan!)

QUERY PLAN
------------------------------------------------------------------------------------------------------
Aggregate (cost=2361251.70..2361260.98 rows=1 width=12)
-> Nested Loop (cost=111239.20..2111251.70 rows=100000000 width=12)
-> Function Scan on generate_series i (cost=0.00..12.50
rows=1000 width=4)
-> Materialize (cost=111239.20..112239.20 rows=100000 width=8)
-> Subquery Scan ss (cost=109889.20..111139.20
rows=100000 width=8)
-> Limit (cost=109889.20..110139.20 rows=100000 width=8)
-> Sort (cost=109889.20..112389.20
rows=1000000 width=8)
Sort Key: (random())
-> Seq Scan on accounts
(cost=0.00..21841.00 rows=1000000 width=8)
SubPlan
-> Limit (cost=9.25..9.27 rows=5 width=8)
-> Sort (cost=9.25..9.50 rows=100 width=8)
Sort Key: (random())
-> Result (cost=0.00..7.59 rows=100 width=8)
One-Time Filter: ($0 > (-1))
-> Seq Scan on packages (cost=0.00..7.34
rows=100 width=8)
Filter: ((id >= 1) AND (id <= 100))
(17 rows)

Create temp table based on view:

explain create temp table fooheh as select * from heh;
QUERY PLAN
------------------------------------------------------------------------------------
Subquery Scan ss (cost=109889.20..1037735.61 rows=100000 width=8)
-> Limit (cost=109889.20..110139.20 rows=100000 width=8)
-> Sort (cost=109889.20..112389.20 rows=1000000 width=8)
Sort Key: (random())
-> Seq Scan on accounts (cost=0.00..21841.00
rows=1000000 width=8)
SubPlan
-> Limit (cost=9.25..9.27 rows=5 width=8)
-> Sort (cost=9.25..9.50 rows=100 width=8)
Sort Key: (random())
-> Result (cost=0.00..7.59 rows=100 width=8)
One-Time Filter: ($0 > (-1))
-> Seq Scan on packages (cost=0.00..7.34
rows=100 width=8)
Filter: ((id >= 1) AND (id <= 100))
(13 rows)

and run simple query against temp table:

explain select count( distinct (id, v[i])) from fooheh, generate_series(1, 5) i;
QUERY PLAN
--------------------------------------------------------------------------------------
Aggregate (cost=3226173.36..3226173.37 rows=1 width=44)
-> Nested Loop (cost=2810.86..2868023.36 rows=143260000 width=44)
-> Function Scan on generate_series i (cost=0.00..12.50
rows=1000 width=4)
-> Materialize (cost=2810.86..4243.46 rows=143260 width=40)
-> Seq Scan on fooheh (cost=0.00..2667.60 rows=143260 width=40)
(5 rows)