За последние 24 часа нас посетили 17340 программистов и 984 робота. Сейчас ищет 431 программист ...

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

Тема в разделе "PostgreSQL", создана пользователем polin11, 14 сен 2022.

  1. polin11

    polin11 Активный пользователь

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

    Происходит удаление пользователя, нужно этого пользователя удалить из User,
    если после удаления массив пустой, то нужно удалить запись.
    Написал такой запрос, но он стал жутко тормозить.
    PHP:
    1. WITH user AS (
    2.                 SELECT DISTINCT UNNEST(ARRAY['27672','6145']) AS "Id"
    3.           ),
    4.           ar AS(
    5.                 SELECT
    6.                 "@Id" AS "Id",
    7.                   array_remove("User", user."Id") AS "NewUser",
    8.                 CASE
    9.                       WHEN array_length(array_remove("User", user."Id"), 1) > 0 THEN FALSE
    10.                       ELSE TRUE
    11.                 END AS "NeedDelete"
    12.                 FROM "Documents" ar, user
    13.                 WHERE
    14.                       "Account" = 4777912 AND
    15.                       array_position(ar."User", user."Id") IS NOT NULL
    16.           ),
    17.           delete_ar AS(
    18.                 DELETE FROM "Documents"
    19.                 USING ar
    20.                 WHERE "@Id" = ar."Id" AND
    21.                       ar."NeedDelete" IS TRUE
    22.           )
    23.           UPDATE "Documents"
    24.           SET "User" = ar."NewUser"
    25.           FROM ar
    26.           WHERE ar."Id" = "@Id" AND
    27.                ar."NeedDelete" IS FALSE
    PHP:
    1. Update on "Documents"  (cost=19771027.17..22312135.48 rows=2074177 width=134) (actual time=188992.755..188992.755 rows=0 loops=1)
    2.   Buffers: shared hit=22742 read=2790563 dirtied=121272 written=56091, temp read=8246 written=1402207
    3.   CTE user
    4.     ->  HashAggregate  (cost=0.02..0.52 rows=100 width=32) (actual time=0.007..0.010 rows=2 loops=1)
    5.           Group Key: unnest('{27672,6145}'::text[])
    6.           ->  Result  (cost=0.00..0.01 rows=1 width=32) (actual time=0.003..0.004 rows=2 loops=1)
    7.   CTE ar
    8.     ->  Nested Loop  (cost=0.57..237640.03 rows=4148354 width=37) (actual time=59.829..484.828 rows=44 loops=1)
    9.           Join Filter: (array_position(ar_1."User", user."Id") IS NOT NULL)
    10.           Rows Removed by Join Filter: 24234
    11.           Buffers: shared hit=7660 read=1433 dirtied=13
    12.           ->  CTE Scan on user  (cost=0.00..2.00 rows=100 width=32) (actual time=0.008..0.015 rows=2 loops=1)
    13.           ->  Materialize  (cost=0.57..133720.72 rows=41692 width=35) (actual time=0.649..236.659 rows=12139 loops=2)
    14.                 Buffers: shared hit=7660 read=1433 dirtied=13
    15.                 ->  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)
    16.                       Index Cond: ("Account" = '4777912'::bigint)
    17.                       Buffers: shared hit=7660 read=1433 dirtied=13
    18.   CTE delete_ar
    19.     ->  Delete on "Documents" "Documents_1"  (cost=8512344.15..10206749.46 rows=2074177 width=34) (actual time=148112.808..148112.808 rows=0 loops=1)
    20.           Buffers: shared hit=3422 read=2800776 dirtied=29, temp read=59703 written=801388
    21.           ->  Hash Join  (cost=8512344.15..10206749.46 rows=2074177 width=34) (actual time=143942.901..148111.006 rows=38 loops=1)
    22.                 Hash Cond: (ar_2."Id" = "Documents_1"."@Id")
    23.                 Buffers: shared hit=3388 read=2800772 dirtied=3, temp read=59703 written=801388
    24.                 ->  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)
    25.                       Filter: ("NeedDelete" IS TRUE)
    26.                       Rows Removed by Filter: 6
    27.                 ->  Hash  (cost=4888739.29..4888739.29 rows=208459029 width=10) (actual time=143820.738..143820.738 rows=205483044 loops=1)
    28.                       Buckets: 1048576  Batches: 512  Memory Usage: 25459kB
    29.                       Buffers: shared hit=3388 read=2800772 dirtied=3, temp written=800839
    30.                       ->  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)
    31.                             Buffers: shared hit=3388 read=2800772 dirtied=3
    32.   ->  Hash Join  (cost=9326637.15..11867745.46 rows=2074177 width=134) (actual time=187388.784..188988.878 rows=6 loops=1)
    33.         Hash Cond: (ar."Id" = "Documents"."@Id")
    34.         Buffers: shared hit=22689 read=2790553 dirtied=121259 written=56091, temp read=8246 written=1402207
    35.         ->  CTE Scan on ar  (cost=0.00..82967.08 rows=2074177 width=96) (actual time=297.173..484.991 rows=6 loops=1)
    36.               Filter: ("NeedDelete" IS FALSE)
    37.               Rows Removed by Filter: 38
    38.               Buffers: shared hit=7660 read=1433 dirtied=13
    39.         ->  Hash  (cost=4888739.29..4888739.29 rows=208459029 width=42) (actual time=186809.680..186809.680 rows=205483044 loops=1)
    40.               Buckets: 524288  Batches: 1024  Memory Usage: 17420kB
    41.               Buffers: shared hit=15029 read=2789120 dirtied=121246 written=56091, temp written=1401178
    42.               ->  Seq Scan on "Documents"  (cost=0.00..4888739.29 rows=208459029 width=42) (actual time=0.712..100785.196 rows=205483044 loops=1)
    43.                     Buffers: shared hit=15029 read=2789120 dirtied=121246 written=56091        
    Хочется переписать запрос, чтобы не было seq scan,
    а использовался индекс @ID из Documents при удалении и обновлении записей