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

1. polin11 14.09.22 04:56 Сейчас в теме
Убрать из плана seq scan

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

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

 WITH user AS (
                SEL ECT DISTINCT UNNEST(ARRAY['27672','6145']) AS "Id"
          ),
          ar AS(
                SEL ECT
                "@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"
                FR OM "Documents" ar, user
                WHERE
                      "Account" = 4777912 AND
                      array_position(ar."User", user."Id") IS NOT NULL
          ),
          delete_ar AS(
                DELETE FR OM "Documents"
                USING ar
                WHERE "@Id" = ar."Id" AND
                      ar."NeedDelete" IS TRUE
          )
          UPD ATE "Documents"
          SE T "User" = ar."NewUser"
          FR OM ar
          WH ERE 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 при удалении и обновлении записей
По теме из базы знаний
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
Оставьте свое сообщение

Для получения уведомлений об ответах подключите телеграм бот:
Инфостарт бот