Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
 Использование индекса для выражений в ORDER BY  [new]
Victor Borg
Member

Откуда:
Сообщений: 26
Есть простая таблица Orders с кучей полей, одно из которых называется status
Задача: отсортировать таблицу чтобы впереди шли елементы с status!=6, и дальше по убыванию id
(status=6 - заказ обработан и должен выводится в конце. к сожалению статусы имеют диапазон от 0 до 20 и простой сортировкой здесь не поможешь)
решается просто:
select id, status from order order by status=6, id desc limit 20 
но к сожалению работает долго и не использует индексы.
на status есть индекс btree, пробовал делать частичный индекс на id для status=6
все равно не подхватывается.
Подскажите возможно ли в данном примере использовать индекс, или простой путь решения проблемы

QUERY PLAN
Limit  (cost=79396.58..79396.63 rows=20 width=8) (actual time=5429.269..5429.329 rows=20 loops=1)
  ->  Sort  (cost=79396.58..82051.07 rows=1061799 width=8) (actual time=5429.264..5429.284 rows=20 loops=1)
        Sort Key: ((status = 6)), id
        Sort Method:  top-N heapsort  Memory: 17kB
        ->  Seq Scan on orders  (cost=0.00..51142.49 rows=1061799 width=8) (actual time=0.044..3596.832 rows=1061799 loops=1)
Total runtime: 5429.407 ms
19 фев 10, 02:42    [8368213]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса для выражений в ORDER BY  [new]
Warstone
Member

Откуда:
Сообщений: 4896
Блог
SELECT * FROM order order by CASE WHEN status = 6 THEN -1 ELSE status END;
Индекс такой-же. То есть вместо status ставите тов тот кейс.
19 фев 10, 02:45    [8368218]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса для выражений в ORDER BY  [new]
Victor Borg
Member

Откуда:
Сообщений: 26
Это практически тоже что и в моем примере (правда еще тяжелее ;) )
explain analyze SELECT * FROM orders order by CASE WHEN status = 6 THEN -1 ELSE status END;
Sort  (cost=1492976.37..1495630.87 rows=1061799 width=705) (actual time=13211.430..16449.729 rows=1061799 loops=1)
  Sort Key: (CASE WHEN (status = 6) THEN (-1) ELSE status END)
  Sort Method:  external merge  Disk: 275504kB
  ->  Seq Scan on orders  (cost=0.00..51142.49 rows=1061799 width=705) (actual time=0.040..3500.022 rows=1061799 loops=1)
Total runtime: 19290.154 ms
19 фев 10, 02:49    [8368223]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса для выражений в ORDER BY  [new]
Kruchinin Pahan
Member

Откуда: Екатеринбург
Сообщений: 925
Victor Borg
Это практически тоже что и в моем примере (правда еще тяжелее ;) )
explain analyze SELECT * FROM orders order by CASE WHEN status = 6 THEN -1 ELSE status END;
Sort  (cost=1492976.37..1495630.87 rows=1061799 width=705) (actual time=13211.430..16449.729 rows=1061799 loops=1)
  Sort Key: (CASE WHEN (status = 6) THEN (-1) ELSE status END)
  Sort Method:  external merge  Disk: 275504kB
  ->  Seq Scan on orders  (cost=0.00..51142.49 rows=1061799 width=705) (actual time=0.040..3500.022 rows=1061799 loops=1)
Total runtime: 19290.154 ms

Вообще не вижу смысла здесь сортировать по индексу. Вы выбираете всю таблицу. Если сортировку пустить по индексу, то алгоритму придется скакать по блокам на диске. А так, последовательно читаем блоки и сортируем - получается на порядок быстрее.

Если поставить LIMIT, то еще как-то можно индекс использовать. Но, если я не ошибаюсь, Postgre не очень хорошо LIMIT оптимизирует.
19 фев 10, 05:50    [8368287]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса для выражений в ORDER BY  [new]
Warstone
Member

Откуда:
Сообщений: 4896
Блог
Kruchinin Pahan
Если поставить LIMIT, то еще как-то можно индекс использовать. Но, если я не ошибаюсь, Postgre не очень хорошо LIMIT оптимизирует.
Нормально, если по индексу.
19 фев 10, 08:46    [8368453]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса для выражений в ORDER BY  [new]
Warstone
Member

Откуда:
Сообщений: 4896
Блог
Кстати... Вы-бы ANALYZE FULL сделали-бы что-ли на таблицу... А то у вас планировщик ошибается сильно.
19 фев 10, 08:48    [8368456]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса для выражений в ORDER BY  [new]
Warstone
Member

Откуда:
Сообщений: 4896
Блог
Пардон VACUUM FULL ANALYZE
19 фев 10, 08:49    [8368457]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса для выражений в ORDER BY  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2887
Victor Borg
select id, status from order order by status=6, id desc limit 20
Подскажите возможно ли в данном примере использовать индекс, или простой путь решения проблемы
создайте точно такой же индекс
create table t1 (
        id integer,
        status integer
);
insert into t1 select generate_series(1,1000), 20*random();
create index i1 on t1 ( (status=6), id desc );
explain analyze select * from t1 order by status=6, id desc limit 10;
drop table t1;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.58 rows=10 width=8) (actual time=0.056..0.117 rows=10 loops=1)
   ->  Index Scan using i1 on t1  (cost=0.00..57.75 rows=1000 width=8) (actual time=0.050..0.073 rows=10 loops=1)
 Total runtime: 0.174 ms
(3 rows)
19 фев 10, 10:02    [8368769]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса для выражений в ORDER BY  [new]
Kruchinin Pahan
Member

Откуда: Екатеринбург
Сообщений: 925
Warstone
Kruchinin Pahan
Если поставить LIMIT, то еще как-то можно индекс использовать. Но, если я не ошибаюсь, Postgre не очень хорошо LIMIT оптимизирует.
Нормально, если по индексу.

Ну что-то сидит такое в голове, что индексы под LIMIT плохо подхватываются. Но не помню что.

А основной смысл был к тому, что использовать индекс для выборки всей таблицы целиком смысла не имеет.
19 фев 10, 14:59    [8371638]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса для выражений в ORDER BY  [new]
Victor Borg
Member

Откуда:
Сообщений: 26
Спасибо огромное!
Вариант с составным индексом очень помог
create index i1 on t1 ( (status=6), id desc );
Спасибо всем кто ответил.
19 фев 10, 15:16    [8371783]     Ответить | Цитировать Сообщить модератору
Все форумы / PostgreSQL Ответить