work_mem - 1GB Сортируется в памяти. Какие еще параметры нужно назвать для информативности ?
Кстати если сделать вот так :
select * from (SELECT DISTINCT t0.groupname as s1, t1.groupname, t2.groupname
FROM grouptree as t0
left join models as m on m.id = t0.idmodel
left join grouptree as t1 on t1.parent = t0.groupno AND t1.idmodel = t0.idmodel
left join grouptree as t2 on t2.parent = t1.groupno AND t2.idmodel = t1.idmodel
where t0.parent = 0 AND m.typeauto = 0) as t
order by t.s1 ASC
Что, я считаю, в корне не верно, то запрос отработает за секунды.
"Sort (cost=147755.31..147835.47 rows=32063 width=117) (actual time=1241.491..1241.930 rows=10716 loops=1)"
" Output: t0.groupname, t1.groupname, t2.groupname"
" Sort Key: t0.groupname"
" Sort Method: quicksort Memory: 2612kB"
" Buffers: shared hit=1240970"
" -> HashAggregate (cost=144714.35..145034.98 rows=32063 width=117) (actual time=1228.137..1229.846 rows=10716 loops=1)"
" Output: t0.groupname, t1.groupname, t2.groupname"
" Buffers: shared hit=1240970"
" -> Nested Loop Left Join (cost=2379.07..144473.88 rows=32063 width=117) (actual time=29.937..852.961 rows=1391881 loops=1)"
" Output: t0.groupname, t1.groupname, t2.groupname"
" Buffers: shared hit=1240970"
" -> Nested Loop Left Join (cost=2378.64..128016.41 rows=32063 width=86) (actual time=29.927..172.365 rows=264099 loops=1)"
" Output: t0.groupname, t1.groupname, t1.idmodel, t1.groupno"
" Buffers: shared hit=162206"
" -> Hash Join (cost=2378.21..25071.47 rows=32063 width=47) (actual time=29.915..67.004 rows=30423 loops=1)"
" Output: t0.groupname, t0.idmodel, t0.groupno"
" Hash Cond: (t0.idmodel = m.id)"
" Buffers: shared hit=21731"
" -> Bitmap Heap Scan on public.grouptree t0 (cost=660.19..22638.32 rows=35066 width=47) (actual time=22.059..35.273 rows=34960 loops=1)"
" Output: t0.idmodel, t0.groupno, t0.parent, t0.groupname, t0.groupnameen, t0.pictureindex, t0.mark, t0.sortorder"
" Recheck Cond: (t0.parent = 0)"
" Buffers: shared hit=20791"
" -> Bitmap Index Scan on "2-parent-to-idmodel" (cost=0.00..651.42 rows=35066 width=0) (actual time=14.175..14.175 rows=34960 loops=1)"
" Index Cond: (t0.parent = 0)"
" Buffers: shared hit=98"
" -> Hash (cost=1346.41..1346.41 rows=29729 width=4) (actual time=7.824..7.824 rows=29715 loops=1)"
" Output: m.id"
" Buckets: 4096 Batches: 1 Memory Usage: 1045kB"
" Buffers: shared hit=940"
" -> Seq Scan on public.models m (cost=0.00..1346.41 rows=29729 width=4) (actual time=0.003..5.017 rows=29715 loops=1)"
" Output: m.id"
" Filter: (m.typeauto = 0)"
" Rows Removed by Filter: 2798"
" Buffers: shared hit=940"
" -> Index Scan using "2-parent-to-idmodel" on public.grouptree t1 (cost=0.43..3.20 rows=1 width=51) (actual time=0.001..0.002 rows=9 loops=30423)"
" Output: t1.idmodel, t1.groupno, t1.parent, t1.groupname, t1.groupnameen, t1.pictureindex, t1.mark, t1.sortorder"
" Index Cond: ((t1.parent = t0.groupno) AND (t1.idmodel = t0.idmodel))"
" Buffers: shared hit=140475"
" -> Index Scan using "2-parent-to-idmodel" on public.grouptree t2 (cost=0.43..0.50 rows=1 width=47) (actual time=0.001..0.002 rows=5 loops=264099)"
" Output: t2.idmodel, t2.groupno, t2.parent, t2.groupname, t2.groupnameen, t2.pictureindex, t2.mark, t2.sortorder"
" Index Cond: ((t2.parent = t1.groupno) AND (t2.idmodel = t1.idmodel))"
" Buffers: shared hit=1078764"
"Total runtime: 1242.392 ms"
Как правильно отсортировать ?