Re: Performance issues

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