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)