Re: problem with huge joins

Kolus Maximiliano <Kolus.maximiliano@bcr.com.ar> writes:
>     In order to get this, I run this query:

> SELECT ip, TO_CHAR(date, 'YYYY-MM-DD'), protocol, port
> FROM tProxyPort, tProxyList, tProxyHost
> WHERE tProxyPort.listId=tProxyList.listId
>     AND tProxyList.output=tProxyHost.hostId
> ORDER BY ip, port

>     Whose query plan is:

> Sort  (cost=311874.07..311874.07 rows=986130 width=44) (actual
> time=300086.42..302580.25 rows=986130 loops=1)
>   ->  Hash Join  (cost=39735.96..96907.83 rows=986130 width=44) (actual
> time=86226.28..223195.50 rows=986130 loops=1)
>         ->  Seq Scan on tport  (cost=0.00..18629.30 rows=986130 width=12)
> (actual time=0.15..25910.56 rows=986130 loops=1)
>         ->  Hash  (cost=35972.38..35972.38 rows=403034 width=32) (actual
> time=86194.99..86194.99 rows=0 loops=1)
>               ->  Hash Join  (cost=9787.92..35972.38 rows=403034 width=32)
> (actual time=12180.64..84316.65 rows=403927 loops=1)
>                     ->  Seq Scan on thost  (cost=0.00..7850.41 rows=457341
> width=16) (actual time=619.09..10032.85 rows=458787 loops=1)
>                     ->  Hash  (cost=6812.34..6812.34 rows=403034 width=16)
> (actual time=6656.36..6656.36 rows=0 loops=1)
>                           ->  Seq Scan on tlist  (cost=0.00..6812.34
> rows=403034 width=16) (actual time=6.90..5030.22 rows=403927 loops=1)
> Total runtime: 317046.69 msec

The joins and sort steps seem to take rather a long time.  What do you
have sort_mem set to?  You probably want it on the order of 10Mb so that
these joins are done in memory rather than spilling to disk.

The hash indexes are a waste of time for this :-(

            regards, tom lane