Re: problem with huge joins

Kolus Maximiliano <Kolus(dot)maximiliano(at)bcr(dot)com(dot)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