## Re: 7.4 vs 7.3 ( hash join issue )

From: Gaetano Mendola Dennis Bjorklund Re: 7.4 vs 7.3 ( hash join issue ) 2004-09-22 10:28:04 415153B4.5040902@bigfoot.com (view raw or whole thread) 2004-09-21 22:52:06 from Gaetano Mendola  2004-09-22 06:48:09 from Dennis Bjorklund   2004-09-22 08:22:05 from Gaetano Mendola    2004-09-22 09:32:19 from Dennis Bjorklund     2004-09-22 10:28:04 from Gaetano Mendola    2004-09-22 13:43:24 from Greg Stark     2004-09-22 15:22:42 from Dennis Bjorklund      2004-09-22 17:38:00 from Greg Stark       2004-09-22 17:56:13 from Greg Stark       2004-09-22 17:56:34 from Tom Lane        2004-09-22 18:46:12 from Greg Stark         2004-09-22 19:00:35 from Tom Lane        2004-09-23 14:04:20 from Gaetano Mendola pgsql-patchespgsql-performance
```Dennis Bjorklund wrote:
> On Wed, 22 Sep 2004, Gaetano Mendola wrote:
>
>
>>  Limit  (cost=10.21..10.21 rows=1 width=24) (actual time=0.885..0.885 rows=0 loops=1)
>>  Limit  (cost=10.28..10.28 rows=1 width=24) (actual time=0.429..0.429 rows=0 loops=1)
>
>
> These estimated costs are almost the same, but the runtime differs a bit.
> This means that maybe you need to alter settings like random_page_cost,
> effective_cache and maybe some others to make the cost reflect the runtime
> better.
>
> Since the costs are so close to each other very small changes can make it
> choose the other plan. It's also very hard to make an estimate that is
> correct in all situations. That's why it's called an estimate after all.

Is not feseable.

That values are obtained with random_page_cost = 2, effective_cache_size = 20000,
cpu_tuple_cost = 0.01
increasing or decreasing random_page_cost this means increase or decrease both
costs:

random_page_cost = 1.5
hashjoin on  =>  8.47
hashjoin off =>  8.53

random_page_cost = 3
hashjoin on  =>  13.70
hashjoin off =>  13.76

so is choosen the hasjoin method in both cases.

In the other side the effective_cache_size doesn't affect this costs.

Decreasing the cpu_tuple_cost have the same effect

cpu_tuple_cost = 0.005
hashjoin on  =>  10.11
hashjoin off =>  10.17

cpu_tuple_cost = 0.001
hashjoin on  =>  10.03
hashjoin off =>  10.03

cpu_tuple_cost = 0.0005
hashjoin on  =>  10.01
hashjoin off =>  10.01

And when the two costs are the same the hashjoin path is choosen.

I think cpu_tuple_cost less then 0.001 is not a good idea

I think the only way is set the hashjoin = off.  Any other suggestion ?

Regards
Gaetano Mendola

```

### pgsql-performance by date

 Next: From: Greg Stark Date: 2004-09-22 13:43:24 Subject: Re: 7.4 vs 7.3 ( hash join issue ) Previous: From: Dennis Bjorklund Date: 2004-09-22 09:32:19 Subject: Re: 7.4 vs 7.3 ( hash join issue )

### pgsql-patches by date

 Next: From: Alvaro Herrera Date: 2004-09-22 13:00:00 Subject: Re: WIP: CREATE TABLE AS / WITH DATA Previous: From: Dennis Bjorklund Date: 2004-09-22 09:32:19 Subject: Re: 7.4 vs 7.3 ( hash join issue )