Postgres Materialize вызывает низкую производительность в запросе на удаление

У меня есть запрос DELETE, который мне нужно запустить в PostgreSQL 9.0.4. Я обнаружил, что он эффективен до тех пор, пока не достигнет 524 289 строк в запросе подзапроса.

Например, при 524 288 материализованное представление не используется, и стоимость выглядит довольно неплохо:

explain DELETE FROM table1 WHERE pointLevel = 0 AND userID NOT IN
(SELECT userID FROM table2 fetch first 524288 rows only);
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Delete  (cost=13549.49..17840.67 rows=21 width=6)
   ->  Index Scan using jslps_userid_nopt on table1  (cost=13549.49..17840.67 rows=21 width=6)
         Filter: ((NOT (hashed SubPlan 1)) AND (pointlevel = 0))
         SubPlan 1
           ->  Limit  (cost=0.00..12238.77 rows=524288 width=8)
                 ->  Seq Scan on table2  (cost=0.00..17677.92 rows=757292 width=8)
(6 rows)

Однако как только я набираю 524 289, в игру вступает материализованное представление, и запрос DELETE становится намного дороже:

explain DELETE FROM table1 WHERE pointLevel = 0 AND userID NOT IN
(SELECT userID FROM table2 fetch first 524289 rows only);

  QUERY PLAN

-----------------------------------------------------------------------------------------------------------  
Delete  (cost=0.00..386910.33 rows=21 width=6)
    ->  Index Scan using jslps_userid_nopt on table1  (cost=0.00..386910.33 rows=21 width=6)
         Filter: ((pointlevel = 0) AND (NOT (SubPlan 1)))
         SubPlan 1
           ->  Materialize  (cost=0.00..16909.24 rows=524289 width=8)
                 ->  Limit  (cost=0.00..12238.79 rows=524289 width=8)
                       ->  Seq Scan on table2  (cost=0.00..17677.92 rows=757292 width=8) (7 rows)

Я обошел проблему, используя вместо этого JOIN в запросе подвыбора:

SELECT s.userid 
FROM table1 s 
LEFT JOIN table2 p ON s.userid=p.userid
WHERE p.userid IS NULL AND s.pointlevel=0

Однако мне все еще интересно понять, почему материализация так резко снижает производительность.


postgresql database sql-delete query-performance materialized-views
person cizer    schedule 21.10.2014    source источник

Ответы (1)


arrow_upward
9
arrow_downward

Я предполагаю, что в rows=524289 буфер памяти заполнен, поэтому подзапрос должен быть материализован на диске. Отсюда резкое увеличение необходимого времени.

Здесь вы можете узнать больше о настройке буферов памяти: http://www.postgresql.org/docs/9.1/static/runtime-config-resource.html
Если вы поиграете с work_mem, вы увидите разницу в поведении запроса.

Однако использование соединения в подзапросе - гораздо лучший способ ускорить запрос, поскольку вы ограничиваете количество строк в самом источнике, а не просто выбираете первые строки XYZ и затем выполняете проверки.

person Antoan Milkov    schedule 21.10.2014