Есть view - таблица с клиентами с кучей джонов, нашел такую проблему, если беру тот запрос который во view и добавляю order by name он работает во много раз быстрее чем если сделаю тоже самое но на view

я так понимаю если накладываешь order by на view, то база сначала соединяет все таблицы, а уже потом сортирует и выводит результат ограничивая его, а если сделать на прямую запрос, то база явно ограничивает записи по главной таблице т.к. поля name как раз в ней, а уже потом соединяет оставшиеся таблицы.

Что тут можно сделать кроме как не использовать view?

Maxim Boguk,

SELECT
objects.id,
split_part((objects.name)::text, '~|~'::text, 1) AS name,
objects.type,
objects.active,
clients.data_rozhdeniya,
clients.pol,
clients.nationalitys_id,
clients.num_card,
clients.passfrase,
clients.type_client,
fio_to_small(mkuser.name) AS mkuser,
fio_to_small(moduser.name) AS moduser,
clients.home_metro_id,
clients.work_metro_id,
home_m.name AS home_metro,
work_m.name AS work_metro,
hotels_countries.name AS nationalitys,
clients.lk_account,
to_char(clients.lk_dt, 'yyyy-mm-dd HH24:MI:SS'::text) AS lk_dt,
clients.lk_comein,
to_char(clients.lk_comein_dt, 'yyyy-mm-dd HH24:MI:SS'::text) AS lk_comein_dt,
( SELECT
sum(bonus_transactions.summa) AS sum
FROM bonus_transactions
WHERE
(bonus_transactions.clients_id = objects.id)) AS bonus,
clients.email,
clients.phone
FROM objects
JOIN clients ON clients.clients_id = objects.id
LEFT JOIN objects mkuser ON mkuser.id = objects.mk_user_id
LEFT JOIN objects moduser ON moduser.id = objects.mod_user_id
LEFT JOIN nationalitys ON nationalitys.id = clients.nationalitys_id
LEFT JOIN offices ON offices.offices_id = clients.office_id
LEFT JOIN hotels_countries ON nationalitys.hotels_countries_id = hotels_countries.id
LEFT JOIN metro home_m ON home_m.id = clients.home_metro_id
LEFT JOIN metro work_m ON work_m.id = clients.work_metro_id


ВОТ план view SELECT * FROM clients_t ORDER BY name LIMIT 30 OFFSET 0

