PDO, Postgres. Долго выполняется SELECT, если есть bindValue

@Reposlav · Регистрация: 31.05.2011

Студворк — интернет-сервис помощи студентам

Всем привет!

Очень-очень странный баг.

Есть табличка postgres с 3.5кк записей. Есть два почти идентичных скрипта:

PHP
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?php
$time_start = microtime(true);
$pdo = new PDO('pgsql:host=localhost;dbname=dbname', 'username', 'password');
$statement = $pdo->prepare('SELECT id FROM "contest_results" WHERE "contest_id"=34 ORDER BY "likes" DESC LIMIT 10');
$time_before_execute = microtime(true);
$statement->execute();
$time_after_execute = microtime(true);
$result = $statement->fetchAll();
$time_after_fetch = microtime(true);
echo 'Prepare: ' . ($time_before_execute-$time_start) . '<br>';
echo 'Execute: ' . ($time_after_execute-$time_before_execute) . '<br>';
echo 'Fetch: ' . ($time_after_fetch-$time_after_execute) . '<br>';
foreach($result as $row) {
    echo $row['id'] . '<br>';
}

и

PHP
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?php
$time_start = microtime(true);
$pdo = new PDO('pgsql:host=localhost;dbname=dbname', 'username', 'password');
$statement = $pdo->prepare('SELECT id FROM "contest_results" WHERE "contest_id"=:qp0 ORDER BY "likes" DESC LIMIT 10');
$statement->bindValue(':qp0', 34);
$time_before_execute = microtime(true);
$statement->execute();
$time_after_execute = microtime(true);
$result = $statement->fetchAll();
$time_after_fetch = microtime(true);
echo 'Prepare: ' . ($time_before_execute-$time_start) . '<br>';
echo 'Execute: ' . ($time_after_execute-$time_before_execute) . '<br>';
echo 'Fetch: ' . ($time_after_fetch-$time_after_execute) . '<br>';
foreach($result as $row) {
    echo $row['id'] . '<br>';
}

Отличия только в том, что в первом скрипте параметр в запросе инлайновый (строка 4), а во втором биндится с помощью bindValue() (строки 4-5). В остальном они идентичны.
Вот что выдают скрипты:

HTML5
1
2
3
4
5
6
7
8
9
Prepare: 0.054606914520264
Execute: 0.0077860355377197
Fetch: 3.9100646972656E-5
3541769
3541766
3541765
3541764
3541767
3541768

HTML5
1
2
3
4
5
6
7
8
9
Prepare: 0.053914070129395
Execute: 38.654514789581
Fetch: 4.1007995605469E-5
3541769
3541766
3541765
3541764
3541767
3541768

Вариант с bindValue() выполняется больше полуминуты, а инлайновый почти мгновенно.
WTF? Почему так получается? Как мне ускорить второй вариант? Это я туплю, или в PDO какой-то баг?

PHP 5.5.20
Pgsql 9.1.14

NEbO · 19.02.2015, 03:03

Видимо, вы не первый: http://www.postgresql.org/mess... .gmail.com
1. Попробуйте сделать explain.
2. Если explain ничего интересного не выдаст, попробуйте эти же запросы на таблице с 10ю записями.
3. Попробуйте передавать массив параметров вместо bindValue, прямо в метод execute.
4. Вероятно, посмотреть в документацию PDO на предмет настроек, связанных с подготовленными запросами, и, возможно, конкретно psql-зависимых (на php.net заметил только EMULATE_PREPARES-настройку, наврядли это то, что вам нужно, даже если оно и исправит ситуацию).
Скорее всего, как я понял, при bindValue почему-то не используются индексы (это можно выяснить с помощью пункта 1), также я заметил, что в пхп-шных доках для select-запросов обычно параметры передаются с помощью массива в execute, а не через bindValue. Последний же используется больше для insert-запросов.
Поэтому начните с пунктов 1 и 3, возможно, что-то сработает

@Reposlav · 19.02.2015, 11:03 **[ТС]**

NEbO, EXPLAIN пробовал делать и результаты были одинаковыми.

Попробовал выполнить такой запрос:

Oracle 11 SQL
1
2
3
PREPARE usrrptplan (int) AS
    SELECT id FROM "contest_results" WHERE "contest_id"=$1 ORDER BY "likes" DESC LIMIT 10;
EXECUTE usrrptplan(34)

выполняется так же долго. EXPLAIN уже этого запроса показал, что pg действительно в этом случае для сортировки не использует индексы (для выборки индекс используется). Значит проблема не в PDO, а в самой СУБД.
Пойду ковырять конфиги постгреса.

Добавлено через 42 минуты
Интересно! Мне помогло добавление нового индекса на (contest_id, likes). Раньше было только два, отдельно на contest_id и отдельно на likes.

Кстати, похоже проблема кроется в этом: http://intaro.ru/magazine/2010... statement/
Если кратко - при подготовленном запросе СУБД составляет план запроса только один раз, и применяет этот план даже в том случае, если этот план не эффективен для конкретного запроса.

Спасибо, NEbO! Направили меня в нужную сторону.

@root · 19.02.2015, 18:05

Reposlav, поиск без индекса идет простым перебором и 34 номер найдется очень быстро, намного быстрее чем 65448392й номер. Но если этот 65448392й номер находится в проиндексированном поле, то он на несколько порядков найдется быстрее чем без индексов и скорость поиска будет примерно равна скорости поиска 34 номера. Возможно разработчикам PDO есть над чем задуматься, но тест в вашем первом посте абсолютно некорректен поскольку используются совершенно разные способы формирования и обработки запроса.

@Reposlav · 19.02.2015, 18:18 **[ТС]**

root, собсна, в том-то и дело, что индекс был и есть. Прикол был в том, что в подготовленном запросе Pgsql не использовал индекс для сортировки. А PDO здесь ни при чем, он юзает функционал, предоставляемый СУБД.

поскольку используются совершенно разные способы формирования и обработки запроса.

Ну, когда я делаю простую выборку с фильтрацией и сортировкой по индексированным полям, меня не очень волнует, какие там способы у СУБД внутри, но я полагаю, что если индекс есть, то СУБД должна его использовать. Здесь нужно просто принять как данное и запомнить, что подготовленные запросы могут выполняться неоптимальным способом.

И, что самое интересное: Pgsql посчитал, что сначала надо отсортировать, а потом отфильтровать. То есть сначала он отсортировал все 3.5кк записей, а потом отфильтровал из них 7, а не наоборот.

поиск без индекса идет простым перебором и 34 номер найдется очень быстро, намного быстрее чем 65448392й номер

Не факт кстати, хотя в простейшем случае да.

@root · 19.02.2015, 18:21

Сообщение от Reposlav

То есть сначала он отсортировал все 3.5кк записей, а потом отфильтровал из них 7, а не наоборот.

а по другому он и не мог сделать не обработав все записи. не?

@Reposlav · 19.02.2015, 18:32 **[ТС]**

root, мог) Так он сначала провел более сложный, чем фильтрация, алгоритм сортировки над 3.5кк записей, а потом снова пробежался по всем 3.5кк записей, чтобы отфильтровать из них 7.
Он мог сначала отфильтровать все подходящие записи, а потом уже сортировать гораздо меньшее количество записей. Даже в самом пессимистичном случае второй вариант будет не медленнее первого, но в остальных случаях второй подход будет экономить ресурсы вплоть до нескольких порядков, как в данном случае.

@root · 19.02.2015, 18:51

Сообщение от Reposlav

Так он сначала провел более сложный, чем фильтрация, алгоритм сортировки над 3.5кк записей, а потом снова пробежался по всем 3.5кк записей, чтобы отфильтровать из них 7.
Он мог сначала отфильтровать все подходящие записи

Чисто логически, как можно выбрать 7 подходящих, не просмотрев все? В индексируемом поле индексы уже были отсортированы при последнем добавлении в таблицу, а при выборке идет лишь бинарный поиск. Скорее всего все связано с запутанностью или отсутствием индексов, поэтому алгоритмы работы базы неочевидны. Вы привели в примере простую выборку с сортировкой и limit(limit медленный по определению), а какова база целиком, какие связи между таблицами ведь никому неизвестно..

@Reposlav · 19.02.2015, 19:10 **[ТС]**

Сообщение от root

Чисто логически, как можно выбрать 7 подходящих, не просмотрев все?

Никак (это если без индексов). Но выборка займет линейное время, сортировка же линейное время займет только в лучшем случае, а в худшем - зависит от алгоритма сортировки (вплоть до квадратичного). Именно поэтому лучше сначала выполнить более быстрый алгоритм, т.е. фильтрацию, а потом уже, на меньшей выборке, проводить более медленный. Особенно если учесть, что для фильтрации СУБД индекс применяла, а значит не просматривала всю таблицу.

Сообщение от root

Скорее всего все связано с запутанностью или отсутствием индексов, поэтому алгоритмы работы базы неочевидны. Вы привели в примере простую выборку с сортировкой и limit(limit медленный по определению), а какова база целиком, какие связи между таблицами ведь никому неизвестно..

Скорость работы неподготовленного запроса и EXPLAIN дают понять, что база спроектирована правильно, просто подготовленный запрос не всегда выполняется наиболее эффективным способом (который доступен СУБД), в отличие от неподготовленного.

@root · 19.02.2015, 19:29

Сообщение от Reposlav

более быстрый алгоритм, т.е. фильтрацию,

вся быстрота фильтрации заключается в том что она выкидывает ненужное из уже полученного набора записей. В этом смысл фильтрации.. Она выкидывает из 3.5к ненужные, а вот самое интересное, сколько времени займет получить эти 3.5к, а потом выкинуть 3493 записи?

@Reposlav · 19.02.2015, 19:59 **[ТС]**

Ну, для начала, алгоритм не выкидывает лишнее, а берет необходимое.

Сообщение от root

