Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have expected that selecting from a view would not join the tables from which I don't query any values, but it doesn't seem to be the case.

I have a bunch of tables with the following structure:

CREATE TABLE ind1 (year integer, id integer, ind1 float);
ALTER TABLE ind1 ADD PRIMARY KEY (year, id);
INSERT INTO ind1 VALUES (2000, 1, 0.0);
INSERT INTO ind1 VALUES (2000, 2, 0.3);
INSERT INTO ind1 VALUES (2000, 3, 1.1);
INSERT INTO ind1 VALUES (2001, 1, 0.0);
INSERT INTO ind1 VALUES (2001, 2, 0.3);
INSERT INTO ind1 VALUES (2001, 3, 1.1);
INSERT INTO ind1 VALUES (2002, 1, 0.0);
INSERT INTO ind1 VALUES (2002, 2, 0.3);
INSERT INTO ind1 VALUES (2002, 3, 1.1);
VACUUM ANALYZE ind1;

There is also ind2, ind3, ... . The set of possible year and id values is identical in all tables, and also given in an extra table:

CREATE TABLE id (id integer PRIMARY KEY);
INSERT INTO id VALUES (1);
INSERT INTO id VALUES (2);
INSERT INTO id VALUES (3);
VACUUM ANALYZE id;

Now I create a view to show all entries for a given year:

CREATE VIEW ind_2000 AS SELECT id, ind1, ind2, ind3
  FROM (SELECT id, 2000 AS year FROM id) T
  LEFT JOIN ind1 USING (year, id)
  LEFT JOIN ind2 USING (year, id)
  LEFT JOIN ind3 USING (year, id);

However, when selecting only the columns id, ind1 and ind2, still all tables are joined, although this is not necessary to perform the query correctly:

EXPLAIN ANALYZE SELECT id, ind1, ind2 FROM ind_2000;

                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=3.45..4.63 rows=3 width=20) (actual time=0.150..0.157 rows=3 loops=1)
   Hash Cond: (id.id = ind1.id)
   ->  Hash Left Join  (cost=2.30..3.43 rows=3 width=12) (actual time=0.118..0.124 rows=3 loops=1)
         Hash Cond: (id.id = ind2.id)
         ->  Hash Left Join  (cost=1.15..2.23 rows=3 width=4) (actual time=0.087..0.089 rows=3 loops=1)
               Hash Cond: (id.id = ind3.id)
               ->  Seq Scan on id  (cost=0.00..1.03 rows=3 width=4) (actual time=0.007..0.007 rows=3 loops=1)
               ->  Hash  (cost=1.11..1.11 rows=3 width=4) (actual time=0.028..0.028 rows=3 loops=1)
                     ->  Seq Scan on ind3  (cost=0.00..1.11 rows=3 width=4) (actual time=0.019..0.024 rows=3 loops=1)
                           Filter: (2000 = year)
         ->  Hash  (cost=1.11..1.11 rows=3 width=12) (actual time=0.012..0.012 rows=3 loops=1)
               ->  Seq Scan on ind2  (cost=0.00..1.11 rows=3 width=12) (actual time=0.007..0.011 rows=3 loops=1)
                     Filter: (2000 = year)
   ->  Hash  (cost=1.11..1.11 rows=3 width=12) (actual time=0.011..0.011 rows=3 loops=1)
         ->  Seq Scan on ind1  (cost=0.00..1.11 rows=3 width=12) (actual time=0.007..0.009 rows=3 loops=1)
               Filter: (2000 = year)
 Total runtime: 0.304 ms
(17 rows)

The entire code is in this gist.

How do I tell the optimizer to avoid the useless joins?

(Using PostgreSQL 8.4)

EDIT: The error persists even when dropping the time dimension, see option1.sql in the gist.

share|improve this question
    
I don't think this is currently possible with your setup. The "Postgres" way to do partition is using table inheritance: postgresql.org/docs/9.2/static/ddl-partitioning.html –  a_horse_with_no_name Jun 11 '13 at 22:41
    
@a_horse_with_no_name: I'm seeing this behavior even when no second year column is involved: gist.github.com/krlmlr/5761307#file-option1-sql –  krlmlr Jun 11 '13 at 22:48
    
Again: partition pruning will only work if you use table inheritance with check constraints as described in the manual (and then you don't need that complicated view either, you just query the base table) –  a_horse_with_no_name Jun 11 '13 at 22:49
    
@a_horse_with_no_name: This sounds interesting. Does data duplication occur? Which inheritance hierarchy do you propose? –  krlmlr Jun 11 '13 at 23:00
1  
Please show explain analyze output –  Craig Ringer Jun 12 '13 at 3:49

2 Answers 2

Outer join removal is implemented in PostgreSQL 9.0. I suggest you try your example with that version (or an even more recent one).

share|improve this answer
    
Indeed, with Postgres 9.1 on Ubuntu 13.04 the unused table is not part of the execution plan anymore. Is there any way to achieve this with 8.4? Another user mentioned table inheritance, but I don't see yet how to implement it. –  krlmlr Jun 14 '13 at 21:17

You're only considering that there could be zero or one joined rows in ind3 -- if there were seven then it would affect the number of rows returned from the query, so it's not correct to say that the join is irrelevant.

Even if that were not the case, it's a matter of whether a potential optimising operation is implemented in the query optimiser. Recent Oracle versions will avoid an unnecessary join in querying a view or inline view if constraints are in place to enforce the logic that omitting the join will make no difference to the query but I'm not sure that they would deal with this sort of situation.

You would almost certainly still see the potential join in the execution plan, so that is not really the way to judge whether any work has been expended in the join.

share|improve this answer
    
I was thinking that it can't be more than one row because of the primary key constraint... –  krlmlr Jun 12 '13 at 18:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.