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кк с фильтрацией

Новые блоги и статьи Все статьи Все блоги /
Валидация и контроль данных табличной части документа перед записью Maks 22.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа, разработанного в КА2. Задача: контроль и валидация данных табличной части документа перед записью с учетом регламента компании. . .	Отчёт о затраченных материалах за определенный период с макетом печатной формы Maks 21.04.2026 Отчёт из решения ниже размещён в конфигурации КА2. Задача: разработка отчёта по затраченным материалам за определённый период, с возможностью вывода печатной формы отчёта с шапкой и подвалом. В. . .	Отчёт о спецтехнике находящейся в ремонте Maks 20.04.2026 Отчёт из решения ниже размещен в конфигурации КА2. Задача: отобразить спецтехнику, которая на данный момент находится в ремонте. Есть нетиповой документ "Заявка на ремонт спецтехники" который. . .	Памятка для бота и "визитка" для читателей "Semantic Universe Layer (Слой семантической вселенной)" Hrethgir 19.04.2026 Сгенерировано для краткого описания по случаю сборки и компиляции скелета серверного приложения. И пусть после этого скажут, что статьи сгенерированные AI - туфта и не интересно. И это не реклама -. . .
Запрет удаления строк ТЧ документа при определённом условии Maks 19.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа "Аккумуляторы", разработанного в конфигурации КА2. У данного документа есть ТЧ, в которой в зависимости от прав доступа. . .	Модель заражения группы наркоманов alhaos 17.04.2026 Условия задачи сформулированы тут Суть: - Группа наркоманов из 10 человек. - Только один инфицирован ВИЧ. - Колются одной иглой. - Колются раз в день. - Колются последовательно через. . .	Мысли в слух. Про "навсегда". kumehtar 16.04.2026 Подумалось тут, что наверное очень глупо использовать во всяких своих установках понятие "навсегда". Это очень сильное понятие, и я только начинаю понимать край его смысла, не смотря на то что давно. . .	My Business CRM MaGz GoLd 16.04.2026 Всем привет, недавно возникла потребность создать CRM, для личных нужд. Собственно программа предоставляет из себя базу данных клиентов, в которой можно фиксировать звонки, стадии сделки, а также. . .

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

Решение