On 16.3.2015 20:43, Jim Nasby wrote: > On 3/13/15 7:12 PM, Tomas Vondra wrote: >> (4) I suspect many of the relations referenced in the views are not >> actually needed in the query, i.e. the join is performed but >> then it's just discarded because those columns are not used. >> Try to simplify the views as much has possible - remove all the >> tables that are not really necessary to run the query. If two >> queries need different tables, maybe defining two views is >> a better approach. > > A better alternative with multi-purpose views is to use an outer > join instead of an inner join. With an outer join if you ultimately > don't refer to any of the columns in a particular table Postgres will > remove the table from the query completely.
Really? Because a quick test suggests otherwise: db=# create table test_a (id int); CREATE TABLE db=# create table test_b (id int); CREATE TABLE db=# explain select test_a.* from test_a left join test_b using (id); QUERY PLAN ---------------------------------------------------------------------- Merge Left Join (cost=359.57..860.00 rows=32512 width=4) Merge Cond: (test_a.id = test_b.id) -> Sort (cost=179.78..186.16 rows=2550 width=4) Sort Key: test_a.id -> Seq Scan on test_a (cost=0.00..35.50 rows=2550 width=4) -> Sort (cost=179.78..186.16 rows=2550 width=4) Sort Key: test_b.id -> Seq Scan on test_b (cost=0.00..35.50 rows=2550 width=4) (8 rows) Also, how would that work with duplicate rows in the referenced table? -- Tomas Vondra http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance