У меня есть таблица с данными временных рядов и с первичным ключом - на самом деле несколько больших таблиц, но это подойдет в качестве примера:
postgres=# create table foo (x varchar, y varchar, z text, updated timestamptz, primary key(x, y, updated));
CREATE TABLE
postgres=# \d foo
Table "public.foo"
Column | Type | Collation | Nullable | Default
---------+--------------------------+-----------+----------+---------
x | character varying | | not null |
y | character varying | | not null |
z | text | | |
updated | timestamp with time zone | | not null |
Indexes:
Это автоматически создаст обновленный индекс для x. Однако многие из моих запросов ищут «самый последний foo для различных значений x», а не самый старый, поэтому индекс является неправильным для эффективных запросов.
Например, если я это сделаю:
explain analyze select distinct on (x,y,updated) * from foo order by x,y,updated;
Затем он прекрасно использует индекс:
----------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=0.43..145739.21 rows=158334 width=38) (actual time=0.031..889.771 rows=1583344 loops=1)
-> Index Scan using foo_pkey on foo (cost=0.43..133864.13 rows=1583344 width=38) (actual time=0.030..534.995 rows=1583344 loops=1)
Planning Time: 0.074 ms
Execution Time: 958.419 ms
(4 rows)
но, конечно, показывает мне самые старые элементы.
Если я это сделаю:
userdb=> explain analyze select distinct on (x,y,updated) * from foo order by x, y, updated desc;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Unique (cost=278945.67..294779.11 rows=158334 width=38) (actual time=3823.677..4808.844 rows=1583344 loops=1)
-> Sort (cost=278945.67..282904.03 rows=1583344 width=38) (actual time=3823.674..4482.405 rows=1583344 loops=1)
Sort Key: x, y, updated DESC
Sort Method: external merge Disk: 79536kB
-> Seq Scan on foo (cost=0.00..29314.44 rows=1583344 width=38) (actual time=0.013..142.781 rows=1583344 loops=1)
Planning Time: 0.075 ms
Execution Time: 4888.878 ms
(7 rows)
затем он дает мне самые последние данные, а это то, что мне нужно, но это занимает в пять раз больше времени.
Я хотел бы получить следующее:
Table "public.foo"
Column | Type | Collation | Nullable | Default
---------+--------------------------+-----------+----------+---------
x | character varying | | not null |
y | character varying | | not null |
z | integer | | |
updated | timestamp with time zone | | not null |
Indexes:
"foo_pkey" PRIMARY KEY, btree (x, y, updated DESC)
Это просто приводит к синтаксической ошибке:
create table foo (x varchar, y varchar, z text, updated timestamptz, primary key(x, y, updated desc));
Я могу сделать это:
postgres=# create index on foo (x, y, updated desc);
CREATE INDEX
postgres=#
который исправляет запрос, но затем я получаю два индекса, которые явно менее производительны, чем один. Я могу удалить первичный ключ, но это выглядит довольно некрасиво. Есть ли лучший способ получить то, что я хочу? Есть ли веская причина, по которой ПК генерирует индекс с определенным порядком и почему обратный порядок может быть проблематичным?
Единственные ссылки, которые я вижу в других местах, - это ветка (https://www.postgresql.org/message-id/[email protected]), в которой предполагается, что уникальный индекс и отсутствие PK - лучший вариант, но это 15 лет. Есть ли что-нибудь лучше сейчас?
то есть действительно ли это лучшее, что я могу сделать?
=> alter table foo drop constraint foo_pkey;
ALTER TABLE
=> create unique index on foo(x,y,updated desc);
CREATE INDEX
=> explain analyze select distinct on (x,y,updated) * from foo order by x, y, updated desc;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=0.43..126019.21 rows=158334 width=38) (actual time=0.025..867.984 rows=1583344 loops=1)
-> Index Scan using foo_x_y_updated_idx on foo (cost=0.43..114144.13 rows=1583344 width=38) (actual time=0.024..521.720 rows=1583344 loops=1)
Planning Time: 0.171 ms
Execution Time: 935.838 ms
(4 rows)
🤔 А знаете ли вы, что...
PostgreSQL обеспечивает возможность создания групп пользователей и управления правами доступа.
Вы не можете создать первичный ключ с индексом, который сортирует столбец по убыванию.
Но для вашего случая использования это может и не потребоваться: PostgreSQL может сканировать индекс в порядке убывания так же эффективно, как и в порядке возрастания, поэтому вам следует переписать запрос следующим образом:
SELECT DISTINCT ON (x, y, updated) *
FROM foo
ORDER BY x DESC, y DESC, updated DESC;
Это позволит просканировать индекс первичного ключа назад и не потребует сортировки.
Если для вас важно, чтобы результат был отсортирован по возрастанию для x и y, вам придется либо стиснуть зубы и создать второй индекс, либо добавить еще одну сортировку после DISTINCT, что должно быть немного дешевле, чем сортировка до этого. DISTINCT:
SELECT *
FROM (SELECT DISTINCT ON (x, y, updated) *
FROM foo
ORDER BY x DESC, y DESC, updated DESC
) AS q
ORDER BY x, y;