Доброе время суток. Есть 2 таблицы
CREATE TABLE part.indexed_result_0
(
-- Унаследована from table indexed_result: id bigint NOT NULL DEFAULT nextval('indexed_result__id_seq'::regclass),
-- Унаследована from table indexed_result: id_article bigint,
-- Унаследована from table indexed_result: key_in_spr bigint,
CONSTRAINT pk_indexed_result_17 PRIMARY KEY (id),
CONSTRAINT indexed_result_17_id_article_check CHECK (id_article >= (17 * 100000) AND id_article < ((17 + 1) * 100000))
)
INHERITS (indexed_result);
CREATE INDEX idx_indexed_result_17_id_article
ON part.indexed_result_17
USING btree
(id_article);
CREATE TABLE spr_article
(
id bigint NOT NULL,
name text,
is_check boolean DEFAULT false,
CONSTRAINT pk_spr_article_ PRIMARY KEY (id)
)
indexed_result ~10 млн.записей , spr_article_old ~ 2 млн.записей. indexed_result разбит на партиции по признаку id_article (по ~ 0.5 млн. записей в таблице).
Есть запрос
1.
2.
3.
4.
5.
6.
7.
8.
9.
SELECT
id_article,
id_spr_object,
key_in_spr,
rank
FROM
indexed_result ir, spr_article_old sa
WHERE
ir.id_article = sa.id
Планировщик отказывается от использования индекса, выдавая следующий результат
"Hash Join (cost=250594.40..698734.05 rows=9215951 width=32)"
" Hash Cond: (ir.id_article = sa.id)"
" -> Append (cost=0.00..168969.50 rows=9215951 width=24)"
" -> Seq Scan on indexed_result ir (cost=0.00..0.00 rows=1 width=24)"
" -> Seq Scan on indexed_result_0 ir (cost=0.00..5569.76 rows=303776 width=24)"
" -> Seq Scan on indexed_result_1 ir (cost=0.00..5404.77 rows=294777 width=24)"
" -> Seq Scan on indexed_result_10 ir (cost=0.00..8606.15 rows=469415 width=24)"
" -> Seq Scan on indexed_result_11 ir (cost=0.00..8764.31 rows=478031 width=24)"
" -> Seq Scan on indexed_result_12 ir (cost=0.00..8214.69 rows=448069 width=24)"
" -> Seq Scan on indexed_result_13 ir (cost=0.00..9048.07 rows=493507 width=24)"
" -> Seq Scan on indexed_result_14 ir (cost=0.00..8859.08 rows=483208 width=24)"
" -> Seq Scan on indexed_result_15 ir (cost=0.00..9212.89 rows=502489 width=24)"
" -> Seq Scan on indexed_result_16 ir (cost=0.00..9004.21 rows=491121 width=24)"
" -> Seq Scan on indexed_result_17 ir (cost=0.00..8651.94 rows=471894 width=24)"
" -> Seq Scan on indexed_result_18 ir (cost=0.00..9210.47 rows=502347 width=24)"
" -> Seq Scan on indexed_result_19 ir (cost=0.00..9215.08 rows=502608 width=24)"
" -> Seq Scan on indexed_result_2 ir (cost=0.00..6957.68 rows=379468 width=24)"
" -> Seq Scan on indexed_result_20 ir (cost=0.00..8174.59 rows=445859 width=24)"
" -> Seq Scan on indexed_result_3 ir (cost=0.00..6537.63 rows=356563 width=24)"
" -> Seq Scan on indexed_result_4 ir (cost=0.00..6718.68 rows=366468 width=24)"
" -> Seq Scan on indexed_result_5 ir (cost=0.00..6002.80 rows=327380 width=24)"
" -> Seq Scan on indexed_result_6 ir (cost=0.00..8276.27 rows=451427 width=24)"
" -> Seq Scan on indexed_result_7 ir (cost=0.00..9236.98 rows=503798 width=24)"
" -> Seq Scan on indexed_result_8 ir (cost=0.00..8548.10 rows=466210 width=24)"
" -> Seq Scan on indexed_result_9 ir (cost=0.00..8755.35 rows=477535 width=24)"
" -> Hash (cost=215383.18..215383.18 rows=2025618 width=16)"
" -> Seq Scan on spr_article_old sa (cost=0.00..215383.18 rows=2025618 width=16)"
При насильном использовании индекса результат еще более удручающий (set enable_seqscan = false;)
"Merge Join (cost=10000000000.50..10043144012.98 rows=9215951 width=32)"
" Merge Cond: (sa.id = ir.id_article)"
" -> Index Scan using pk_spr_article on spr_article_old sa (cost=0.00..7678088.74 rows=2025618 width=16)"
" -> Materialize (cost=10000000000.50..10035345660.81 rows=9215951 width=24)"
" -> Merge Append (cost=10000000000.50..10035322620.93 rows=9215951 width=24)"
" Sort Key: ir.id_article"
" -> Sort (cost=10000000000.01..10000000000.01 rows=1 width=24)"
" Sort Key: ir.id_article"
" -> Seq Scan on indexed_result ir (cost=10000000000.00..10000000000.00 rows=1 width=24)"
" -> Index Scan using idx_indexed_result_0_id_article on indexed_result_0 ir (cost=0.00..1149596.92 rows=303776 width=24)"
" -> Index Scan using idx_indexed_result_1_id_article on indexed_result_1 ir (cost=0.00..1115769.94 rows=294777 width=24)"
" -> Index Scan using idx_indexed_result_10_id_article on indexed_result_10 ir (cost=0.00..1777097.53 rows=469415 width=24)"
" -> Index Scan using idx_indexed_result_11_id_article on indexed_result_11 ir (cost=0.00..1809874.77 rows=478031 width=24)"
" -> Index Scan using idx_indexed_result_12_id_article on indexed_result_12 ir (cost=0.00..1696437.33 rows=448069 width=24)"
" -> Index Scan using idx_indexed_result_13_id_article on indexed_result_13 ir (cost=0.00..1868338.91 rows=493507 width=24)"
" -> Index Scan using idx_indexed_result_14_id_article on indexed_result_14 ir (cost=0.00..1829272.42 rows=483208 width=24)"
" -> Index Scan using idx_indexed_result_15_id_article on indexed_result_15 ir (cost=0.00..1902105.64 rows=502489 width=24)"
" -> Index Scan using idx_indexed_result_16_id_article on indexed_result_16 ir (cost=0.00..1859207.12 rows=491121 width=24)"
" -> Index Scan using idx_indexed_result_17_id_article on indexed_result_17 ir (cost=0.00..1786610.71 rows=471894 width=24)"
" -> Index Scan using idx_indexed_result_18_id_article on indexed_result_18 ir (cost=0.00..1902019.51 rows=502347 width=24)"
" -> Index Scan using idx_indexed_result_19_id_article on indexed_result_19 ir (cost=0.00..1902587.43 rows=502608 width=24)"
" -> Index Scan using idx_indexed_result_2_id_article on indexed_result_2 ir (cost=0.00..1436584.31 rows=379468 width=24)"
" -> Index Scan using idx_indexed_result_20_id_article on indexed_result_20 ir (cost=0.00..1688032.18 rows=445859 width=24)"
" -> Index Scan using idx_indexed_result_3_id_article on indexed_result_3 ir (cost=0.00..1349648.73 rows=356563 width=24)"
" -> Index Scan using idx_indexed_result_4_id_article on indexed_result_4 ir (cost=0.00..1387109.31 rows=366468 width=24)"
" -> Index Scan using idx_indexed_result_5_id_article on indexed_result_5 ir (cost=0.00..1239362.99 rows=327380 width=24)"
" -> Index Scan using idx_indexed_result_6_id_article on indexed_result_6 ir (cost=0.00..1708995.70 rows=451427 width=24)"
" -> Index Scan using idx_indexed_result_7_id_article on indexed_result_7 ir (cost=0.00..1907377.28 rows=503798 width=24)"
" -> Index Scan using idx_indexed_result_8_id_article on indexed_result_8 ir (cost=0.00..1764693.45 rows=466210 width=24)"
" -> Index Scan using idx_indexed_result_9_id_article on indexed_result_9 ir (cost=0.00..1807879.33 rows=477535 width=24)"
Вопрос - как заставить Postgres использовать индексы и делать это достаточно эффективно? Размеры индексов таковы, что они все целиком влезают в shared_buffer
