From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Huang, Suya" <Suya.Huang@au.experian.com>
cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: same query different execution plan (hash join vs. semi-hash
 join)
In-reply-to: 
 <D83E55F5F4D99B4A9B4C4E259E6227CD01498C88@AUX1EXC01.apac.experian.local>
References: 
 <D83E55F5F4D99B4A9B4C4E259E6227CD01498C88@AUX1EXC01.apac.experian.local>
Comments: In-reply-to "Huang, Suya" <Suya.Huang@au.experian.com>
 message dated "Fri, 16 May 2014 02:38:22 -0000"
Date: Thu, 15 May 2014 22:58:02 -0400
Message-ID: <21694.1400209082@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

"Huang, Suya" <Suya.Huang@au.experian.com> writes:
> I've got a query as below, it runs several times with different execution plan and totally different execution time. The one using hash-join is slow and the one using semi-hash join is very fast. However, I have no control over the optimizer behavior of PostgreSQL database. Or, do I have?

A salient feature of the slow plan is that the planner is misinformed
about the size of test1:

>                      ->  Seq Scan on test1  (cost=0.00..5153.94 rows=63294 width=516) (actual time=0.068..91.378 rows=441736 loops=1)

whereas in the fast plan its rows estimate for that scan is dead on.
It looks like the two cases also have different ideas of how many
distinct values are in the test1.userid column, though this is more a
guess than an indisputable fact.

In short, I suspect you're recreating the test1 table and not bothering
to ANALYZE it after you fill it.  This leaves you at the mercy of when
the autovacuum daemon gets around to analyzing the table before you'll
get good plans for it.

			regards, tom lane


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance