big distinct clause vs. group by

Hi,

I'm having trouble with some sql statements which use an expression with
many columns and distinct in the column list of the select.
select distinct col1,col2,.....col20,col21
from table1 left join table2 on <join condition>,...
where
<other expressions>;

The negative result is a big sort with teporary files.
-> Sort (cost=5813649.93..5853067.63 rows=15767078 width=80)
(actual time=79027.079..81556.059 rows=12076838 loops=1)
Sort Method: external sort Disk: 1086096kB
By the way - for this query I have a work_mem of 1 GB - so raising this
further is not generally possible - also not for one special command, due to
parallelism.

How do I get around this?
I have one idea and like to know if there any other approaches or an even
known better solution to that problem. By using group by I don't need the
big sort for the distinct - I reduce it (theoreticly) to the key columns.

select <list of key columns>,<non key column>
from tables1left join table2 on <join condition>,...
where
<other conditions>
group by <list of key columns>

Another question would be what's the aggregate function which needs as less
as possible resources (time).
Below is a list of sql statements which shows a reduced sample of the sql
and one of the originating sqls.

Any hints are welcome. They may safe hours
Best Regards,
Uwe

create table a(a_id int,a_a1 int, a_a2 int, a_a3 int, a_a4 int, a_a5 int,
a_a6 int, a_a7 int, a_a8 int, a_a9 int, a_a10 int, primary key (a_id));
create table b(b_id int,b_a1 int, b_a2 int, b_a3 int, b_a4 int, b_a5 int,
b_a6 int, b_a7 int, b_a8 int, b_a9 int, b_a10 int, primary key (b_id));
insert into a select
generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000);
insert into b select
generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000);

-- current state
------------------------------
postgres=# explain analyze verbose select distinct
a_id,b_id,coalesce(a_a1,0), a_a2, a_a3, a_a4, a_a5, a_a6, a_a7, a_a8 , a_a9,
a_a10,b_a1, b_a2, b_a3, b_a4, b_a5, b_a6, b_a7, b_a8 , b_a9, b_a10 from a
left join b on a_id=b_id;

QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------
HashAggregate (cost=128694.42..138694.64 rows=1000022 width=88) (actual
time=3127.884..3647.814 rows=1000000 loops=1)
Output: a.a_id, b.b_id, (COALESCE(a.a_a1, 0)), a.a_a2, a.a_a3, a.a_a4,
a.a_a5, a.a_a6, a.a_a7, a.a_a8, a.a_a9, a.a_a10, b.b_a1, b.b_a2, b.b_a3,
b.b_a4, b.b
_a5, b.b_a6, b.b_a7, b.b_a8, b.b_a9, b.b_a10
-> Hash Left Join (cost=31846.50..73693.21 rows=1000022 width=88)
(actual time=361.938..2010.894 rows=1000000 loops=1)
Output: a.a_id, b.b_id, COALESCE(a.a_a1, 0), a.a_a2, a.a_a3,
a.a_a4, a.a_a5, a.a_a6, a.a_a7, a.a_a8, a.a_a9, a.a_a10, b.b_a1, b.b_a2,
b.b_a3, b.b_a4,
b.b_a5, b.b_a6, b.b_a7, b.b_a8, b.b_a9, b.b_a10
Hash Cond: (a.a_id = b.b_id)
-> Seq Scan on a (cost=0.00..19346.22 rows=1000022 width=44)
(actual time=0.014..118.918 rows=1000000 loops=1)
Output: a.a_id, a.a_a1, a.a_a2, a.a_a3, a.a_a4, a.a_a5,
a.a_a6, a.a_a7, a.a_a8, a.a_a9, a.a_a10
-> Hash (cost=19346.22..19346.22 rows=1000022 width=44) (actual
time=361.331..361.331 rows=1000000 loops=1)
Output: b.b_id, b.b_a1, b.b_a2, b.b_a3, b.b_a4, b.b_a5,
b.b_a6, b.b_a7, b.b_a8, b.b_a9, b.b_a10
-> Seq Scan on b (cost=0.00..19346.22 rows=1000022
width=44) (actual time=0.008..119.711 rows=1000000 loops=1)
Output: b.b_id, b.b_a1, b.b_a2, b.b_a3, b.b_a4, b.b_a5,
b.b_a6, b.b_a7, b.b_a8, b.b_a9, b.b_a10
Total runtime: 3695.845 ms

