ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Запрос с limit выполняется дольше запроса без limit
25 сообщений из 27, страница 1 из 2
  1  все
Запрос с limit выполняется дольше запроса без limit
    #39708914
Pilat01
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Pilat01
Гость
Добрый день!

PostgreSQL 9.6

Есть три запроса к таблице xxx (в ней 22.000.000 строк):

select * from xxx where yyy='1234567' limit 1; -- выполняется мгновенно
select * from xxx where yyy='1234567' order by createdate desc ; -- выполняется мгновенно
select * from xxx where yyy='1234567' order by createdate desc limit 1; -- выполняется за 5 минут

ANALYZE сделал, REINDEX сделал...

select count(yyy) from xxx; -- выполняется за 60 секунд

Во время выполнения SELECT...limit скорость чтения с диска до 20 Мб/сек ;
во время выполнения select count(yyy) from xxx скорость чтения с диска больше 100 Мб/сек ;

Почему запрос с limit, который должен вернуть одну строку, выполняется дольше чем тот же запрос без limit, возвращающий 10.000 строк ???
...
Рейтинг: 0 / 0
27.09.2018, 00:12
| Ответить | Цитировать | Написать  
Запрос с limit выполняется дольше запроса без limit
    #39708921
drsm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
drsm
Участник
Pilat01,
а так?
Код: sql
1.
select * from (select * from xxx where yyy='1234567' ) order by createdate desc limit 1



нужен explain analyze всех запросов.
...
Рейтинг: 0 / 0
27.09.2018, 00:31
| Ответить | Цитировать | Написать  
Запрос с limit выполняется дольше запроса без limit
    #39708922
drsm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
drsm
Участник
[quot drsm]
Код: sql
1.
select * from (select * from xxx where yyy='1234567' ) _ order by createdate desc limit 1


/quot]
...
Рейтинг: 0 / 0
27.09.2018, 00:32
| Ответить | Цитировать | Написать  
Запрос с limit выполняется дольше запроса без limit
    #39708939
Pilat01
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Pilat01
Гость
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
explain analyze select * from xxx where yyy='9735323' order by createdate desc limit 1;
/*
Limit  (cost=0.44..4058.54 rows=1 width=105) (actual time=207932.468..207932.471 rows=1 loops=1)
  ->  Index Scan Backward using xxx_createdate on xxx  (cost=0.44..41847196.46 rows=10312 width=105) (actual time=207932.451..207932.451 rows=1 loops=1)
        Filter: (yyy = '9735323'::bpchar)
        Rows Removed by Filter: 4702344
Planning time: 0.513 ms
Execution time: 207932.559 ms
*/

explain analyze select * from xxx where yyy='9735323'  limit 1;
/*
Limit  (cost=0.44..4.42 rows=1 width=105) (actual time=15.963..15.964 rows=1 loops=1)
  ->  Index Scan using xxx_yyy on xxx  (cost=0.44..41035.42 rows=10312 width=105) (actual time=15.962..15.962 rows=1 loops=1)
        Index Cond: (yyy = '9735323'::bpchar)
Planning time: 0.559 ms
Execution time: 16.030 ms
*/

explain analyze select * from xxx where yyy='9735323' order by createdate desc ;
/*
Sort  (cost=36949.37..36975.15 rows=10312 width=105) (actual time=3066.207..3067.907 rows=6692 loops=1)
  Sort Key: createdate DESC
  Sort Method: quicksort  Memory: 1134kB
  ->  Bitmap Heap Scan on xxx  (cost=196.36..36261.97 rows=10312 width=105) (actual time=20.636..3057.065 rows=6692 loops=1)
        Recheck Cond: (yyy = '9735323'::bpchar)
        Heap Blocks: exact=5388
        ->  Bitmap Index Scan on xxx_yyy  (cost=0.00..193.78 rows=10312 width=0) (actual time=19.402..19.403 rows=6692 loops=1)
              Index Cond: (yyy = '9735323'::bpchar)
Planning time: 0.178 ms
Execution time: 3069.013 ms
*/


