Доброе время суток. Имеем PG 9.2 на Gentoo. Скромные 4 ГБ оперативки, из них в shared_buffers = 1900MB. constrain_exclusion = partition.
Есть 2 таблицы, родительские
CREATE TABLE indexed_result
(
id bigint NOT NULL DEFAULT nextval('indexed_result__id_seq'::regclass),
id_article bigint,
id_spr_object_sin bigint,
id_spr_object bigint,
key_in_spr bigint,
CONSTRAINT pk_indexed_result_ PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
); CREATE TABLE indexed_result_ext
(
id bigint NOT NULL DEFAULT nextval('indexed_result_ext__id_seq'::regclass),
pos_start bigint,
pos_stop bigint,
par_num bigint,
phrase_num bigint,
id_indexed_result bigint,
id_indexed_words bigint,
id_article bigint,
CONSTRAINT pk_indexed_result_ext_ PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
Триггера на них висят такие
CREATE OR REPLACE FUNCTION tgf_indexed_result_bef_ins()
RETURNS trigger AS
$BODY$
declare idx bigint;
declare r text;
BEGIN
if NEW.id_article is null then idx = 0;
else idx = NEW.id_article/100000;
end if;
--raise notice 'id=%,id_article=%,id_spr_object_sin=%,id_spr_object=%,key_in_spr=%',NEW.id,NEW.id_article,NEW.id_spr_object_sin,NEW.id_spr_object,NEW.key_in_spr;
r = 'insert into part.indexed_result_'||idx||' (id,id_article,id_spr_object_sin,id_spr_object,key_in_spr) values ('
||NEW.id||','||NEW.id_article||','||NEW.id_spr_object_sin||','||NEW.id_spr_object||','||NEW.key_in_spr||');';
--raise notice '%',r;
execute r;
return null;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE OR REPLACE FUNCTION tgf_indexed_result_ext_bef_ins()
RETURNS trigger AS
$BODY$
declare idx bigint;
declare r text;
BEGIN
if NEW.id_article is null then idx = 0;
else idx = NEW.id_article/100000;
end if;
--raise notice 'id=%,par_num=%,phrase_num=%,word=%,id_article=%',NEW.id,NEW.par_num,NEW.phrase_num,NEW.word,NEW.id_article;
r = 'insert into part.indexed_result_ext_'||idx||' (id_article,pos_start,pos_stop,par_num,phrase_num,id_indexed_result,id_indexed_words,id) values ('
||NEW.id_article||','||NEW.pos_start||','||NEW.pos_stop||','||NEW.par_num||','||NEW.phrase_num||','||NEW.id_indexed_result||','||NEW.id_indexed_words||','||NEW.id||');';
--raise notice '%',r;
execute r;
return null;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Все работает, данные пишутся (быстро или нет - вопрос отдельный). SELECT COUNT(1) FROM ONLY indexed_result и SELECT COUNT(1) FROM ONLY indexed_result_ext возвращают нули.
Выборка существенно подтормаживает. Причем explain показывает, что партиции используются, однако сканирование родителя все равно идет.
SELECT ir.id_article, ir.id_spr_object_sin, ir.id_spr_object,
ir.key_in_spr, ire.pos_start, ire.pos_stop, ire.par_num, ire.phrase_num,
iw.word FROM indexed_result ir, indexed_result_ext ire, indexed_words iw
WHERE ire.id_article = 1926326 and ir.id_article = 1926326 and ire.id_indexed_result = ir.id AND ire.id_indexed_words = iw.id;
Nested Loop (cost=3853.48..206077.91 rows=11 width=90)
Output: ir.id_article, ir.id_spr_object_sin, ir.id_spr_object, ir.key_in_spr, ire.pos_start, ire.pos_stop, ire.par_num, ire.phrase_num, iw.word
-> Hash Join (cost=3853.48..205989.95 rows=11 width=72)
Output: ir.id_article, ir.id_spr_object_sin, ir.id_spr_object, ir.key_in_spr, ire.pos_start, ire.pos_stop, ire.par_num, ire.phrase_num, ire.id_indexed_words
Hash Cond: (ire.id_indexed_result = ir.id)
-> Append (cost=0.00..202130.07 rows=1259 width=48)
-> Seq Scan on public.indexed_result_ext ire (cost=0.00..199794.00 rows=1 width=48) --ЗАЧЕМ ВОТ ЭТО ДЕЛАЕТСЯ???
Output: ire.pos_start, ire.pos_stop, ire.par_num, ire.phrase_num, ire.id_indexed_result, ire.id_indexed_words
Filter: (ire.id_article = 1926326)
-> Bitmap Heap Scan on part.indexed_result_ext_19 ire (cost=26.03..2336.07 rows=1258 width=48)
Output: ire.pos_start, ire.pos_stop, ire.par_num, ire.phrase_num, ire.id_indexed_result, ire.id_indexed_words
Recheck Cond: (ire.id_article = 1926326)
-> Bitmap Index Scan on idx_indexed_result_ext_19_id_article (cost=0.00..25.71 rows=1258 width=0)
Index Cond: (ire.id_article = 1926326)
-> Hash (cost=3823.09..3823.09 rows=2431 width=40)
Output: ir.id_article, ir.id_spr_object_sin, ir.id_spr_object, ir.key_in_spr, ir.id
-> Append (cost=0.00..3823.09 rows=2431 width=40)
-> Seq Scan on public.indexed_result ir (cost=0.00..0.00 rows=1 width=40)
Output: ir.id_article, ir.id_spr_object_sin, ir.id_spr_object, ir.key_in_spr, ir.id
Filter: (ir.id_article = 1926326)
-> Bitmap Heap Scan on part.indexed_result_19 ir (cost=47.14..3823.09 rows=2430 width=40)
Output: ir.id_article, ir.id_spr_object_sin, ir.id_spr_object, ir.key_in_spr, ir.id
Recheck Cond: (ir.id_article = 1926326)
-> Bitmap Index Scan on idx_indexed_result_19_id_article (cost=0.00..46.53 rows=2430 width=0)
Index Cond: (ir.id_article = 1926326)
-> Index Scan using pk_indexed_words on public.indexed_words iw (cost=0.00..7.99 rows=1 width=34)
Output: iw.id, iw.word
Index Cond: (iw.id = ire.id_indexed_words)
Вопрос - зачем выполняется "Seq Scan on public.indexed_result_ext ire (cost=0.00..199794.00 rows=1 width=48)", если партицированные таблицы существуют ив ыборки по ним успешно прошли "Bitmap Heap Scan on part.indexed_result_ext_19 ire (cost=26.03..2336.07 rows=1258 width=48)" за время, в 100 раз меньшее?