Re: bad performances using hashjoin

David Brown <time(at)bigpond(dot)net(dot)au> writes:
> The planner is not breaking up the outer join in his v_packages view:

The planner doesn't make any attempt to rearrange join order of outer
joins. There are some cases where such a rearrangement is OK, but there
are other cases where it isn't, and we don't currently have the logic
needed to tell which is which.

In the particular case at hand here, 8.0's hack to suppress evaluating
the outer side of a hash join after finding the inner side is empty
would eliminate the complaint.

In the original message, it did seem that the packages-to-
package_security join is taking a lot longer than one would expect:

-> Hash Left Join (cost=15.54..86.42 rows=1097 width=162) (actual time=2.978..6087.608 rows=1104 loops=1)
Hash Cond: ("outer".id_package = "inner".id_package)
-> Seq Scan on packages p (cost=0.00..53.48 rows=1097 width=146) (actual time=0.011..7.978 rows=1104 loops=1)
-> Hash (cost=13.69..13.69 rows=738 width=20) (actual time=2.061..2.061 rows=0 loops=1)
-> Seq Scan on package_security ps (cost=0.00..13.69 rows=738 width=20) (actual time=0.027..1.289 rows=747 loops=1)

but this behavior isn't reproduced in the later message, so I wonder if
it wasn't an artifact of something else taking a chunk of time.

regards, tom lane