Re: Oddity with view (now with test case) - Mailing list pgsql-performance

On Nov 11, 2008, at 1:15 PM, Tom Lane wrote:
> "Jim 'Decibel!' Nasby" <jnasby@cashnetusa.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@platter.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@platter.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@platter.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@platter.local=# \timing
Timing is on.
decibel@platter.local=# select count(*) from v2;
   count
---------
  9999999
(1 row)

Time: 6217.624 ms
decibel@platter.local=#

--
Decibel! jnasby@cashnetusa.com (512) 569-9461