Re: Hash join in 8.3 - Mailing list pgsql-general

From Gregory Stark
Subject Re: Hash join in 8.3
Date
Msg-id 87odcuo1dn.fsf@oxford.xeocode.com
Whole thread Raw
In response to Hash join in 8.3  (André Volpato<andre.volpato@ecomtecnologia.com.br>)
Responses Re: Hash join in 8.3  (André Volpato<andre.volpato@ecomtecnologia.com.br>)
Re: Hash join in 8.3  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
André Volpato <andre.volpato@ecomtecnologia.com.br> writes:

> And the query:
>
> # select j.i, t.t from jtest j inner join test t on t.i = j.i where (j.i*1.5)
> between 3000000 and 4000000;
>
> Planner for [1]:
> Nested Loop  (cost=0.00..270192.02 rows=20000 width=41) (actual

> Planner for [2]:
> Hash Join  (cost=176924.02..297518.03 rows=20000 width=38) (actual

> Now, turning off hashing:
> # set enable_hashjoin=off;
> # set enable_hashagg=off;
>
> Again for [2]:
> Merge Join  (cost=178781.75..328370.60 rows=20000 width=38) (actual

I think the answer is that if you have bad statistics you'll get a bad plan
and which bad plan is going to be pretty much random.

But I'm curious if you turn off mergejoin whether you can get a Nested Loop
plan and what cost 8.3 gives it. It looks to me like 8.3 came up with a higher
cost for Nested Loop than 8.1.9 (I think 8.1.10 came out with some planner
fixes btw) and so it's deciding these other plans are better. And they might
have been better for the imaginary scenario that the planner thinks is going
on.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's Slony Replication support!

pgsql-general by date:

Previous
From: "Joshua D. Drake"
Date:
Subject: Re: Slow PITR restore
Next
From: André Volpato
Date:
Subject: Re: Hash join in 8.3