Re: Oddity with view (now with test case)

On Nov 10, 2008, at 9:20 PM, Tom Lane wrote:
> "Jim 'Decibel!' Nasby" <jnasby(at)cashnetusa(dot)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(at)platter(dot)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(at)platter(dot)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(at)platter(dot)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(at)platter(dot)local=#
--
Decibel! jnasby(at)cashnetusa(dot)com (512) 569-9461