Убрать из плана seq scan

0

1

Использую СУБД PGSQL. Есть таблица Documents в ней поле Account - целое, User - массив строк.

Происходит удаление пользователя, нужно этого пользователя удалить из User, если после удаления массив пустой, то нужно удалить запись. Написал такой запрос, но он стал жутко тормозить.

 WITH user AS (
                SELECT DISTINCT UNNEST(ARRAY['27672','6145']) AS "Id"
          ),
          ar AS(
                SELECT
                "@Id" AS "Id",
                  array_remove("User", user."Id") AS "NewUser",
                CASE
                      WHEN array_length(array_remove("User", user."Id"), 1) > 0 THEN FALSE
                      ELSE TRUE
                END AS "NeedDelete"
                FROM "Documents" ar, user
                WHERE
                      "Account" = 4777912 AND
                      array_position(ar."User", user."Id") IS NOT NULL
          ),
          delete_ar AS(
                DELETE FROM "Documents"
                USING ar
                WHERE "@Id" = ar."Id" AND
                      ar."NeedDelete" IS TRUE
          )
          UPDATE "Documents"
          SET "User" = ar."NewUser"
          FROM ar
          WHERE ar."Id" = "@Id" AND
               ar."NeedDelete" IS FALSE

			   
Update on "Documents"  (cost=19771027.17..22312135.48 rows=2074177 width=134) (actual time=188992.755..188992.755 rows=0 loops=1)
  Buffers: shared hit=22742 read=2790563 dirtied=121272 written=56091, temp read=8246 written=1402207
  CTE user
    ->  HashAggregate  (cost=0.02..0.52 rows=100 width=32) (actual time=0.007..0.010 rows=2 loops=1)
          Group Key: unnest('{27672,6145}'::text[])
          ->  Result  (cost=0.00..0.01 rows=1 width=32) (actual time=0.003..0.004 rows=2 loops=1)
  CTE ar
    ->  Nested Loop  (cost=0.57..237640.03 rows=4148354 width=37) (actual time=59.829..484.828 rows=44 loops=1)
          Join Filter: (array_position(ar_1."User", user."Id") IS NOT NULL)
          Rows Removed by Join Filter: 24234
          Buffers: shared hit=7660 read=1433 dirtied=13
          ->  CTE Scan on user  (cost=0.00..2.00 rows=100 width=32) (actual time=0.008..0.015 rows=2 loops=1)
          ->  Materialize  (cost=0.57..133720.72 rows=41692 width=35) (actual time=0.649..236.659 rows=12139 loops=2)
                Buffers: shared hit=7660 read=1433 dirtied=13
                ->  Index Scan using "iDocuments-AccountDocument" on "Documents" ar_1  (cost=0.57..133512.26 rows=41692 width=35) (actual time=1.296..466.032 rows=12139 loops=1)
                      Index Cond: ("Account" = '4777912'::bigint)
                      Buffers: shared hit=7660 read=1433 dirtied=13
  CTE delete_ar
    ->  Delete on "Documents" "Documents_1"  (cost=8512344.15..10206749.46 rows=2074177 width=34) (actual time=148112.808..148112.808 rows=0 loops=1)
          Buffers: shared hit=3422 read=2800776 dirtied=29, temp read=59703 written=801388
          ->  Hash Join  (cost=8512344.15..10206749.46 rows=2074177 width=34) (actual time=143942.901..148111.006 rows=38 loops=1)
                Hash Cond: (ar_2."Id" = "Documents_1"."@Id")
                Buffers: shared hit=3388 read=2800772 dirtied=3, temp read=59703 written=801388
                ->  CTE Scan on ar ar_2  (cost=0.00..82967.08 rows=2074177 width=32) (actual time=0.030..0.104 rows=38 loops=1)
                      Filter: ("NeedDelete" IS TRUE)
                      Rows Removed by Filter: 6
                ->  Hash  (cost=4888739.29..4888739.29 rows=208459029 width=10) (actual time=143820.738..143820.738 rows=205483044 loops=1)
                      Buckets: 1048576  Batches: 512  Memory Usage: 25459kB
                      Buffers: shared hit=3388 read=2800772 dirtied=3, temp written=800839
                      ->  Seq Scan on "Documents" "Documents_1"  (cost=0.00..4888739.29 rows=208459029 width=10) (actual time=0.040..85509.087 rows=205483044 loops=1)
                            Buffers: shared hit=3388 read=2800772 dirtied=3
  ->  Hash Join  (cost=9326637.15..11867745.46 rows=2074177 width=134) (actual time=187388.784..188988.878 rows=6 loops=1)
        Hash Cond: (ar."Id" = "Documents"."@Id")
        Buffers: shared hit=22689 read=2790553 dirtied=121259 written=56091, temp read=8246 written=1402207
        ->  CTE Scan on ar  (cost=0.00..82967.08 rows=2074177 width=96) (actual time=297.173..484.991 rows=6 loops=1)
              Filter: ("NeedDelete" IS FALSE)
              Rows Removed by Filter: 38
              Buffers: shared hit=7660 read=1433 dirtied=13
        ->  Hash  (cost=4888739.29..4888739.29 rows=208459029 width=42) (actual time=186809.680..186809.680 rows=205483044 loops=1)
              Buckets: 524288  Batches: 1024  Memory Usage: 17420kB
              Buffers: shared hit=15029 read=2789120 dirtied=121246 written=56091, temp written=1401178
              ->  Seq Scan on "Documents"  (cost=0.00..4888739.29 rows=208459029 width=42) (actual time=0.712..100785.196 rows=205483044 loops=1)
                    Buffers: shared hit=15029 read=2789120 dirtied=121246 written=56091

