pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
optimizer, view, union 2+ messages / 2 participants [nested] [flat]
* optimizer, view, union @ 2005-06-23 17:31 Markus Bertheau <twanger@bluetwanger.de> 0 siblings, 1 reply; 2+ messages in thread From: Markus Bertheau @ 2005-06-23 17:31 UTC (permalink / raw) To: pgsql-sql Hi, 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' into SELECT 'foo' AS class, * FROM foo? The subselect is a view here, that's why I don't just use the second query. Markus -- Markus Bertheau <twanger@bluetwanger.de> Attachments: signature.asc (application/pgp-signature, 188B) download ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: optimizer, view, union @ 2005-06-23 19:28 Tom Lane <tgl@sss.pgh.pa.us> parent: Markus Bertheau <twanger@bluetwanger.de> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2005-06-23 19:28 UTC (permalink / raw) To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: pgsql-sql 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-06-23 19:28 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2005-06-23 17:31 optimizer, view, union Markus Bertheau <twanger@bluetwanger.de> 2005-06-23 19:28 ` Tom Lane <tgl@sss.pgh.pa.us>
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