-- possible future state
------------------------------

postgres=# explain analyze verbose select a_id,b_id, min(coalesce(a_a1,0)),
min( a_a2), min( a_a3), min( a_a4), min( a_a5), min( a_a6), min( a_a7), min(
a_a8 ), min( a_a9), min( a_a10), min(b_a1), min( b_a2), min( b_a3), min(
b_a4), min( b_a5), min( b_a6), min( b_a7), min( b_a8 ), min( b_a9), min(
b_a10) from a left join b on a_id=b_id group by a_id,b_id;

QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=128694.42..188695.74 rows=1000022 width=88) (actual
time=3057.096..3884.902 rows=1000000 loops=1)
Output: a.a_id, b.b_id, min(COALESCE(a.a_a1, 0)), min(a.a_a2),
min(a.a_a3), min(a.a_a4), min(a.a_a5), min(a.a_a6), min(a.a_a7),
min(a.a_a8), min(a.a_a9), m
in(a.a_a10), min(b.b_a1), min(b.b_a2), min(b.b_a3), min(b.b_a4),
min(b.b_a5), min(b.b_a6), min(b.b_a7), min(b.b_a8), min(b.b_a9),
min(b.b_a10)
-> Hash Left Join (cost=31846.50..73693.21 rows=1000022 width=88)
(actual time=362.611..1809.991 rows=1000000 loops=1)
Output: a.a_id, a.a_a1, a.a_a2, a.a_a3, a.a_a4, a.a_a5, a.a_a6,
a.a_a7, a.a_a8, a.a_a9, a.a_a10, b.b_id, b.b_a1, b.b_a2, b.b_a3, b.b_a4,
b.b_a5, b.b_
a6, b.b_a7, b.b_a8, b.b_a9, b.b_a10
Hash Cond: (a.a_id = b.b_id)
-> Seq Scan on a (cost=0.00..19346.22 rows=1000022 width=44)
(actual time=0.014..119.920 rows=1000000 loops=1)
Output: a.a_id, a.a_a1, a.a_a2, a.a_a3, a.a_a4, a.a_a5,
a.a_a6, a.a_a7, a.a_a8, a.a_a9, a.a_a10
-> Hash (cost=19346.22..19346.22 rows=1000022 width=44) (actual
time=362.002..362.002 rows=1000000 loops=1)
Output: b.b_id, b.b_a1, b.b_a2, b.b_a3, b.b_a4, b.b_a5,
b.b_a6, b.b_a7, b.b_a8, b.b_a9, b.b_a10
-> Seq Scan on b (cost=0.00..19346.22 rows=1000022
width=44) (actual time=0.010..121.665 rows=1000000 loops=1)
Output: b.b_id, b.b_a1, b.b_a2, b.b_a3, b.b_a4, b.b_a5,
b.b_a6, b.b_a7, b.b_a8, b.b_a9, b.b_a10
Total runtime: 3934.146 ms

One of my originating sql statements is:
select distinct
l.link_id, ra.bridge, ra.tunnel, ra.urban, ra.long_haul, ra.stub,
COALESCE(rac.admin_class, 0), ra.functional_class,
ra.speed_category,ra.travel_direction,
ra.paved, ra.private, ra.tollway, ra.boat_ferry, ra.rail_ferry,
ra.multi_digitized, ra.in_process_data, ra.automobiles, ra.buses, ra.taxis,
ra.carpools, ra.pedestrians, ra.trucks, ra.through_traffic, ra.deliveries,
ra.emergency_vehicles,
ra.ramp,ra.roundabout,ra.square, ra.parking_lot_road, ra.controlled_access,
ra.frontage,
CASE WHEN COALESCE(cl.INTERSECTION_ID,0) = 0 THEN 'N' ELSE 'Y' END,
ra.TRANSPORT_VERIFIED, ra.PLURAL_JUNCTION, ra.vignette, ra.SCENIC_ROUTE,
ra.four_wheel_drive
from nds.link l
join nndb.link_road_attributes lra on l.nndb_id = lra.feature_id
join nndb.road_attributes ra on lra.road_attr_id = ra.road_attr_id
left join nds.road_admin_class rac on rac.nndb_feature_id = l.nndb_id
left join nndb.complex_intersection_link cl on l.nndb_id = cl.link_id