Всем привет! EXPLAIN SELECT "idx" FROM "geometries" WHERE "geometry" && ST_GeomFromText('POLYGON

(...)', 4326) ORDER BY "idx" DESC LIMIT 100;

Limit (cost=0.57..324664.77 rows=100 width=8)
-> Index Scan Backward using geometries_pkey on geometries (cost=0.57..591612854.78 rows=182223 width=8)
Filter: (geom && '0103000020E6100000010000000500000040C0AA1AE65BF2BF6FABA2F85DB7454040C0AA1AE65BF2BF647D72A327404640AC433F9B2A31EB3F647D72A327404640AC433F9B2A31EB3F6FABA2F85DB7454040C0AA1AE65BF2BF6FABA2F85DB74540'::geometry)
Вот такой запрос вместо индекса по геометрии берет индекс по pk, но в виду наличия фильтра видимо превращается в полный скан таблицы (работает очень медленно). Правильно было бы убрать просто ORDER BY чтобы использовался индекс по геометрии, но сам запрос формируется geoserver-ом и поправить его нельзя. Можно ли как-то зафорсить postgres-у юзать индекс по геометрии?

Для начала — проверьте, что дело в order by , и без него (и, отдельно, без лимита ) запрос отработает по индэксу. Ну, и https://t.me/pgsql/303899

Хм. Теперь понятно. Да, опять дурацкая ситуацыя — планировщик считает данные некореллированными, а реально они там все в концэ индэкса лежат. А нельзя заставить там limit=500 сказать этому приложэнию? Ну, если нет — то можно опустить (втрое-вчетверо) оба page_cost: и seq и random. Тожэ должно проломить дурака.

Вот лимит вполне можно поменять, да

А, кстати, банальный вопрос — а параметры сервера из какого-нибудь конфигуратора типа cybertec применили? А то вдруг у вас там random_page_cost=4 на ssd...

Нет не в курсе про cybertec - надо глянуть что это такое. Вполне возможно

Два вопроса: там SSD? И show random_page_cost;

Amazon gp2, да под капотом SSD и random_page_cost именно 4 сейчас выставлен

EXPLAIN (ANALYZE, BUFFERS) SELECT "idx" FROM "public"."geometries" WHERE "geometry" && ST_GeomFromText('POLYGON ((-1.147436241310075 43.4325552744184, -1.147436241310075 44.5012096699745 4, 0.8497517616434513 44.50120966997454, 0.8497517616434513 43.4325552744184, -1.147436241310075 43.4325552744184))', 4326) ORDER BY "idx" DESC LIMIT 500; Limit (cost=964870.82..964872.07 rows=500 width=8) (actual time=481.608..482.641 rows=500 loops=1) Buffers: shared hit=9851 read=27246 -> Sort (cost=964870.82..965464.26 rows=237376 width=8) (actual time=481.604..481.971 rows=500 loops=1) Sort Key: idx DESC Sort Method: top-N heapsort Memory: 60kB Buffers: shared hit=9851 read=27246 -> Index Scan using geometries_geom_idx on geometries (cost=0.55..953042.63 rows=237376 width=8) (actual time=0.343..325.720 rows=165633 loops=1) Index Cond: (geometry && '0103000020E6100000010000000500000040C0AA1AE65BF2BF6FABA2F85DB7454040C0AA1AE65BF2BF647D72A327404640AC433F9B2A31EB3F647D72A327404640AC433F9B2A31EB3F6FABA2F85DB7454040C0AA1AE 65BF2BF6FABA2F85DB74540'::geometry) Buffers: shared hit=9851 read=27246 Planning Time: 0.348 ms Execution Time: 483.017 ms (11 rows) увеличение лимита реально помогло

Тогда банально настройте сервер по рекомендацыям — всё станет заметно легче. В частности, вероятно именно эта проблема уйдёт. Но, кстати, если вы в начале пути — то самое время поставить 14.2

Спасибо большое! Планируем в RDS все перенести. А что за рекомендации имеется в виду?

gp2 вас покусает тем что в один прекрасный день вы не сможете снять бекап, посмотрите как минимум на io1

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

Имелось в виду — настройки вроде http://pgconfigurator.cybertec.at/ или какого другого вменяемого конфигуратора. Там из действительно важных — shared_buffers, random_page_cost, что-то про workers и effective_cash_size. Немного. Но полезно. Да и остальные оттуда невредно.

Спасибо большое, будем изучать!

Поднятие limit-а не для всех запросов помогает надо сказать.

Отправляйте анализ запросов, будем думать. Но сначала — конфигуратор. Собственно, можэте попробовать set random_page_cost=1 — для этого запроса ужэ должно сработать.