Всем привет! Помогите разобраться плз. Есть таблица: \d+ stat.ips

Table "stat.ips"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+--------------------------+-----------+----------+--------------------------------------+----------+--------------+-------------
id | bigint | | not null | nextval('stat.ips_id_seq'::regclass) | plain | |
time | timestamp with time zone | | not null | | plain | |
ip | character varying(40) | | not null | | extended | |
type | character varying(32) | | not null | | extended | |
product_id | integer | | not null | | plain | |
customer_id | integer | | | | plain | |
raw | text | | | ''::text | extended | |
server_id | character varying(64) | | | | extended | |
created | timestamp with time zone | | not null | now() | plain | |
data | jsonb | | not null | '{}'::jsonb | extended | |
Indexes:
"id_pkey" PRIMARY KEY, btree (id)
"ip" btree (ip)
"ip_label_prod" btree (ip, type, product_id)
"ip_label_prod_cust" btree (ip, type, product_id, customer_id)
"ip_product_time" btree (ip, product_id, "time")
"ip_server_id" btree (server_id)
"ips_customer_id" btree (customer_id)
"ips_customer_id_time_product_id_idx" btree (customer_id, "time", product_id)
Referenced by:
TABLE "stat.addon_usage" CONSTRAINT "fk_addon_usage_ip_record" FOREIGN KEY (ip_record_id) REFERENCES stat.ips(id) ON DELETE CASCADE

И простой запрос по ней:
explain SELECT "customer_id","ip","created","product_id" FROM stat.ips WHERE type = 'CHECKIN' AND product_id = 1001;
QUERY PLAN
----------------------------------------------------------------------
Seq Scan on ips (cost=0.00..18475257.59 rows=217570390 width=30)
Filter: (((type)::text = 'CHECKIN'::text) AND (product_id = 1001))
(2 rows)


Не могу сделать explain analyze - потому что в таблице 360млн строк и полное сканирование выполняется больше 2часов, поэтому сделал обычный explain. Вопрос -почему так долго выполняется этот запрос и не использует индекс вместо seq scan? данный индекс ""ip_label_prod" btree (ip, type, product_id)" не подходит из-за наличия третьего поля и нужен отдельный только с type и product_id?

P.S. еще смущает то что type зачем то приводится к типу ::text , мб поэтому индекс и не используется, но пока не понимаю как это исправить?

по type у вас индекса нет. по product_id тоже нет. таким образом, кроме seq scan и вариантов не остаётся.

Postgres так и не научился Index Skip Scan?

он давно умеет. просто не пишет слово “Skip” в плане

Create index ips_type_idx on stat.ips (type);

А почему без product _id?

по type 1вым должен быть

Ничего подобного. Никогда не умел и до сих пор не умеет: https://wiki.postgresql.org/wiki/Loose_indexscan @SergeyPpro — см. тоже.

Index Skip Scan — умение использовать 2й (и последующие) аттрибуты в индексном ключе, пропуская 1й (отсюда и название). Loose Scan — перебор уникальных вдоль индекса. Не одно и то же. Первое умеет, второе — нет. Но работа над SOAP идёт, может быть и научится, надо будет посмотреть детальнее.

Про Loose Scan я такое уже видел, а вот про Skip Sсan впервые встретил

Ещё раз — не умеет ни одного, ни другого. > умение использовать 2й (и последующие) аттрибуты в индексном ключе Нет. Вы ссылку-то открыли, извините? Ту, где есть и определения, и подробное описание того и другого... и та информация (от разработчиков PostgreSQL!), что у нас нет ни того, ни другого (в отличие от большинства других СУБД)? ;)

Слушайте, кстати, а что у вас там за сисиема, что 200 гиг данных более 5 минут seq scan идёт? Покажыте explain (analyze, verbose, buffers, timing, settings) SELECT "customer_id","ip","created","product_id" FROM stat.ips LIMIT 1000000

explain (analyze, verbose, buffers, timing) SELECT "customer_id","ip","created","product_id" FROM stat.ips LIMIT 1000000; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- ------ Limit (cost=0.00..45259.53 rows=1000000 width=30) (actual time=0.031..547.598 rows=1000000 loops=1) Output: customer_id, ip, created, product_id Buffers: shared read=27052 I/O Timings: read=284.740 -> Seq Scan on stat.ips (cost=0.00..16644311.38 rows=367752638 width=30) (actual time=0.029..468.794 rows=1000000 loo ps=1) Output: customer_id, ip, created, product_id Buffers: shared read=27052 I/O Timings: read=284.740 Planning Time: 1.430 ms Execution Time: 591.145 ms

Хм. То есть миллион строк читается за полсекунды. Триста миллионов строк при таком раскладе должно читаться за 3 минуты. А не за два часа. Можэт, правда, этот миллион был в дисковом кэшэ? Правда, с чего бы... Но... Могли бы быть, при некоторых условиях. А если 10 миллионов такжэ прочитать? Или 50 миллионов Какой будет вывод explain (analyze, buffers, settings, timing) ? (Кстати, почто вы settings забыли -- тут тожэ могли бы быть какие-то советы...)

Спасибо за рекомендацию, изначально запрос был совсем дурацкий (в подзапросе выбиралась вся таблица 350млн строк) потом из нее опять выбирались определенные столбцы, но уже с условиями, я просто сократил его до варианта без подзапроса (ибо не понятно зачем он там). Так вот именно он висел пару часов у меня. Собственно я попробовал уже и 10млн и 100 и всю таблицу выбрать, самое долгое 7 минут. То есть именно вариант с вложенным чтением всей таблицы и потом выборки по критериям ещё 200млн давал очень долгий запрос. Насчёт settings - ее нету а 11 версии pg

11 ещё pg, да. Не то, чтобы совсем тяжко, но. При всё жэлании explain(analyze) простого запроса не должэн занимать большэ, чем explain(analyze) от SELECT *

Можэт, он там случайно на блокировке завис? Какое-нибудь создание индэкса или что-то такое.

нет, он выполнялся на реплике, посмотрел по pg_stat_activity он был в состоянии active

но кстати что странно и с чем еще не разобрался, у реплики есть stream_delay таймаут и она по нему не убила этот запрос

Ну, max_standby_streaming_delay и не обязан отстреливать — только когда запрос наткнётся на реально страницу, из которой нужное отвакуумили (возможно). А ещё там можэт быть включен hot_standby_feedback. А ещё можэт на ведущем долгий запрос идти.

hot_standby_feedback - выключен долгих (настолько долгих) запросов на ведущем небыло ну max_standby_streaming_delay должен насколько понимаю отстреливать сразу после истечения указанного времени с начала конфликта применения нового wal с выполняемыми на реплике запросами. И это как раз видно было что с самого начала этой длинной транзакции рос и лаг репликации