Left joins and inheritance (table partitioning) - Mailing list pgsql-hackers

I've recently been playing with table partitioning limitations. Turning
over a large volume of data in inherited structures in a live
environment, and have run into a couple of snags in the planner.

The first is that LEFT JOIN will always do a sequential scan on all
inherited tables.

The second is that IN (1,4,6) works very differently than IN (SELECT id
FROM tab) when "tab" contains the values 1, 4, and 6. I'm not surprised
a straight left join failed, but I was surprised that IN failed to use
an index with enable_seqscan = off.


My fallback plan is to simply create a view and replace it to point to
the correct data segment when changes occur.



BEGIN;

CREATE TABLE key (keyword_id serial PRIMARY KEY);
INSERT INTO key DEFAULT VALUES;
INSERT INTO key DEFAULT VALUES;
INSERT INTO key DEFAULT VALUES;
INSERT INTO key DEFAULT VALUES;
INSERT INTO key DEFAULT VALUES;
INSERT INTO key DEFAULT VALUES;
INSERT INTO key DEFAULT VALUES;
INSERT INTO key DEFAULT VALUES;
INSERT INTO key DEFAULT VALUES;

CREATE TABLE key2 (keyword_id integer PRIMARY KEY);
INSERT INTO key2 VALUES (1);
INSERT INTO key2 VALUES (6);
INSERT INTO key2 VALUES (8);
ANALYZE key2;


CREATE TABLE foo 
( keyword_id integer PRIMARY KEY    REFERENCES key);

CREATE TABLE foo2
( PRIMARY KEY (keyword_id)
, FOREIGN KEY (keyword_id) REFERENCES KEY
) INHERITS (foo);

INSERT INTO foo2 VALUES (1);
INSERT INTO foo2 VALUES (2);
INSERT INTO foo2 VALUES (3);
INSERT INTO foo2 VALUES (4);
INSERT INTO foo2 VALUES (5);
INSERT INTO foo2 VALUES (6);
INSERT INTO foo2 VALUES (7);
INSERT INTO foo2 VALUES (8);
INSERT INTO foo2 VALUES (9);

SET enable_seqscan = off;

EXPLAIN
SELECT * FROM key2 LEFT JOIN foo USING (keyword_id) ;
                                        QUERY PLAN
---------------------------------------------------------------------------------------------Hash Left Join
(cost=200000073.50..200000191.74rows=6 width=4)  Hash Cond: ("outer".keyword_id = "inner".keyword_id)  ->  Index Scan
usingkey2_pkey on key2  (cost=0.00..3.03 rows=3
 
width=4)  ->  Hash  (cost=200000062.80..200000062.80 rows=4280 width=4)        ->  Append
(cost=100000000.00..200000062.80rows=4280 width=4)              ->  Seq Scan on foo  (cost=100000000.00..100000031.40
 
rows=2140 width=4)              ->  Seq Scan on foo2 foo
(cost=100000000.00..100000031.40 rows=2140 width=4)
(7 rows)


EXPLAIN
SELECT * FROM key2
LEFT JOIN (SELECT keyword_id            FROM foo           WHERE keyword_id IN (SELECT keyword_id FROM key2)         )
AStab USING (keyword_id) ;
 
                                           QUERY PLAN
---------------------------------------------------------------------------------------------------Merge Left Join
(cost=200000087.38..200000090.46rows=3 width=4)  Merge Cond: ("outer".keyword_id = "inner".keyword_id)  ->  Index Scan
usingkey2_pkey on key2  (cost=0.00..3.03 rows=3
 
width=4)  ->  Sort  (cost=200000087.38..200000087.39 rows=6 width=4)        Sort Key: public.foo.keyword_id        ->
HashIN Join  (cost=100000003.04..200000087.30 rows=6
 
width=4)              Hash Cond: ("outer".keyword_id = "inner".keyword_id)              ->  Append
(cost=100000000.00..200000062.80rows=4280
 
width=4)                    ->  Seq Scan on foo
(cost=100000000.00..100000031.40 rows=2140 width=4)                    ->  Seq Scan on foo2 foo
(cost=100000000.00..100000031.40 rows=2140 width=4)              ->  Hash  (cost=3.03..3.03 rows=3 width=4)
      ->  Index Scan using key2_pkey on key2
 
(cost=0.00..3.03 rows=3 width=4)
(12 rows)

EXPLAIN
SELECT * FROM key2
LEFT JOIN (SELECT keyword_id            FROM foo           WHERE keyword_id IN (1,6,8)         ) AS tab USING
(keyword_id);
 
                                          QUERY PLAN
------------------------------------------------------------------------------------------------Merge Left Join
(cost=22.08..25.16rows=3 width=4)  Merge Cond: ("outer".keyword_id = "inner".keyword_id)  ->  Index Scan using
key2_pkeyon key2  (cost=0.00..3.03 rows=3
 
width=4)  ->  Sort  (cost=22.08..22.09 rows=6 width=4)        Sort Key: public.foo.keyword_id        ->  Append
(cost=3.01..22.00rows=6 width=4)              ->  Bitmap Heap Scan on foo  (cost=3.01..9.50 rows=3
 
width=4)                    Recheck Cond: ((keyword_id = 1) OR (keyword_id = 6)
OR (keyword_id = 8))                    ->  BitmapOr  (cost=3.01..3.01 rows=3 width=0)                          ->
BitmapIndex Scan on foo_pkey
 
(cost=0.00..1.00 rows=1 width=0)                                Index Cond: (keyword_id = 1)
-> Bitmap Index Scan on foo_pkey
 
(cost=0.00..1.00 rows=1 width=0)                                Index Cond: (keyword_id = 6)
-> Bitmap Index Scan on foo_pkey
 
(cost=0.00..1.00 rows=1 width=0)                                Index Cond: (keyword_id = 8)              ->  Bitmap
HeapScan on foo2 foo  (cost=6.01..12.50
 
rows=3 width=4)                    Recheck Cond: ((keyword_id = 1) OR (keyword_id = 6)
OR (keyword_id = 8))                    ->  BitmapOr  (cost=6.01..6.01 rows=3 width=0)                          ->
BitmapIndex Scan on foo2_pkey
 
(cost=0.00..2.00 rows=1 width=0)                                Index Cond: (keyword_id = 1)
-> Bitmap Index Scan on foo2_pkey
 
(cost=0.00..2.00 rows=1 width=0)                                Index Cond: (keyword_id = 6)
-> Bitmap Index Scan on foo2_pkey
 
(cost=0.00..2.00 rows=1 width=0)                                Index Cond: (keyword_id = 8)
(24 rows)


ROLLBACK;



--