Есть такие таблицы в базе данных postgresql:
-- в таблице около 5 миллионов записей
create table raitings
(
id bigserial primary key,
state varchar(50) not null,
raiting_cause varchar(100) default ''::character varying not null,
item_id bigint not null,
article_id bigint not null constraint items_pk primary key,
seller_id uuid not null,
feedback_id varchar(36) not null,
deleted boolean default false not null
....
);
-- в таблице около 200 миллионов записей
create table items
(
item_id bigint not null,
article_id bigint not null constraint items_pk primary key,
....
);
create index idx_items_article_id_item_id_covering
on items (article_id) include (item_id, supplier_id);
Есть такой запрос:
Суть в том чтобы обновить нестабильный item_id по стабильному article_id.
WITH raitings_imt AS (
SELECT rs.id, rs.item_id AS old_item_id, ims.item_id AS new_item_id
FROM raitings rs
JOIN items ims USING (article_id)
WHERE (rs.state = 'active' OR rs.raiting_cause = 'price')
),
mismatched AS (
SELECT id, new_item_id
FROM raitings_imt
WHERE old_item_id <> item_id -- Фильтрация уже по подготовленным данным
LIMIT 10000
)
select * from mismatched;
Вот план выполнения:
1) Почему так много Shared Read Blocks(как следствие I/O Read Time": 104076.195), ведь Index Only Scan, из-за условаия (rr.item_id <> ims.item_id но как по другому)?
Или другие запрос вытисняют информацию из индексов из кеша ?
"Node Type": "Index Only Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_items_article_id_item_id_covering",
"Relation Name": "items",
"Schema": "public",
"Alias": "ims",
"Startup Cost": 0.57,
"Total Cost": 2.10,
"Plan Rows": 1,
"Plan Width": 16,
"Actual Startup Time": 0.075,
"Actual Total Time": 0.075,
"Actual Rows": 0,
"Actual Loops": 1575285,
"Output": ["si.article_id", "si.item_id", "si.supplier_id"],
"Index Cond": "(ims.article_id = rr.article_id)",
"Rows Removed by Index Recheck": 0,
"Filter": "(rr.item_id <> ims.item_id)",
"Rows Removed by Filter": 1,
"Heap Fetches": 1023819,
"Shared Hit Blocks": 7512544,
"Shared Read Blocks": 1338914,
"Shared Dirtied Blocks": 9499,
"Shared Written Blocks": 18210,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 104076.195,
"I/O Write Time": 404.416,
2) Как быть в таких случаях?
Чтобы обновить эти item_id в raitings из items, мне приходится прочесывать все таблицу raitings.
Может надо как то батчами доставать из raitings, а потом идти в items?