Хочется переписать запрос, чтобы не было seq scan, а использовался индекс @Id из Documents при удалении и обновлении записей

Ссылка

←	Разработка под Linux. Как лучше вести её?

VsCode для web development

→

Без указания версии PostgreSQL и хотя бы списка индексов на таблицу Documents сложно будет ответить на этот вопрос.

~~emorozov~~ ☆
(14.09.22 07:58:31 MSK)

Ссылка

По заголовку можно было бы подумать про https://github.com/ossc-db/pg_hint_plan

Только он не будет работать на CTE. Да и удачное применение этих хинтов - очень, очень редкий случай. ПГ обычно сам хорошо разбирается, что и как ему сканировать. Один раз видел, как с JOIN ему удачно помогли. На сканирование - ни разу не видел.

По тексту задачи и вашему запросу - мне кажется у вас тут совсем не то происходит, что вы описываете. Или я что-то не понимаю, или у вас NeedDelete вообще никогда не наступит для записи с «User» полностью совпадающим с запрошенными пользователями. Вы же каждый раз исключаете из массива следующего пользователя. У вас и получатся две записи - в одной первый исключен, во второй другой исключен. Разве это то, что вы хотите реализовать?

Попробовал написать в вашем стиле, и в ваших терминах что-то такое:

WITH cte_user AS (
	SELECT DISTINCT UNNEST(ARRAY['27672','6145']) AS "Id"
)
,cte_sel AS (
	SELECT
		 d."@Id" 
		,d."Account" 
		,(
			SELECT array_agg("Id") 
			FROM UNNEST(d."User") ou("Id")
			WHERE NOT EXISTS (SELECT FROM  cte_user cu WHERE cu."Id" = ou."Id")
		) AS "NewUser"
	FROM
		"Documents" d
	WHERE 
		d."Account" = 4777912
)
,cte_upd AS (
	UPDATE "Documents" d
	SET "User" = s."NewUser"
	FROM cte_sel s
	WHERE
		d."@Id" = s."@Id"
		AND s."NewUser" IS NOT NULL
)
DELETE FROM "Documents" d
USING cte_sel s
WHERE
	d."@Id" = s."@Id"
	AND s."NewUser" IS NULL

Toxo2 ★★★★
(14.09.22 08:36:34 MSK)
Последнее исправление: Toxo2 14.09.22 08:49:52 MSK (всего исправлений: 1)

Ссылка

Тут нужен индекс по идентификатору документа и индекс по имени пользователя. Я не знаю postgres и как в нём делать индексы по вложенным таблицам. Зачем здесь вложенная таблица я тоже не знаю. Если делать без вложенных таблиц, то всё тривиально. Один delete удаляет документы, у которых пользователь является единственным пользователем, второй deletе удаляет связи удаляемого пользователя с документами. Например, в sqlite:

sqlite> create table documents (id integer not null, name text);
sqlite> create unique index documents$id on documents (id);
sqlite> create table user_documents (user_name text not null, doc_id integer not null);
sqlite> create index user_documents$user_name on user_documents (user_name);
sqlite> .eqp on
sqlite> delete from documents where id in (select doc_id from user_documents where user_name=:1 group by user_name having count(doc_id)=1);
QUERY PLAN
|--SEARCH TABLE documents USING INDEX documents$id (id=?)
`--LIST SUBQUERY
   `--SEARCH TABLE user_documents USING INDEX user_documents$user_name (user_name=?)
sqlite> delete from user_documents where user_name=:1;
QUERY PLAN
`--SEARCH TABLE user_documents USING INDEX user_documents$user_name (user_name=?)
sqlite>

iliyap ★★★★★
(14.09.22 10:12:55 MSK)

Ссылка

Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.

←	Разработка под Linux. Как лучше вести её?

Development

VsCode для web development

→

Похожие темы