Она выкидывает из 3.5к ненужные, а вот самое интересное, сколько времени займет получить эти 3.5к, а потом выкинуть 3493 записи?

А это не важно само по себе. Мы же читаем не абсолютное время, а относительное.
Короче, лучше сначала пройтись по 3.5кк с фильтрацией, а потом по 7 с сортировкой, чем сначала по 3.5кк с сортировкой, а потом по 3.5кк с фильтрацией

Новые блоги и статьи Все статьи Все блоги /
Использование SDL3-callbacks вместо функции main() на Android, Desktop и WebAssembly 8Observer8 24.01.2026 Если вы откроете примеры для начинающих на официальном репозитории SDL3 в папке: examples, то вы увидите, что все примеры используют следующие четыре обязательные функции, а привычная функция main(). . .	моя боль iceja 24.01.2026 Выложила интерполяцию кубическими сплайнами www. iceja. net REST сервисы временно не работают, только через Web. Написала за 56 рабочих часов этот сайт с нуля. При помощи perplexity. ai PRO , при. . .	Модель сукцессии микоризы anaschu 24.01.2026 Решили писать научную статью с неким РОманом	http://iceja.net/ математические сервисы iceja 20.01.2026 Обновила свой сайт http:/ / iceja. net/ , приделала Fast Fourier Transform экстраполяцию сигналов. Однако предсказывает далеко не каждый сигнал (см ограничения http:/ / iceja. net/ fourier/ docs ). Также. . .
http://iceja.net/ сервер решения полиномов iceja 18.01.2026 Выкатила http:/ / iceja. net/ сервер решения полиномов (находит действительные корни полиномов методом Штурма). На сайте документация по API, но скажу прямо VPS слабенький и 200 000 полиномов. . .	Расчёт переходных процессов в цепи постоянного тока igorrr37 16.01.2026 / * Дана цепь(не выше 3-го порядка) постоянного тока с элементами R, L, C, k(ключ), U, E, J. Программа находит переходные токи и напряжения на элементах схемы классическим методом(1 и 2 з-ны. . .	Восстановить юзерскрипты Greasemonkey из бэкапа браузера damix 15.01.2026 Если восстановить из бэкапа профиль Firefox после переустановки винды, то список юзерскриптов в Greasemonkey будет пустым. Но восстановить их можно так. Для этого понадобится консольная утилита. . .	Сукцессия микоризы: основная теория в виде двух уравнений. anaschu 11.01.2026 https:/ / rutube. ru/ video/ 7a537f578d808e67a3c6fd818a44a5c4/

NEbO 601 / 468 / 73 Регистрация: 22.01.2009 Сообщений: 1,180 Записей в блоге: 1
	19.02.2015, 03:03
	Сообщение было отмечено Reposlav как решение Решение Видимо, вы не первый: http://www.postgresql.org/mess... .gmail.com 1. Попробуйте сделать explain. 2. Если explain ничего интересного не выдаст, попробуйте эти же запросы на таблице с 10ю записями. 3. Попробуйте передавать массив параметров вместо bindValue, прямо в метод execute. 4. Вероятно, посмотреть в документацию PDO на предмет настроек, связанных с подготовленными запросами, и, возможно, конкретно psql-зависимых (на php.net заметил только EMULATE_PREPARES-настройку, наврядли это то, что вам нужно, даже если оно и исправит ситуацию). Скорее всего, как я понял, при bindValue почему-то не используются индексы (это можно выяснить с помощью пункта 1), также я заметил, что в пхп-шных доках для select-запросов обычно параметры передаются с помощью массива в execute, а не через bindValue. Последний же используется больше для insert-запросов. Поэтому начните с пунктов 1 и 3, возможно, что-то сработает 1

@root Заблокирован
	19.02.2015, 18:05
	Reposlav, поиск без индекса идет простым перебором и 34 номер найдется очень быстро, намного быстрее чем 65448392й номер. Но если этот 65448392й номер находится в проиндексированном поле, то он на несколько порядков найдется быстрее чем без индексов и скорость поиска будет примерно равна скорости поиска 34 номера. Возможно разработчикам PDO есть над чем задуматься, но тест в вашем первом посте абсолютно некорректен поскольку используются совершенно разные способы формирования и обработки запроса. 0

@Reposlav 48 / 48 / 26 Регистрация: 31.05.2011 Сообщений: 139
	19.02.2015, 18:32 [ТС]
	root, мог) Так он сначала провел более сложный, чем фильтрация, алгоритм сортировки над 3.5кк записей, а потом снова пробежался по всем 3.5кк записей, чтобы отфильтровать из них 7. Он мог сначала отфильтровать все подходящие записи, а потом уже сортировать гораздо меньшее количество записей. Даже в самом пессимистичном случае второй вариант будет не медленнее первого, но в остальных случаях второй подход будет экономить ресурсы вплоть до нескольких порядков, как в данном случае. 0

PDO, Postgres. Долго выполняется SELECT, если есть bindValue

Решение