Оптимизация запросов к Постгре
есть запрос
1. С вложеным подзапросом
2. Без вложеного запроса
после чего в пхп скрипте прогоняю в цикле на выборку из towns_list
Практика показала что второй вариант выполняеться быстрей хотя и запросов к БД больше, хотя моя логика подсказывала что первый должен быть быстрей...
таблица beach_tours порядка 150 000 записей, towns_list - 150, результирующий набор - 7 - 8 строк....
Может подзапрос не правильно написан?
towns_list
id - serial primary key
name - text
beach_tours
id - serial primary key
country - integer
остальные поля integer
+ пару text`ов
А теперь версию СУБД, про индексы расскажи какие где и ЗАДАЧУ - что именно ты хочешь сделать запросом (запрос кстати построен неграмотно - не в обиду)
А теперь версию СУБД, про индексы расскажи какие где и ЗАДАЧУ - что именно ты хочешь сделать запросом (запрос кстати построен неграмотно - не в обиду)
PostgreSQL 7.4.13
Индексы - стандартные постгрешные b-tree
Не в обиду конечно, если б запрос был правильно составлен и выполнялся быстрей - поста бы не было :)
ЗАДАЧА...
Первое нужно выбрать уникальные country из beach_tours, после чего не хочу лишний раз дергать БД прогоняя в цикле внешний полученный ключ запросами ко второй таблицы для получения имени... все просто.. :)
Хочу вместить в одном заросе, но в одно запросе получаеться от 3 до 10 раз дольше выполнение...
Индексы должны быть в towns_list на поле id, в beach_tours на поле country.
Как протестишь - напиши сколько времени занял и напиши результат EXPLAIN ANALYZE по этому запросу
Индексы должны быть в towns_list на поле id, в beach_tours на поле country.
Как протестишь - напиши сколько времени занял и напиши результат EXPLAIN ANALYZE по этому запросу
Хм, интересное решение :))
Экспланы
1. для SELECT DISTINCT(country), (SELECT name FROM countries_list WHERE id=country) FROM beach_tours
-> Sort (cost=17199203602905.69..17199203603335.66 rows=171991 width=4) (actual time=53163.899..53799.075 rows=171991 loops=1)
Sort Key: country, (subplan)
-> Seq Scan on beach_tours (cost=100000000.00..17199203587949.37 rows=171991 width=4) (actual time=1.132..51478.012 rows=171991 loops=1)
SubPlan
-> Seq Scan on countries_list (cost=100000000.00..100000020.84 rows=1 width=11) (actual time=0.168..0.275 rows=1 loops=171991)
Filter: (id = $0)
Total runtime: 55011.421 ms
8 запис(ь/и/ей)
Время выполнения: 55,013.551 мсек
2. для SELECT t.name, q1.country FROM (SELECT COUNT(*), country FROM beach_tours GROUP BY country) as q1, towns_list t WHERE t.id=q1.country
Hash Cond: ("outer".id = "inner".country)
-> Seq Scan on countries_list t (cost=100000000.00..100000020.47 rows=147 width=15) (actual time=0.006..0.311 rows=147 loops=1)
-> Hash (cost=339718.27..339718.27 rows=6 width=4) (actual time=2318.595..2318.595 rows=0 loops=1)
-> Subquery Scan q1 (cost=0.00..339718.27 rows=6 width=4) (actual time=20.121..2318.547 rows=6 loops=1)
-> GroupAggregate (cost=0.00..339718.21 rows=6 width=4) (actual time=20.116..2318.508 rows=6 loops=1)
-> Index Scan using date on beach_tours (cost=0.00..338858.24 rows=171991 width=4) (actual time=0.691..589.579 rows=171991 loops=1)
Total runtime: 2319.500 ms
8 запис(ь/и/ей)
Время выполнения: 2,354.203 мсек
Убиваешь на этих таблицах ВСЕ индексы. Создаешь те что я сказал постом выше.
Потом делаешь VACUUM FULL ANALYZE
Потом снова тестишь запросы и снова показываешь EXPLAIN ANALYZE
Для PostgreSQL это очень медленно. Должно быть на два-три порядка быстрее.
Зато я догадываюсь почему =)))
Убиваешь на этих таблицах ВСЕ индексы. Создаешь те что я сказал постом выше.
Потом делаешь VACUUM FULL ANALYZE
Потом снова тестишь запросы и снова показываешь EXPLAIN ANALYZE
Для PostgreSQL это очень медленно. Должно быть на два-три порядка быстрее.
Создал индекс по полю country, в towns_list уже есть индекс по полю id - ситуация толком не изменилась - 300 мс
Зато я догадываюсь почему =)))
Просвети :)
Проблема в методе выборки. Всякие hashjoin, mergejoin и т.д.
Как только ты мне напишешь то что я попросил - я напишу тебе как можно исправить.
Проблема в методе выборки. Всякие hashjoin, mergejoin и т.д.
Как только ты мне напишешь то что я попросил - я напишу тебе как можно исправить.
Sort Key: t.name
-> Hash Join (cost=100340409.30..100340430.56 rows=5 width=15) (actual time=969.867..970.665 rows=6 loops=1)
Hash Cond: ("outer".id = "inner".country)
-> Seq Scan on countries_list t (cost=100000000.00..100000020.47 rows=147 width=15) (actual time=0.019..0.487 rows=147 loops=1)
-> Hash (cost=340409.29..340409.29 rows=6 width=4) (actual time=969.650..969.650 rows=0 loops=1)
-> Subquery Scan q1 (cost=0.00..340409.29 rows=6 width=4) (actual time=16.318..969.614 rows=6 loops=1)
-> GroupAggregate (cost=0.00..340409.23 rows=6 width=4) (actual time=16.312..969.575 rows=6 loops=1)
-> Index Scan using country on beach_tours (cost=0.00..339549.26 rows=171991 width=4) (actual time=0.270..556.749 rows=171991 loops=1)
Total runtime: 970.837 ms
10 запис(ь/и/ей)
Время выполнения: 973.168 мсек
SQL-запрос выполнен.
SET enable_hashjoin=off; SET enable_mergejoin=off;SELECT t.name, q1.country FROM (SELECT COUNT(*), country FROM beach_tours GROUP BY country) as q1, towns_list t WHERE t.id=q1.country
А потом посмотри что напишет EXPLAIN
SET enable_hashjoin=off; SET enable_mergejoin=off;
EXPLAIN ANALYZE SELECT t.name, q1.country FROM (SELECT COUNT(*), country FROM beach_tours GROUP BY country) as q1, towns_list t WHERE t.id=q1.country
А потом посмотри что напишет EXPLAIN
Join Filter: ("inner".id = "outer".country)
-> Subquery Scan q1 (cost=0.00..340409.29 rows=6 width=4) (actual time=15.877..872.309 rows=6 loops=1)
-> GroupAggregate (cost=0.00..340409.23 rows=6 width=4) (actual time=15.872..872.273 rows=6 loops=1)
-> Index Scan using country on beach_tours (cost=0.00..339549.26 rows=171991 width=4) (actual time=0.274..478.091 rows=171991 loops=1)
-> Materialize (cost=100000020.47..100000021.94 rows=147 width=15) (actual time=0.006..0.385 rows=147 loops=6)
-> Seq Scan on countries_list t (cost=100000000.00..100000020.47 rows=147 width=15) (actual time=0.019..0.543 rows=147 loops=1)
Total runtime: 876.515 ms
8 запис(ь/и/ей)
Время выполнения: 878.369 мсек
хм, нужно перечитать раздел по експлан... :)
PostgreSQL очень не любит COUNT() - это самое слабое ее место. Попробуем от него избавится.
SET enable_hashjoin=off; SET enable_mergejoin=off;SELECT t.name, q1.country FROM (SELECT DISTINCT country FROM beach_tours) as q1, towns_list t WHERE t.id=q1.country
PostgreSQL очень не любит COUNT() - это самое слабое ее место. Попробуем от него избавится.
Join Filter: ("inner".id = "outer".country)
-> Subquery Scan q1 (cost=0.00..339979.29 rows=6 width=4) (actual time=0.177..2283.254 rows=6 loops=1)
-> Unique (cost=0.00..339979.23 rows=6 width=4) (actual time=0.172..2283.220 rows=6 loops=1)
-> Index Scan using country on beach_tours (cost=0.00..339549.26 rows=171991 width=4) (actual time=0.168..1146.817 rows=171991 loops=1)
-> Materialize (cost=100000020.47..100000021.94 rows=147 width=15) (actual time=0.004..0.355 rows=147 loops=6)
-> Seq Scan on countries_list t (cost=100000000.00..100000020.47 rows=147 width=15) (actual time=0.007..0.368 rows=147 loops=1)
Total runtime: 2287.299 ms
8 запис(ь/и/ей)
Время выполнения: 2,289.255 мсек
хм :) судя по експлану похоже DISTINCT ему нравиться еще меньше :), хотя запрос идет в рамках 250 - 300 мс
Где ты увидел 250-300 мс??? Total runtime: 2287.299 ms - это 2287 миллисекунд или чуть больше 2 сек.
Вообще ничего больше конкретного сказать не могу. Могу только дать направление для раскопок:
1) УБИТЬ все индексы, потом создать индексы на тех полях что я говорил и VACUUM ANALYZE
2) Поиграть с параметрами enable_hashjoin, enable_mergejoin попробовав разные комбинации.
3) Обновиться до версии 8.2 если возможно - она быстрее 7.х
4) Поковыряться с параметрами выделения памяти для сервера БД в файле postgresql.conf - если возможно. Инфа по нему есть в официальном мануале.
5) Произвести апгрейд железа :D
6) Попробовать некоторые из запросов ниже... Но имхо это уже извращения
SET enable_hashjoin=off; SET enable_mergejoin=off;SELECT t.name, q1.country FROM (SELECT SUM(id), country FROM beach_tours GROUP BY country) as q1, towns_list t WHERE t.id=q1.country
SELECT DISTINCT ON (b.country) b.country, t.name FROM beach_tours b, towns_list t WHERE b.country=t.id
PostgreSQL очень не любит COUNT() - это самое слабое ее место. Попробуем от него избавится.
а для трех таблиц?
FROM (SELECT country, town FROM beach_tours WHERE country='87' LIMIT 10 OFFSET 0) as q1,
countries_list as c, towns_list as t
WHERE c.id=q1.country AND t.id=q1.town
возвращает 0 записей....
Логика таже что и в двух
towns_list - id(serial), name
countries_list - id(serial), name
beach_tours - country (int - уже индекс:)), town (int - уже индекс:))
вытащить нужно как и для двух таблиц... связка город(id) - название, страна(id) - название
а потом уже приклеивать третью таблицу.
а потом уже приклеивать третью таблицу.
ок, санкс большое... буду смотреть.... просто не могу понять логики подключения 3-ей таблицы... будим копать :))
Все разобрался... :( значения по внешенму ключу не было, вот оно и выводило 0
еще раз санкс
Логика такая же как и при подключении второй.
Когда добавил запись понял :) sql вверху рабочий :)