Re: Oddity with view (now with test case)

On Nov 11, 2008, at 1:15 PM, Tom Lane wrote:
> "Jim 'Decibel!' Nasby" <jnasby(at)cashnetusa(dot)com> writes:
>> On Nov 10, 2008, at 9:20 PM, Tom Lane wrote:
>>> 8.3 gets it right though.
>
>> Doesn't seem to for me... :/
>
> Oh, I was looking at "select * from v2" not "select count(*) from v2".
> HEAD is a bit smarter about the latter than 8.3 is.

So here's something odd... in both 8.3 and HEAD from a while ago it
gives a better plan for SELECT * than for SELECT count(*):

decibel(at)platter(dot)local=# explain analyze select * from v2;
QUERY PLAN
------------------------------------------------------------------------
-----------------------------------------------------
Result (cost=0.00..254178.40 rows=10002315 width=72) (actual
time=0.049..8452.152 rows=9999999 loops=1)
-> Append (cost=0.00..254178.40 rows=10002315 width=72) (actual
time=0.048..5887.025 rows=9999999 loops=1)
-> Seq Scan on a (cost=0.00..154056.75 rows=10000175
width=14) (actual time=0.048..4207.482 rows=9999999 loops=1)
-> Hash Join (cost=37.67..98.50 rows=2140 width=40)
(actual time=0.002..0.002 rows=0 loops=1)
Hash Cond: (b.c_id = c.c_id)
-> Seq Scan on b (cost=0.00..31.40 rows=2140
width=8) (actual time=0.000..0.000 rows=0 loops=1)
-> Hash (cost=22.30..22.30 rows=1230 width=36)
(never executed)
-> Seq Scan on c (cost=0.00..22.30 rows=1230
width=36) (never executed)
Total runtime: 9494.162 ms
(9 rows)

decibel(at)platter(dot)local=# explain analyze select count(*) from v2;
QUERY
PLAN
------------------------------------------------------------------------
-----------------------------------------------------------------
Aggregate (cost=279184.19..279184.20 rows=1 width=0) (actual
time=13155.524..13155.524 rows=1 loops=1)
-> Append (cost=0.00..254178.40 rows=10002315 width=0) (actual
time=0.045..11042.562 rows=9999999 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..254058.50
rows=10000175 width=0) (actual time=0.045..8976.352 rows=9999999
loops=1)
-> Seq Scan on a (cost=0.00..154056.75
rows=10000175 width=14) (actual time=0.045..5936.930 rows=9999999
loops=1)
-> Subquery Scan "*SELECT* 2" (cost=37.67..119.90
rows=2140 width=0) (actual time=0.002..0.002 rows=0 loops=1)
-> Hash Join (cost=37.67..98.50 rows=2140 width=40)
(actual time=0.002..0.002 rows=0 loops=1)
Hash Cond: (b.c_id = c.c_id)
-> Seq Scan on b (cost=0.00..31.40 rows=2140
width=8) (actual time=0.001..0.001 rows=0 loops=1)
-> Hash (cost=22.30..22.30 rows=1230
width=36) (never executed)
-> Seq Scan on c (cost=0.00..22.30
rows=1230 width=36) (never executed)
Total runtime: 13155.642 ms
(11 rows)

decibel(at)platter(dot)local=# explain analyze select count(*) from (select
* from v2 offset 0) a;
QUERY
PLAN
------------------------------------------------------------------------
-----------------------------------------------------------------
Aggregate (cost=379207.34..379207.35 rows=1 width=0) (actual
time=12592.273..12592.274 rows=1 loops=1)
-> Limit (cost=0.00..254178.40 rows=10002315 width=72) (actual
time=0.173..11057.717 rows=9999999 loops=1)
-> Result (cost=0.00..254178.40 rows=10002315 width=72)
(actual time=0.172..9213.524 rows=9999999 loops=1)
-> Append (cost=0.00..254178.40 rows=10002315
width=72) (actual time=0.172..6608.656 rows=9999999 loops=1)
-> Seq Scan on a (cost=0.00..154056.75
rows=10000175 width=14) (actual time=0.171..4793.116 rows=9999999
loops=1)
-> Hash Join (cost=37.67..98.50 rows=2140
width=40) (actual time=0.002..0.002 rows=0 loops=1)
Hash Cond: (b.c_id = c.c_id)
-> Seq Scan on b (cost=0.00..31.40
rows=2140 width=8) (actual time=0.001..0.001 rows=0 loops=1)
-> Hash (cost=22.30..22.30 rows=1230
width=36) (never executed)
-> Seq Scan on c
(cost=0.00..22.30 rows=1230 width=36) (never executed)
Total runtime: 12592.442 ms
(11 rows)

And yes, explain overhead is huge...

decibel(at)platter(dot)local=# \timing
Timing is on.
decibel(at)platter(dot)local=# select count(*) from v2;
count
---------
9999999
(1 row)

Time: 6217.624 ms
decibel(at)platter(dot)local=#

--
Decibel! jnasby(at)cashnetusa(dot)com (512) 569-9461