explain analyze select * from xxx where yyy='9735323' order by createdate desc limit 1;
/*
Limit  (cost=0.44..4058.54 rows=1 width=105) (actual time=186052.803..186052.805 rows=1 loops=1)
  ->  Index Scan Backward using xxx_createdate on xxx  (cost=0.44..41847196.46 rows=10312 width=105) (actual time=186052.796..186052.797 rows=1 loops=1)
        Filter: (yyy = '9735323'::bpchar)
        Rows Removed by Filter: 4702344
Planning time: 1.547 ms
Execution time: 186052.901 ms
*/
...
Рейтинг: 0 / 0
27.09.2018, 01:18
| Ответить | Цитировать | Написать  
Запрос с limit выполняется дольше запроса без limit
    #39708945
Pilat01
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Pilat01
Гость
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
explain analyze select * from (select * from xxx where yyy='1234567' ) _ order by createdate desc limit 1;
/*
Limit  (cost=0.44..4058.54 rows=1 width=105) (actual time=614503.618..614503.618 rows=0 loops=1)
  ->  Index Scan Backward using xxx_createdate on xxx  (cost=0.44..41847196.46 rows=10312 width=105) (actual time=614503.597..614503.597 rows=0 loops=1)
        Filter: (yyy = '1234567'::bpchar)
        Rows Removed by Filter: 22040609
Planning time: 0.432 ms
Execution time: 614503.787 ms
*/
...
Рейтинг: 0 / 0
27.09.2018, 01:51
| Ответить | Цитировать | Написать  
Запрос с limit выполняется дольше запроса без limit
    #39708946
Pilat01
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Pilat01
Гость
Код: sql
1.
select * from (select * from xxx where yyy='1234567' ) order by createdate desc limit 1



тоже долго.
...
Рейтинг: 0 / 0
27.09.2018, 01:53
| Ответить | Цитировать | Написать  
Запрос с limit выполняется дольше запроса без limit
    #39708965
drsm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
drsm
Участник
Pilat01,
что-то не то со статистикой похоже...
analyze xxx не помогает?

Код: sql
1.
2.
with x as (select * from xxx where yyy='1234567' )
select * from x order by createdate desc limit 1
...
Рейтинг: 0 / 0
27.09.2018, 03:58
| Ответить | Цитировать | Написать  
Запрос с limit выполняется дольше запроса без limit
    #39708966
drsm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
drsm
Участник
Pilat01,

xxx_yyy, xxx_createdate идексы зачем созданы?

чтобы этот запрос выполнялся быстро нужен индекс составной yyy, createdate desc
...
Рейтинг: 0 / 0
27.09.2018, 04:10
| Ответить | Цитировать | Написать  
Запрос с limit выполняется дольше запроса без limit
    #39709055
Pilat01
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Pilat01
Гость
NALYZE xxx; выполнено.

drsmPilat01,

xxx_yyy, xxx_createdate идексы зачем созданы?

чтобы этот запрос выполнялся быстро нужен индекс составной yyy, createdate desc

Но почему тогда запрос
Код: sql
1.
explain analyze select * from xxx where yyy='9735323' order by createdate desc ;


выполняется быстро, а
Код: sql
1.
explain analyze select * from xxx where yyy='9735323' order by createdate desc limit 1;


выполняется медленно? Второй запрос - это первый, от которого осталась одна строка.

Составные индексы на каждый запрос не насоставляешься.
...
Рейтинг: 0 / 0
27.09.2018, 10:03
| Ответить | Цитировать | Написать  
Запрос с limit выполняется дольше запроса без limit
    #39709067
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Pilat01Но почему тогда запрос
Код: sql
1.
explain analyze select * from xxx where yyy='9735323' order by createdate desc ;


выполняется быстро, а
Код: sql
1.
explain analyze select * from xxx where yyy='9735323' order by createdate desc limit 1;


выполняется медленно? Второй запрос - это первый, от которого осталась одна строка.