QUERY PLAN
Limit (cost=10409.58..10409.66 rows=30 width=292) (actual time=624.277..624.292 rows=30 loops=1)
-> Sort (cost=10409.58..10444.64 rows=14021 width=292) (actual time=624.275..624.283 rows=30 loops=1)
Sort Key: (split_part((objects.name)::text, '~|~'::text, 1))
Sort Method: top-N heapsort Memory: 32kB
-> Hash Left Join (cost=1915.92..9995.48 rows=14021 width=292) (actual time=101.735..594.348 rows=14031 loops=1)
Hash Cond: (clients.work_metro_id = work_m.id)
-> Hash Left Join (cost=1913.12..2824.44 rows=14021 width=268) (actual time=101.543..189.642 rows=14031 loops=1)
Hash Cond: (clients.home_metro_id = home_m.id)
-> Hash Left Join (cost=1910.31..2769.05 rows=14021 width=244) (actual time=101.434..183.016 rows=14031 loops=1)
Hash Cond: (objects.mod_user_id = moduser.id)
-> Hash Left Join (cost=1275.69..1936.93 rows=14021 width=223) (actual time=71.322..141.891 rows=14031 loops=1)
Hash Cond: (objects.mk_user_id = mkuser.id)
-> Hash Join (cost=641.07..1095.50 rows=14021 width=202) (actual time=41.123..97.224 rows=14031 loops=1)
Hash Cond: (clients.clients_id = objects.id)
-> Hash Left Join (cost=6.45..254.07 rows=14021 width=151) (actual time=0.259..23.301 rows=14031 loops=1)
Hash Cond: (clients.nationalitys_id = nationalitys.id)
-> Seq Scan on clients (cost=0.00..181.02 rows=14021 width=138) (actual time=0.012..5.397 rows=14031 loops=1)
-> Hash (cost=6.34..6.34 rows=30 width=25) (actual time=0.233..0.233 rows=30 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 2kB
-> Hash Right Join (cost=2.13..6.34 rows=30 width=25) (actual time=0.057..0.214 rows=30 loops=1)
Hash Cond: (hotels_countries.id = nationalitys.hotels_countries_id)
-> Seq Scan on hotels_countries (cost=0.00..3.25 rows=248 width=25) (actual time=0.012..0.091 rows=248 loops=1)
-> Hash (cost=2.03..2.03 rows=30 width=8) (actual time=0.032..0.032 rows=30 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 2kB
-> Seq Scan on nationalitys (cost=0.00..2.03 rows=30 width=8) (actual time=0.004..0.020 rows=30 loops=1)
-> Hash (cost=505.03..505.03 rows=37027 width=59) (actual time=40.837..40.837 rows=36949 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 3351kB
-> Seq Scan on objects (cost=0.00..505.03 rows=37027 width=59) (actual time=0.007..18.366 rows=36949 loops=1)
-> Hash (cost=505.03..505.03 rows=37027 width=37) (actual time=30.165..30.165 rows=36949 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 2675kB
-> Seq Scan on objects mkuser (cost=0.00..505.03 rows=37027 width=37) (actual time=0.007..12.804 rows=36949 loops=1)
-> Hash (cost=505.03..505.03 rows=37027 width=37) (actual time=30.085..30.085 rows=36949 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 2675kB
-> Seq Scan on objects moduser (cost=0.00..505.03 rows=37027 width=37) (actual time=0.006..12.152 rows=36949 loops=1)
-> Hash (cost=2.18..2.18 rows=179 width=28) (actual time=0.095..0.095 rows=187 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Seq Scan on metro home_m (cost=0.00..2.18 rows=179 width=28) (actual time=0.009..0.043 rows=187 loops=1)
-> Hash (cost=2.18..2.18 rows=179 width=28) (actual time=0.087..0.087 rows=187 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Seq Scan on metro work_m (cost=0.00..2.18 rows=179 width=28) (actual time=0.004..0.038 rows=187 loops=1)
Planning time: 6.189 ms
Execution time: 626.834 ms

ВОТ план того же самого без view

QUERY PLAN
Limit (cost=3.71..79.13 rows=30 width=292) (actual time=60.795..78.730 rows=30 loops=1)
-> Nested Loop Left Join (cost=3.71..35254.78 rows=14021 width=292) (actual time=60.792..78.711 rows=30 loops=1)
-> Nested Loop Left Join (cost=3.56..26027.97 rows=14021 width=268) (actual time=60.611..77.157 rows=30 loops=1)
-> Nested Loop Left Join (cost=3.42..23916.82 rows=14021 width=244) (actual time=60.606..77.118 rows=30 loops=1)
-> Nested Loop Left Join (cost=3.12..19348.19 rows=14021 width=223) (actual time=60.596..77.018 rows=30 loops=1)
-> Nested Loop Left Join (cost=2.83..14779.55 rows=14021 width=202) (actual time=60.579..76.783 rows=30 loops=1)
Join Filter: (nationalitys.id = clients.nationalitys_id)
Rows Removed by Join Filter: 856
-> Nested Loop (cost=0.70..12249.35 rows=14021 width=181) (actual time=60.364..76.096 rows=30 loops=1)
-> Index Scan using index_name on objects (cost=0.41..1002.96 rows=37027 width=59) (actual time=0.019..9.995 rows=22724 loops=1)
-> Index Scan using clients_turists_settings_pkey on clients (cost=0.29..0.30 rows=1 width=138) (actual time=0.002..0.002 rows=0 loops=22724)
Index Cond: (clients_id = objects.id)
-> Materialize (cost=2.13..6.49 rows=30 width=25) (actual time=0.003..0.014 rows=30 loops=30)
-> Hash Right Join (cost=2.13..6.34 rows=30 width=25) (actual time=0.082..0.246 rows=30 loops=1)
Hash Cond: (hotels_countries.id = nationalitys.hotels_countries_id)
-> Seq Scan on hotels_countries (cost=0.00..3.25 rows=248 width=25) (actual time=0.014..0.101 rows=248 loops=1)
-> Hash (cost=2.03..2.03 rows=30 width=8) (actual time=0.038..0.038 rows=30 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 2kB
-> Seq Scan on nationalitys (cost=0.00..2.03 rows=30 width=8) (actual time=0.008..0.023 rows=30 loops=1)
-> Index Scan using objects_pkey on objects mkuser (cost=0.29..0.32 rows=1 width=37) (actual time=0.005..0.006 rows=1 loops=30)
Index Cond: (id = objects.mk_user_id)
-> Index Scan using objects_pkey on objects moduser (cost=0.29..0.32 rows=1 width=37) (actual time=0.002..0.002 rows=0 loops=30)
Index Cond: (id = objects.mod_user_id)
-> Index Scan using metro_pkey on metro home_m (cost=0.14..0.15 rows=1 width=28) (actual time=0.000..0.000 rows=0 loops=30)
Index Cond: (id = clients.home_metro_id)
-> Index Scan using metro_pkey on metro work_m (cost=0.14..0.15 rows=1 width=28) (actual time=0.000..0.000 rows=0 loops=30)
Index Cond: (id = clients.work_metro_id)
Planning time: 6.670 ms
Execution time: 79.062 ms

westvovik,

В запросе, подзапрос забыл удалить он не участвовал в EXPLAIN

qwwq,

Да я уже сам увидел, блин костыль вставляли, так надо было, мда!