From: | Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com> |
---|---|
To: | PostgreSQL <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: funny view/temp table problem with query |
Date: | 2009-02-25 13:20:21 |
Message-ID: | 2f4958ff0902250520g3a7a126ay67a2d643627d6368@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Gregory Stark | 2009-02-25 13:21:12 | Re: Using xmin to identify last modified rows |
Previous Message | Ashish Karalkar | 2009-02-25 12:38:14 | Re: Restore DB |