Потому что план разный.
В одном случае выбирают все строки с yyy='9735323' и сортируют.
А во втором случае выбирают строки отсортированные по createdate и фильтруют по yyy='9735323' с надеждой что первая строка найдется быстро.
А по факту у вас получается приходится перебрать Rows Removed by Filter: 22040609 - 22M строк.
Сколько всего у вас строк в таблице xxx (select count(*) from xxx )?
Скорее всего у вас значения yyy распределены очень не равномерно по createdate - это называется скрытая корреляция данных про которую база ничего не знает.

Вам что хочется
1)максимально быстро выполнить запроса - тогда индекс добавить
или
2)выполнить запрос не медленнее чем без LIMIT - тогда переделайте его на
select * from xxx where yyy='9735323' order by createdate+'0 second'::interval desc limit 1; чтобы исключить неудачный план
(но если вы в yyy= подставите значение которого там полтаблицы то вам будет очень больно по скорости)
или
3)понять почему выбирается такой план а не другой (я вам описал это выше).


--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
27.09.2018, 10:12
| Ответить | Цитировать | Написать  
Запрос с limit выполняется дольше запроса без limit
    #39709124
Pilat01
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Pilat01
Гость
Maxim Boguk,

спасибо. Я построил составной индекс и запрос выполняется быстро.

