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.74 rows=6 width=4)
Hash Cond: ("outer".keyword_id = "inner".keyword_id)
-> Index Scan using key2_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.80 rows=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)
) AS tab USING (keyword_id) ;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Merge Left Join (cost=200000087.38..200000090.46 rows=3 width=4)
Merge Cond: ("outer".keyword_id = "inner".keyword_id)
-> Index Scan using key2_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
-> Hash IN Join (cost=100000003.04..200000087.30 rows=6
width=4)
Hash Cond: ("outer".keyword_id = "inner".keyword_id)
-> Append (cost=100000000.00..200000062.80 rows=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.16 rows=3 width=4)
Merge Cond: ("outer".keyword_id = "inner".keyword_id)
-> Index Scan using key2_pkey on 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.00 rows=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)
-> Bitmap Index 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 Heap Scan 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)
-> Bitmap Index 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;
--
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
