Prepared statement в PostgreSQL: преимущества и недостатки

В данной статье рассматривается производительность prepared statement по сравнению с запросами, в который параметры подставлены прямо в sql. Существует мнение, что prepared statement всегда оказывается лучше в плане производительности, и всегда нужно стараться использовать именно его. Попробуем разобраться в том, чем prepared statement лучше и всегда ли.

Рассмотрим пример. Создадим таблицу с id, вещественными числом val1 и целым чилом val2.

CREATE TABLE test
(
  id serial PRIMARY KEY,
  val1 double precision,
  val2 integer
);

Добавим в таблицу миллион записей, при этом val1 будет равномерно распределено от 0 до 100, а val2 будет с вероятностью 0.1% принимать значение 1, а с вероятностью 99.9% принимать значение 0.

INSERT INTO test (val1, val2) SELECT random()*100, CASE WHEN random() < 0.001 THEN 1 ELSE 0 END FROM generate_series(1,1000000);

Создадим индексы по стоблцу val1 и столбцу val2.

CREATE INDEX test_val1_idx ON test(val1);

CREATE INDEX test_val2_idx ON test(val2);

При этом не забываем собрать статистику.

ANALYZE test;

Посмотрим, как будет выполняться запрос, который выбирает строки, для которых val2 = 0, отсортированные по val1.

EXPLAIN ANALYZE SELECT * FROM test WHERE val2 = 0 ORDER BY val1;

                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using test_val1_idx on test  (cost=0.00..53960.30 rows=999033 width=16) (actual time=0.075..7702.011 rows=999012 loops=1)
   Filter: (val2 = 0)
 Total runtime: 9671.413 ms
(3 rows)

В этом запросе планировщик решил, что нужно идти по индексу test_val1_idx, отбирая при этом только записи с val2 = 0. Посмотрим теперь, как будет выполняться аналогичный запрос, но выбирающий строки с val2 = 1.

EXPLAIN ANALYZE SELECT * FROM test WHERE val2 = 1 ORDER BY val1;

                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=103.86..106.28 rows=967 width=16) (actual time=10.311..12.249 rows=988 loops=1)
   Sort Key: val1
   Sort Method:  quicksort  Memory: 86kB
   ->  Index Scan using test_val2_idx on test  (cost=0.00..55.91 rows=967 width=16) (actual time=0.092..7.172 rows=988 loops=1)
         Index Cond: (val2 = 1)
 Total runtime: 14.149 ms
(6 rows)

Теперь планировщик решил выполнить выбрать все значения с val2 = 1 по индексу test_val2_idx, а затем отсортировать их по val1. Как видно, для каждого из запросов планировщик выбрал оптимальный план, и эти планы – разные. Теперь попробуем использовать prepared statement.

PREPARE test_plan (int) AS SELECT * FROM test WHERE val2 = $1 ORDER BY val1;

Выполним этот план с параметром 0.

EXPLAIN ANALYZE EXECUTE test_plan(0);

                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using test_val1_idx on test  (cost=0.00..53960.30 rows=500000 width=16) (actual time=0.081..7719.301 rows=999012 loops=1)
   Filter: (val2 = $1)
 Total runtime: 9689.408 ms
(3 rows)

Здесь получилось то же, что и при подстановке параметров прямо в sql. Теперь посмотрим, что получается при параметре, равном 1.

EXPLAIN ANALYZE EXECUTE test_plan(1);

                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using test_val1_idx on test  (cost=0.00..53960.30 rows=500000 width=16) (actual time=0.087..7706.097 rows=999012 loops=1)
   Filter: (val2 = $1)
 Total runtime: 9673.314 ms
(3 rows)

Запрос выполнялся примерно столько же, как и запрос с параметром 0. Это примерно в 700 раз медленнее, чем запрос, в который был напрямую подставлен параметр!

Дело в том, что когда в запрос напрямую подставлен параметр, то планировщик составляет оптимальный план, учитывая конкретное значение параметра и собранную статистику. Однако, когда используется prepared statement, планировщик составляет один план, который затем может выполняться несколько раз для разных значений параметров. При этом этот план может быть для некоторых значений параметров неоптимальным. Выигрыш, который даёт prepared statement, в том, что не нужно отдельно составлять план для каждого запроса, а это позволяет сэкономить время. Если нужно выполнить несколько тысяч одинаковых запросов, эта экономия может оказаться существенной. Но в тех случаях, когда нужно выполнить какой-то сложный поиск, где важно чтобы план был оптимальным, prepared statement лучше не использовать.

P.S. Написанное выше справедливо относительно версий PostgreSQL до 9.0 включительно. В версии 9.1 планируется, что планировщик сможет изменять планы prepared statement'ов в зависимости от конкретных значений параметров.

Комментарии