From: Tom Lane <tgl@sss.pgh.pa.us>
To: Markus Bertheau <twanger@bluetwanger.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: optimizer, view, union
Date: Thu, 23 Jun 2005 15:28:50 -0400
Message-ID: <7743.1119554930@sss.pgh.pa.us> (raw)
In-Reply-To: <1119547917.7430.12.camel@localhost.localdomain>
References: <1119547917.7430.12.camel@localhost.localdomain>
Markus Bertheau <twanger@bluetwanger.de> writes:
> Can pg transform
> SELECT * FROM (
> SELECT 'foo' AS class, id FROM foo
> UNION ALL
> SELECT 'bar' AS class, id FROM bar
> ) AS a WHERE class = 'foo'
[ experiments... ] Yes, if you spell it like this:
regression=# explain SELECT * FROM (
regression(# SELECT 'foo'::text AS class, id FROM foo
regression(# UNION ALL
regression(# SELECT 'bar'::text AS class, id FROM bar
regression(# ) AS a WHERE class = 'foo';
QUERY PLAN
-------------------------------------------------------------------------
Append (cost=0.00..105.60 rows=4280 width=4)
-> Subquery Scan "*SELECT* 1" (cost=0.00..52.80 rows=2140 width=4)
-> Seq Scan on foo (cost=0.00..31.40 rows=2140 width=4)
-> Subquery Scan "*SELECT* 2" (cost=0.00..52.80 rows=2140 width=4)
-> Result (cost=0.00..31.40 rows=2140 width=4)
One-Time Filter: false
-> Seq Scan on bar (cost=0.00..31.40 rows=2140 width=4)
(7 rows)
If unadorned, the literals get caught up in some type-conversion issues.
(You don't really want them in the output of a view anyway; "unknown"
type columns are bad news.)
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, twanger@bluetwanger.de
Subject: Re: optimizer, view, union
In-Reply-To: <7743.1119554930@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox