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

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

SQL код:
 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" aruser
                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 

SQL код:
Update on "Documents"  (cost=19771027.17..22312135.48 rows=2074177 width=134) (actual time=188992.755..188992.755 rows=0 loops=1)
  
Buffersshared hit=22742 read=2790563 dirtied=121272 written=56091temp 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 Keyunnest('{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 Filter24234
          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)
                
Buffersshared 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)
                      
Buffersshared 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)
          
Buffersshared hit=3422 read=2800776 dirtied=29temp 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")
                
Buffersshared hit=3388 read=2800772 dirtied=3temp 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 Filter6
                
->  Hash  (cost=4888739.29..4888739.29 rows=208459029 width=10) (actual time=143820.738..143820.738 rows=205483044 loops=1)
                      
Buckets1048576  Batches512  Memory Usage25459kB
                      Buffers
shared hit=3388 read=2800772 dirtied=3temp 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)
                            
Buffersshared 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")
        
Buffersshared hit=22689 read=2790553 dirtied=121259 written=56091temp 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 Filter38
              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)
              
Buckets524288  Batches1024  Memory Usage17420kB
              Buffers
shared hit=15029 read=2789120 dirtied=121246 written=56091temp 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)
                    
Buffersshared hit=15029 read=2789120 dirtied=121246 written=56091 
Хочется переписать запрос, чтобы не было seq scan,
а использовался индекс @Id из Documents при удалении и обновлении записей
Часовой пояс GMT +3, время: 19:53.

Форум на базе vBulletin®
Copyright © Jelsoft Enterprises Ltd.
В случае заимствования информации гипертекстовая индексируемая ссылка на Форум обязательна.