Re: same query different execution plan (hash join vs. semi-hash join)

Thanks Tom, I think you're right. I just did an analyze on table test1 and the execution plan now generated is more stable and predictable.

Thanks,
Suya

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Tuesday, May 20, 2014 12:22 AM
To: Huang, Suya
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] same query different execution plan (hash join vs. semi-hash join)

"Huang, Suya" <Suya(dot)Huang(at)au(dot)experian(dot)com> writes:
> Thank you Tom. But the time spent on scanning table test1 is less than 1 second (91.738 compares to 87.869), so I guess this shouldn't be the issue?

No, the point is that the bad rowcount estimate (and, possibly, lack of stats about join column contents) causes the planner to pick a join method that's not ideal for this query.

regards, tom lane