#postgresql
Вопрос:
У меня есть две таблицы: скважины(идентификатор, имя, дополнительное имя) и геодезические(идентификатор, идентификатор скважины, участок)
Для этого запроса к двум таблицам
EXPLAIN ANALYZE
SELECT
wells.name, geodesies.plot
FROM "geodesies" LEFT OUTER JOIN "wells" ON "wells"."id" = "geodesies"."well_id"
ORDER BY LOWER("wells"."name_nso"), "wells"."extra_name"
LIMIT 10;
Выход:
"Limit (cost=1146.27..1146.29 rows=10 width=58) (actual time=64.482..64.488 rows=10 loops=1)"
" -> Sort (cost=1146.27..1176.83 rows=12225 width=58) (actual time=64.480..64.484 rows=10 loops=1)"
" Sort Key: (lower(wells.name_nso)), wells.extra_name"
" Sort Method: top-N heapsort Memory: 27kB"
" -> Hash Left Join (cost=568.17..882.09 rows=12225 width=58) (actual time=11.214..56.280 rows=12225 loops=1)"
" Hash Cond: (geodesies.well_id = wells.id)"
" -> Seq Scan on geodesies (cost=0.00..251.25 rows=12225 width=23) (actual time=0.017..5.533 rows=12225 loops=1)"
" -> Hash (cost=415.30..415.30 rows=12230 width=118) (actual time=11.126..11.127 rows=12230 loops=1)"
" Buckets: 16384 Batches: 1 Memory Usage: 1848kB"
" -> Seq Scan on wells (cost=0.00..415.30 rows=12230 width=118) (actual time=0.009..5.611 rows=12230 loops=1)"
"Planning Time: 0.804 ms"
"Execution Time: 64.544 ms"
В этом запросе не используется какой-либо индекс.
Если я удалю order by из запроса:
EXPLAIN ANALYZE
SELECT
wells.name, geodesies.plot
FROM "geodesies" LEFT OUTER JOIN "wells" ON "wells"."id" = "geodesies"."well_id"
LIMIT 10;
он использует индекс, и вывод выглядит так:
"Limit (cost=0.57..2.86 rows=10 width=19) (actual time=0.042..0.146 rows=10 loops=1)"
" -> Merge Left Join (cost=0.57..2794.76 rows=12225 width=19) (actual time=0.040..0.142 rows=10 loops=1)"
" Merge Cond: (geodesies.well_id = wells.id)"
" -> Index Scan using index_geodesies_on_well_id on geodesies (cost=0.29..979.64 rows=12225 width=23) (actual time=0.023..0.056 rows=10 loops=1)"
" -> Index Scan using wells_pkey on wells (cost=0.29..1631.73 rows=12230 width=28) (actual time=0.013..0.069 rows=10 loops=1)"
"Planning Time: 0.654 ms"
"Execution Time: 0.293 ms"
Как ускорить запрос с заказом по клаузле?
С уважением
PostgresSQL 13
Комментарии:
1. Чтобы найти первые 10 строк в соответствии с порядком сортировки, база данных должна отсортировать все строки из таблицы. И чтобы отсортировать все строки, ему необходимо извлечь все строки. И это делается наиболее эффективно с помощью сканирования Seq
2.
ORDER BY LOWER("wells"."name_nso"), ...
ПОРЯДОК ПО функции всегда является подстаканником.3. @wildplasser: вы можете создать индекс с выражениями из порядка по, но я сомневаюсь, что это поможет здесь.
4. Я знаю это, но я тоже думаю, что это была бы плохая идея. (заказ без ключа левого присоединенного стола в любом случае является своего рода пыткой …)
5. Отключите последовательное сканирование , чтобы увидеть, как изменяется план запроса и сокращается ли время выполнения.