Добрый день,
Подскажите, почему так - есть 2 таблицы c несколькими тысячами записей и индексом:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
CREATE TABLE A (NAMES JSONB);
CREATE TABLE B (NAME JSONB);
-- INSERT SOME TEST DATA
DO $$
BEGIN
FOR i IN 0..1000 LOOP
INSERT INTO A VALUES (TO_JSON(ARRAY['X'||i, 'Y'||i]::VARCHAR[])::JSONB);
INSERT INTO B VALUES (TO_JSON(ARRAY['X'||i]::VARCHAR[])::JSONB);
INSERT INTO B VALUES (TO_JSON(ARRAY['Y'||i]::VARCHAR[])::JSONB);
END LOOP;
END;
$$;
CREATE INDEX ON A USING GIN (NAMES);
После этого немного тюнинга и апдэйт статистики:
1.
2.
3.
SET enable_seqscan = off;
SET enable_nestloop = off;
VACUUM ANALYZE;
И делаю идентичные запросы с разницой INNER JOIN vs LEFT OUTER LOIN:
1.
2.
3.
EXPLAIN ANALYZE
SELECT * FROM A
JOIN B ON (A.NAMES @> B.NAME)
'Nested Loop (cost=20000000000.04..20000008170.60 rows=2004 width=32) (actual time=0.079..25.214 rows=2002 loops=1)'
' -> Seq Scan on b (cost=10000000000.00..10000000042.02 rows=2002 width=12) (actual time=0.031..0.663 rows=2002 loops=1)'
' -> Bitmap Heap Scan on a (cost=0.04..4.05 rows=1 width=20) (actual time=0.010..0.010 rows=1 loops=2002)'
' Recheck Cond: (names @> b.name)'
' Heap Blocks: exact=2002'
' -> Bitmap Index Scan on a_names_idx1 (cost=0.00..0.04 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=2002)'
' Index Cond: (names @> b.name)'
'Planning time: 0.196 ms'
'Execution time: 25.583 ms'
и
1.
2.
3.
EXPLAIN ANALYZE
SELECT * FROM A
LEFT OUTER JOIN B ON (A.NAMES @> B.NAME)
'Nested Loop Left Join (cost=30000000000.00..30000030134.07 rows=2004 width=32) (actual time=0.077..1793.021 rows=2002 loops=1)'
' Join Filter: (a.names @> b.name)'
' Rows Removed by Join Filter: 2002000'
' -> Seq Scan on a (cost=10000000000.00..10000000027.01 rows=1001 width=20) (actual time=0.033..0.439 rows=1001 loops=1)'
' -> Materialize (cost=10000000000.00..10000000052.03 rows=2002 width=12) (actual time=0.000..0.226 rows=2002 loops=1001)'
' -> Seq Scan on b (cost=10000000000.00..10000000042.02 rows=2002 width=12) (actual time=0.024..0.378 rows=2002 loops=1)'
'Planning time: 0.156 ms'
'Execution time: 1793.319 ms'
В обоих случаях результат 2002 записей, но из-за того что LEFT OUTER JOIN не использует индекс запрос выполняется в тысячу раз медленнее. Подскажите как побороть? И почему планировщик не пользует индекс когда он есть?
