Re: Oddity with view (now with test case)

On Nov 10, 2008, at 9:20 PM, Tom Lane wrote:
> "Jim 'Decibel!' Nasby" <jnasby@cashnetusa.com> writes:
>> On Nov 10, 2008, at 1:31 PM, Tom Lane wrote:
>>> On my machine this runs about twice as fast as the original view.
>
>> Am I missing some magic? I'm still getting the subquery scan.
>
> Hmm, I'm getting a core dump :-( ... this seems to be busted in HEAD.
> 8.3 gets it right though.

Doesn't seem to for me... :/

decibel@platter.local=# select version();

version
------------------------------------------------------------------------
-----------------------------------------------------------------
  PostgreSQL 8.3.5 on i386-apple-darwin8.11.1, compiled by GCC i686-
apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5370)
(1 row)

Time: 0.250 ms
decibel@platter.local=# explain select count(*) from v2;
                                       QUERY PLAN
------------------------------------------------------------------------
--------------
  Aggregate  (cost=279184.19..279184.20 rows=1 width=0)
    ->  Append  (cost=0.00..254178.40 rows=10002315 width=0)
          ->  Subquery Scan "*SELECT* 1"  (cost=0.00..254058.50
rows=10000175 width=0)
                ->  Seq Scan on a  (cost=0.00..154056.75
rows=10000175 width=14)
          ->  Subquery Scan "*SELECT* 2"  (cost=37.67..119.90
rows=2140 width=0)
                ->  Hash Join  (cost=37.67..98.50 rows=2140 width=40)
                      Hash Cond: (b.c_id = c.c_id)
                      ->  Seq Scan on b  (cost=0.00..31.40 rows=2140
width=8)
                      ->  Hash  (cost=22.30..22.30 rows=1230 width=36)
                            ->  Seq Scan on c  (cost=0.00..22.30
rows=1230 width=36)
(10 rows)

Time: 0.923 ms
decibel@platter.local=# \d v2
        View "public.v2"
  Column |  Type   | Modifiers
--------+---------+-----------
  a      | integer |
  b      | text    |
  c_id   | integer |
  c_text | text    |
View definition:
  SELECT a.a, a.b, NULL::integer AS c_id, NULL::text AS c_text
    FROM a
UNION ALL
  SELECT b.a, NULL::text AS b, b.c_id, c.c_text
    FROM b
    JOIN c ON b.c_id = c.c_id;

decibel@platter.local=#
--
Decibel! jnasby@cashnetusa.com (512) 569-9461