запрос
select d.kratnaim as predpr,rtrim(ltrim(g.naim))|| ' ' || rtrim(ltrim(f.naimulicy))|| ' дом '|| rtrim(ltrim(e.naimdom)) as dom ,rtrim(ltrim(c.kvartira)) as kvartira,q12.dogovor,a2.contragentteplo_id,a2.kratnaimcontragent,a2.plo,a2.kol,a2.dniteplo,a2.dnigvs,q12.dolgdo,q12.dolg,(case when q12.dolgdo>=0 then q12.dolgdo else 0 end) as sumdebn,(case when q12.dolgdo<0 then q12.dolgdo*(-1) else 0 end) as sumkredn,(case when q12.dolg>=0 then q12.dolg else 0 end) as sumdebk,(case when q12.dolg<0 then q12.dolg*(-1) else 0 end) as sumkredk from (select to_number(q1.dogovor,'99999') as dogovor, sum(case when q1.god*100+q1.mesyz<2005*100+6 then coalesce(q1.nas,0)-coalesce(q1.summagvs,0)-coalesce(q1.summateplo,0)-coalesce(q1.opl,0) else 0 end ) as dolgdo,sum( coalesce(q1.nas,0)-coalesce(q1.summagvs,0)-coalesce(q1.summateplo,0)-coalesce(q1.opl,0)) as dolg from (select a1.dogovor,(select d.god from period d where d.period_id=a1.period_id) as god,(select d.mesyz from period d where d.period_id=a1.period_id) as mesyz,(select sum(summa) from prodagateplo b where a1.contragentteplo_id=b.contragentteplo_id group by contragentteplo_id ) as nas, (select sum(summagvs) from lgotteplo b where a1.contragentteplo_id=b.contragentteplo_id group by contragentteplo_id ) as summagvs,(select sum(summateplo) from lgotteplo b where a1.contragentteplo_id=b.contragentteplo_id group by contragentteplo_id ) as summateplo,(select sum(summa) from oplatateplo c where a1.contragentteplo_id=c.contragentteplo_id group by contragentteplo_id ) as opl from contragentteplo a1,period d where a1.period_id=d.period_id and (to_number(a1.dogovor,'99999') IN (select to_number(dogovor,'99999') from contragentteplo,period where contragentteplo.period_id= period.period_id and period.god=2005 and period.mesyz=6 and contragentteplo.gek=6)) and d.god*100+d.mesyz<=2005*100+6) as q1 group by to_number(q1.dogovor,'99999') order by to_number(q1.dogovor,'99999')) as q12,contragentteplo a2,period b,kvartira c,predpr d,dom e,ulicy f,tipulicy g where a2.period_id=b.period_id and to_number(a2.dogovor,'99999')=q12.dogovor and b.god=2005 and b.mesyz=6 and a2.kvartira_id=c.kvartira_id and d.predpr_id=b.predpr_id and a2.dom_id=e.dom_id and e.ulicy_id=f.ulicy_id and f.tipulicy_id=g.tipulicy_id order by f.naimulicy,g.naim,e.nomdom,e.indexdom,e.korpdom,c.nomkv,c.nomkom

Выполнение