Это конечно не к Вам претензия, но... почему постгрес так тупит??? Я описание проблемы и решений нашёл, ей минимум 10 лет уже и до сих пор люди задают этот же вопрос. Хинты что ли сделали бы. Так сделали , оказывается. Но отмазки принятия в ядро типа "вам будет потом сложно". В Оракле не сложно, а в постгресе сложно.
Ну построю я индекс (yyy,createdate), а мне понадобится фильтровать по другому полю. Ещё индекс строить по всем полям? :( а другого решения и нет.
...
Рейтинг: 0 / 0
27.09.2018, 11:02
| Ответить | Цитировать | Написать  
Запрос с limit выполняется дольше запроса без limit
    #39709182
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Pilat01,

Тоже раньше думал, что это Postgres тупит. Увы, это вопрос баланса между производительностью и отработкой узких мест.

В 10-й версии можно создать расширенную статистику: https://www.postgresql.org/docs/current/static/sql-createstatistics.html
...
Рейтинг: 0 / 0
27.09.2018, 11:56
| Ответить | Цитировать | Написать  
Запрос с limit выполняется дольше запроса без limit
    #39709196
Pilat01
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Pilat01
Гость
vyegorov,

я в принципе согласен что хинты добавят проблем в будущем (а может и не добавят). Но сейчас придётся добавлять кучу индексов, что будет проблемой всегда. Как минимум скорость вставки и место на диске увеличатся.
...
Рейтинг: 0 / 0
27.09.2018, 12:17
| Ответить | Цитировать | Написать  
Запрос с limit выполняется дольше запроса без limit
    #39709209
p2.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
p2.
Участник
Pilat01Хинты что ли сделали бы.В данном случае достаточно запретить использовать индекс по дате. Это можно сделать и без хинтов.
...
Рейтинг: 0 / 0
27.09.2018, 12:33
| Ответить | Цитировать | Написать  
Запрос с limit выполняется дольше запроса без limit
    #39709214
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Pilat01,

Хинты в ОРАКЛЕ — это псевдокоманды в комментариях.
Хинты в PostgreSQL — это знание особенностей планировщика и исключение ненужных путей исполнения запроса. В вашем случае использование выражения в ORDER BY исключает использование индекса, что приводит к лучшему плану. И как уже упомянул Максим — это может дать и худший план для некоторых ситуаций.
...
Рейтинг: 0 / 0
27.09.2018, 12:41
| Ответить | Цитировать | Написать  
Запрос с limit выполняется дольше запроса без limit
    #39709228
Pilat01
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Pilat01
Гость
p2.Pilat01Хинты что ли сделали бы.В данном случае достаточно запретить использовать индекс по дате. Это можно сделать и без хинтов.

Тогда я буду читать все 20 миллионов строк с диска.
...
Рейтинг: 0 / 0
27.09.2018, 12:54
| Ответить | Цитировать | Написать  
Запрос с limit выполняется дольше запроса без limit
    #39709232
Pilat01
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Pilat01
Гость
vyegorovВ вашем случае использование выражения в ORDER BY исключает использование индекса, что приводит к лучшему плану. И как уже упомянул Максим — это может дать и худший план для некоторых ситуаций.

В некоторых случаях это замедлит выполнение запросов, так что не панацея. Индексы более предсказуемые.
Вот только у меня есть таблицы с 255 столбцами. 255 дополнительных индексов по двум столбцам? Было бы лучше хинты.
...
Рейтинг: 0 / 0
27.09.2018, 12:57
| Ответить | Цитировать | Написать  
Запрос с limit выполняется дольше запроса без limit
    #39709308
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
vyegorovPilat01,

Тоже раньше думал, что это Postgres тупит.
...
и правильно думали
cost=36949.37..36975.15 для выборки всего мн-ва хуже тупой оптимистической (из соображений равномерного распределения) cost=0.44..4058.54 , а если применить к потолочной оценке пусть не знание корреляции а тупое оценивание последствий пессимистического распределения, и усреднить последствия (по мн-ву возможных распределений)-- могут сильно поплыть цифирьки ожиданий. (все в стартовой куче не дадут существенного выигрыша, а все в хвостовой дадут огромный вклад в пессим. веса) нет ?
надо просто нанять комбинаторика -- он правильно посчитает перекос "матожидания коста" относительно "коста от матожидания" на мн-ве перестановок.

а пж тупит всегда -- это константа
...
Рейтинг: 0 / 0
27.09.2018, 14:29
| Ответить | Цитировать | Написать  
Запрос с limit выполняется дольше запроса без limit
    #39709540
drsm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
drsm
Участник
Pilat01vyegorovВ вашем случае использование выражения в ORDER BY исключает использование индекса, что приводит к лучшему плану. И как уже упомянул Максим — это может дать и худший план для некоторых ситуаций.

В некоторых случаях это замедлит выполнение запросов, так что не панацея. Индексы более предсказуемые.
Вот только у меня есть таблицы с 255 столбцами. 255 дополнительных индексов по двум столбцам? Было бы лучше хинты.
зачем?

вот каких хинтов не хватает?
чем использование хинта отличается от order by col + ''
и почти все можно заfenceить при помощи CTE 21687646
...
Рейтинг: 0 / 0
27.09.2018, 21:36
| Ответить | Цитировать | Написать  
Запрос с limit выполняется дольше запроса без limit
    #39709545
Pilat01
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Pilat01
Гость
drsm,

order by col + '' - это хакерство. А хинт точно указывает что от базы хотят.

Хотя для меня выглядит диким то, что постгрес на простую просьбу выдать первую запись начинает делать какие-то предположения о частоте встречаемости конкретного значения, не имея абсолютно никаких возможностей сделать это объективно. Это нечеловеческая логика. А значит неправильная. Посмотрите на эту тему интернет, десять лет вопросы на эту тему возникают. Сколько человекочасов потеряно. Всё что надо было - рассмотреть это как особый случай, если уж невозможно нормальный планировщик написать.
...
Рейтинг: 0 / 0
27.09.2018, 21:51
| Ответить | Цитировать | Написать  
Запрос с limit выполняется дольше запроса без limit
    #39709556
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Pilat01order by col + '' - это хакерство. А хинт точно указывает что от базы хотят.
Эм, поясните за “точно указывает”, пожалуйста.

Pilat01… Посмотрите на эту тему интернет, десять лет вопросы на эту тему возникают. Сколько человекочасов потеряно.
А поискать в интернете на тему проседания производительности из-за неверных хинтов, переставших подходить к данным — можно?
Сколько долларов было потрачено из-за отказа систем.
...
Рейтинг: 0 / 0
27.09.2018, 22:24
| Ответить | Цитировать | Написать  
Запрос с limit выполняется дольше запроса без limit
    #39709564
drsm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
drsm
Участник
Pilat01drsm,

order by col + '' - это хакерство. А хинт точно указывает что от базы хотят.

Хотя для меня выглядит диким то, что постгрес на простую просьбу выдать первую запись...

а это тоже хакерство?

Код: sql
1.
2.
with x as (select * from xxx where yyy='1234567' )
select * from x order by createdate desc limit 1



если не трудно, покажите как по вашему должен выглядеть запрос с хинтом в данном случае.
...
Рейтинг: 0 / 0
27.09.2018, 22:41
| Ответить | Цитировать | Написать  
Запрос с limit выполняется дольше запроса без limit
    #39709571
Pilat01
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Pilat01
Гость
Господа, ну что вы пытаетесь доказать? Что PostgreSQL, в ответ на просьбу выбрать все строки с конкретным столбцом, отсортировать и выдать первую строку, - сортирует всю базу, потом делает последовательный поиск нужного значения и возвращает строку, и это нормально? Почему он не делает именно то что просят?

Вам жена говорит "пойди в магазин купи самый тяжёлый арбуз". Вы идёте в магазин, видите что арбузов много, покупаете весь магазин, сортируете по весу все товары, раскладываете аккуратно, а потом ищете арбуз? Почему от движка ожидается другое? Пусть в базе половина - арбузы. Ну и что, она выберет все арбузы и отсортирует. Это нормальное ожидаемое поведение, хотя и долго. Но это именно то что просит пользователь. Но она-то делает прямо противоположное логике и желанию. Я понимаю что у него такой планировщик, что делает не то что от него просят и ожидают. Но это не достоинство, это недостаток. Зачем это защищать?
...
Рейтинг: 0 / 0
27.09.2018, 23:14
| Ответить | Цитировать | Написать  
Запрос с limit выполняется дольше запроса без limit
    #39709587
drsm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
drsm
Участник
Pilat01Господа, ну что вы пытаетесь доказать? Что PostgreSQL, в ответ на просьбу выбрать все строки с конкретным столбцом, отсортировать и выдать первую строку, - сортирует всю базу, потом делает последовательный поиск нужного значения и возвращает строку, и это нормально? Почему он не делает именно то что просят?

Вам жена говорит "пойди в магазин купи самый тяжёлый арбуз". Вы идёте в магазин, видите что арбузов много, покупаете весь магазин, сортируете по весу все товары, раскладываете аккуратно, а потом ищете арбуз? Почему от движка ожидается другое? Пусть в базе половина - арбузы. Ну и что, она выберет все арбузы и отсортирует. Это нормальное ожидаемое поведение, хотя и долго. Но это именно то что просит пользователь. Но она-то делает прямо противоположное логике и желанию. Я понимаю что у него такой планировщик, что делает не то что от него просят и ожидают. Но это не достоинство, это недостаток. Зачем это защищать?

пусть в магазине арбузы и дыни

- если арбузы лежат отдельно, то выбираем из них самый тяжёлый.
- если все свалено в кучу, то перебираем все и ищем самый тяжёлый арбуз, порядок не важен.
- если все свалено в кучу, но отсортировано по массе, начинаем искать самый тяжёлый арбуз, со стороны где расположены самые тяжелые фрукты.

проблема в том, что в вашем случае практически все дыни тяжелее арбузов.
...
Рейтинг: 0 / 0
28.09.2018, 00:17
| Ответить | Цитировать | Написать  
Запрос с limit выполняется дольше запроса без limit
    #39709593
Pilat01
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Pilat01
Гость
drsm,

проблема в том, что в магазине арбузы, дыни и ещё 100500 товаров. Примерно 20.000.000 экземпляров. А не только арбузы и дыни. Из них 100 арбузов. Это в реальности, и обычно так и в базах данных.
...
Рейтинг: 0 / 0
28.09.2018, 01:04
| Ответить | Цитировать | Написать  
25 сообщений из 27, страница 1 из 2
  1  все
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Запрос с limit выполняется дольше запроса без limit
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=39709055&tid=1995569]:
 0ms
get settings:
 10ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 52ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 59ms
get tp. blocked users:
 2ms
others: 15ms
total:  173ms
созд. / загр.: 173ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]