ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Неиспользование индексов в запросе с limit..offset...
3 сообщений из 3, страница 1 из 1
Неиспользование индексов в запросе с limit..offset...
    #39822975
ZayatsZ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ZayatsZ
Гость
PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

Столкнулся со странным поведение планировщика.

Есть таблица такого рода:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
create table if not exists orders
(
	id bigserial not null constraint orders_pkey primary key,
	partner_id integer,
	order_id varchar,
	date_created date,
	state_code integer,
	state_date timestamp,
	recipient varchar,
	phone varchar,
);

create index if not exists orders_partner_id_index on orders (partner_id);
create index if not exists orders_order_id_index on orders (order_id);
create index if not exists orders_partner_id_date_created_index on orders (partner_id, date_created);



В нёй несколько миллионов строк.

Решается такая задача: партнер в своём личном кабинете должен видеть таблицу своих заказов, с фильтром по датам, постраничным выводом и возможностью сортировки по разным столбцам. Решается это такого рода запросом:

Код: sql
1.
2.
3.
4.
select order_id, date_created, recipient, phone, state_code, state_date
from orders
where partner_id=1 and date_created between '2019-04-01' and '2019-04-30'
order by order_id asc limit 10 offset 10;



И тут начинается такое. Если offset имеет малое значение, то индекс orders_partner_id_date_created_index не используется, и запрос занимает много времени, страница выводится долго. Например, в случае offset=10 получается так:

Код: sql
1.
2.
3.
4.
QUERY PLAN
"Limit  (cost=19495.48..38990.41 rows=10 width=91)"
"  ->  Index Scan using orders_order_id_index on orders  (cost=0.56..41186925.66 rows=21127 width=91)"
"        Filter: ((date_created >= '2019-04-01'::date) AND (date_created <= '2019-04-30'::date) AND (partner_id = 1))"



Однако, когда страницы листаются дальше, с какого-то значения offset вдруг этот индекс начинает работать и всё выводится очень и очень шустро.

Например, вывод четвертой страницы:
Код: sql
1.
2.
3.
4.
select order_id, date_created, recipient, phone, state_code, state_date
from orders
where partner_id=1 and date_created between '2019-04-01' and '2019-04-30'
order by order_id asc limit 10 offset 40;


план его выполнения:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
QUERY PLAN
"Limit  (cost=81449.76..81449.79 rows=10 width=91)"
"  ->  Sort  (cost=81449.66..81502.48 rows=21127 width=91)"
"        Sort Key: order_id"
"        ->  Bitmap Heap Scan on orders  (cost=4241.93..80747.84 rows=21127 width=91)"
"              Recheck Cond: ((partner_id = 1) AND (date_created >= '2019-04-01'::date) AND (date_created <= '2019-04-30'::date))"
"              ->  Bitmap Index Scan on orders_partner_id_date_created_index  (cost=0.00..4236.65 rows=21127 width=0)"
"                    Index Cond: ((partner_id = 1) AND (date_created >= '2019-04-01'::date) AND (date_created <= '2019-04-30'::date))"


Кто-нибудь с таким сталкивался?
...
Рейтинг: 0 / 0
05.06.2019, 17:15
| Ответить | Цитировать | Написать  
Неиспользование индексов в запросе с limit..offset...
    #39822984
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
ZayatsZ,

криво...сть оптимизатора пж -- известный факт. математиксы там не ночевали.

попробуйте так :
Код: sql
1.
2.
3.
4.
select order_id, date_created, recipient, phone, state_code, state_date
from orders
where partner_id=1 and date_created between '2019-04-01' and '2019-04-30'
order by order_id||'' asc limit 10 offset 10;



но пагинация оффсетом -- сама по себе кривое решение. к тому же есть подозрение, что ордер-ид внутри партнера 100%% коррелирует с дейт--криэйтед и с хорошей точностью можно так :
Код: sql
1.
2.
3.
4.
select order_id, date_created, recipient, phone, state_code, state_date
from orders
where partner_id=1 and date_created between '2019-04-01' and '2019-04-30'
order by date_created, order_id asc limit 10 offset 10;


где в пределе можно перейти на пагинацию не оффсетом, а от достигнутого .
...
Рейтинг: 0 / 0
05.06.2019, 17:26
| Ответить | Цитировать | Написать  
Неиспользование индексов в запросе с limit..offset...
    #39822994
ZayatsZ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ZayatsZ
Гость
qwwq,

Первый вариант сработал, теперь индекс всегда используется. Жесть какая, однако.

Вариант с пагинацией "от текущего" рассматривался - но кровь из носа нужна возможность перейти сразу на произвольную страницу.

Спасибо большое!
...
Рейтинг: 0 / 0
05.06.2019, 17:36
| Ответить | Цитировать | Написать  
3 сообщений из 3, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Неиспользование индексов в запросе с limit..offset...
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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