Sort (cost=930784.64..930812.26 rows=11049 width=305) (actual time=274423.569..274427.669 rows=1395 loops=1)
Sort Key: f.naimulicy, g.naim, e.nomdom, e.indexdom, e.korpdom, c.nomkv, c.nomkom
-> Hash Join (cost=149399.11..928994.24 rows=11049 width=305) (actual time=59017.250..274025.163 rows=1395 loops=1)
Hash Cond: ("outer".kvartira_id = "inner".kvartira_id)
-> Hash Join (cost=149302.58..928124.29 rows=11049 width=296) (actual time=58868.570..273688.569 rows=1395 loops=1)
Hash Cond: ("outer".ulicy_id = "inner".ulicy_id)
-> Hash Join (cost=149272.05..927928.03 rows=11049 width=236) (actual time=58804.694..273555.969 rows=1395 loops=1)
Hash Cond: ("outer".dom_id = "inner".dom_id)
-> Merge Join (cost=149190.94..927598.30 rows=11049 width=221) (actual time=58725.948..273371.369 rows=1395 loops=1)
Merge Cond: ("outer".dogovor = "inner"."?column11?")
-> Subquery Scan q12 (cost=95630.47..873691.13 rows=15317 width=96) (actual time=20462.997..234919.035 rows=1395 loops=1)
-> GroupAggregate (cost=95630.47..873537.96 rows=15317 width=17) (actual time=20462.983..234889.231 rows=1395 loops=1)
-> Sort (cost=95630.47..95668.77 rows=15317 width=17) (actual time=16056.844..16963.414 rows=59190 loops=1)
Sort Key: to_number((a1.dogovor)::text, '99999'::text)
-> Hash Join (cost=3595.38..94565.72 rows=15317 width=17) (actual time=7956.257..11972.184 rows=59190 loops=1)
Hash Cond: ("outer".period_id = "inner".period_id)
-> Nested Loop (cost=3567.28..94116.42 rows=45950 width=17) (actual time=7898.470..10188.660 rows=59190 loops=1)
-> Unique (cost=3567.28..3570.83 rows=710 width=9) (actual time=7744.567..7783.649 rows=1395 loops=1)
-> Sort (cost=3567.28..3569.05 rows=710 width=9) (actual time=7744.554..7751.098 rows=1395 loops=1)
Sort Key: to_number((contragentteplo.dogovor)::text, '99999'::text)
-> Hash Join (cost=24.83..3533.65 rows=710 width=9) (actual time=7555.039..7700.170 rows=1395 loops=1)
Hash Cond: ("outer".period_id = "inner".period_id)
-> Index Scan using gek on contragentteplo (cost=0.00..3183.43 rows=63659 width=13) (actual time=79.827..7283.339 rows=64097 loops=1)
Index Cond: (gek = 6::numeric)
-> Hash (cost=24.80..24.80 rows=11 width=4) (actual time=2.645..2.645 rows=0 loops=1)
-> Seq Scan on period (cost=0.00..24.80 rows=11 width=4) (actual time=1.500..2.553 rows=19 loops=1)
Filter: ((god = 2005::numeric) AND (mesyz = 6::numeric))
-> Index Scan using dogovornumber on contragentteplo a1 (cost=0.00..126.39 rows=65 width=17) (actual time=0.190..1.067 rows=42 loops=1395)
Index Cond: (to_number((a1.dogovor)::text, '99999'::text) = "outer"."?column2?")
-> Hash (cost=27.27..27.27 rows=329 width=4) (actual time=57.651..57.651 rows=0 loops=1)
-> Seq Scan on period d (cost=0.00..27.27 rows=329 width=4) (actual time=14.489..53.366 rows=970 loops=1)
Filter: (((god * 100::numeric) + mesyz) <= 200506::numeric)
SubPlan
-> GroupAggregate (cost=0.00..12.19 rows=2 width=15) (actual time=2.422..2.424 rows=0 loops=59190)
-> Index Scan using oplatateplo1 on oplatateplo c (cost=0.00..12.16 rows=4 width=15) (actual time=2.171..2.394 rows=0 loops=59190)
Index Cond: ($1 = contragentteplo_id)
-> GroupAggregate (cost=0.00..3.36 rows=2 width=12) (actual time=0.037..0.037 rows=0 loops=59190)
-> Index Scan using lgotteplo1 on lgotteplo b (cost=0.00..3.35 rows=2 width=12) (actual time=0.021..0.021 rows=0 loops=59190)
Index Cond: ($1 = contragentteplo_id)
-> GroupAggregate (cost=0.00..3.36 rows=2 width=12) (actual time=0.046..0.046 rows=0 loops=59190)
-> Index Scan using lgotteplo1 on lgotteplo b (cost=0.00..3.35 rows=2 width=12) (actual time=0.031..0.031 rows=0 loops=59190)
Index Cond: ($1 = contragentteplo_id)
-> GroupAggregate (cost=0.00..3.06 rows=2 width=15) (actual time=0.577..0.580 rows=1 loops=59190)
-> Index Scan using prodagateplo1 on prodagateplo b (cost=0.00..3.04 rows=3 width=15) (actual time=0.406..0.543 rows=1 loops=59190)
Index Cond: ($1 = contragentteplo_id)
-> GroupAggregate (cost=0.00..12.19 rows=2 width=15) (actual time=0.058..0.059 rows=0 loops=57795)
-> Index Scan using oplatateplo1 on oplatateplo c (cost=0.00..12.16 rows=4 width=15) (actual time=0.031..0.036 rows=0 loops=57795)
Index Cond: ($1 = contragentteplo_id)
-> GroupAggregate (cost=0.00..3.36 rows=2 width=12) (actual time=0.037..0.037 rows=0 loops=57795)
-> Index Scan using lgotteplo1 on lgotteplo b (cost=0.00..3.35 rows=2 width=12) (actual time=0.021..0.021 rows=0 loops=57795)
Index Cond: ($1 = contragentteplo_id)
-> GroupAggregate (cost=0.00..3.36 rows=2 width=12) (actual time=0.038..0.038 rows=0 loops=57795)
-> Index Scan using lgotteplo1 on lgotteplo b (cost=0.00..3.35 rows=2 width=12) (actual time=0.024..0.024 rows=0 loops=57795)
Index Cond: ($1 = contragentteplo_id)
-> GroupAggregate (cost=0.00..3.06 rows=2 width=15) (actual time=0.090..0.094 rows=1 loops=57795)
-> Index Scan using prodagateplo1 on prodagateplo b (cost=0.00..3.04 rows=3 width=15) (actual time=0.039..0.051 rows=1 loops=57795)
Index Cond: ($1 = contragentteplo_id)
-> Index Scan using period_pk on period d (cost=0.00..3.40 rows=1 width=10) (actual time=0.028..0.060 rows=1 loops=59190)
Index Cond: (period_id = $0)
-> Index Scan using period_pk on period d (cost=0.00..3.40 rows=1 width=10) (actual time=0.042..0.050 rows=1 loops=59190)
Index Cond: (period_id = $0)
-> Sort (cost=53560.47..53613.68 rows=21284 width=134) (actual time=37676.112..38069.706 rows=30086 loops=1)
Sort Key: to_number((a2.dogovor)::text, '99999'::text)
-> Hash Join (cost=29.65..51059.64 rows=21284 width=134) (actual time=52.863..34479.915 rows=30086 loops=1)
Hash Cond: ("outer".period_id = "inner".period_id)
-> Seq Scan on contragentteplo a2 (cost=0.00..41268.43 rows=1909743 width=124) (actual time=13.772..20163.235 rows=1908980 loops=1)
-> Hash (cost=29.62..29.62 rows=11 width=18) (actual time=38.945..38.945 rows=0 loops=1)
-> Hash Join (cost=24.83..29.62 rows=11 width=18) (actual time=38.732..38.844 rows=19 loops=1)
Hash Cond: ("outer".predpr_id = "inner".predpr_id)
-> Seq Scan on predpr d (cost=0.00..2.67 rows=67 width=18) (actual time=30.283..31.063 rows=67 loops=1)
-> Hash (cost=24.80..24.80 rows=11 width=8) (actual time=7.368..7.368 rows=0 loops=1)
-> Seq Scan on period b (cost=0.00..24.80 rows=11 width=8) (actual time=1.649..7.262 rows=19 loops=1)
Filter: ((god = 2005::numeric) AND (mesyz = 6::numeric))
-> Hash (cost=71.49..71.49 rows=3849 width=23) (actual time=78.659..78.659 rows=0 loops=1)
-> Seq Scan on dom e (cost=0.00..71.49 rows=3849 width=23) (actual time=11.847..51.556 rows=3849 loops=1)
-> Hash (cost=29.30..29.30 rows=491 width=68) (actual time=63.794..63.794 rows=0 loops=1)
-> Hash Join (cost=9.03..29.30 rows=491 width=68) (actual time=36.773..61.716 rows=491 loops=1)
Hash Cond: ("outer".tipulicy_id = "inner".tipulicy_id)
-> Seq Scan on ulicy f (cost=0.00..12.91 rows=491 width=53) (actual time=6.228..21.160 rows=491 loops=1)
-> Hash (cost=8.02..8.02 rows=402 width=23) (actual time=30.465..30.465 rows=0 loops=1)
-> Seq Scan on tipulicy g (cost=0.00..8.02 rows=402 width=23) (actual time=17.148..28.626 rows=402 loops=1)
-> Hash (cost=84.42..84.42 rows=4842 width=17) (actual time=91.414..91.414 rows=0 loops=1)
-> Seq Scan on kvartira c (cost=0.00..84.42 rows=4842 width=17) (actual time=10.614..68.112 rows=4842 loops=1)
Total runtime: 274494.479 ms

Подскажите почему
1 он упорно делает
Hash Cond: ("outer".period_id = "inner".period_id)
-> Seq Scan on contragentteplo a2 (cost=0.00..41268.43 rows=1909743 width=124) (actual time=13.772..20163.235 rows=1908980 loops=1)

Индекс на period_id в contragentteplo есть

2 можно ли добиться не сканирование подзапроса

Merge Cond: ("outer".dogovor = "inner"."?column11?")
-> Subquery Scan q12 (cost=95630.47..873691.13 rows=15317 width=96) (actual time=20462.997..234919.035 rows=1395 loops=1)


а поиск по индексу (хотя как его в подзапросе использовать ???)

Спасибо
Горю