Message-Id: <1168457215.31642.1168606495@webmail.messagingengine.com>
From: "Jeremy Haile" <jhaile@fastmail.fm>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Content-Disposition: inline
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="ISO-8859-1"
MIME-Version: 1.0
References: <1168445842.5566.1168570133@webmail.messagingengine.com>
	<10060.1168449344@sss.pgh.pa.us>
	<1168453218.22654.1168592237@webmail.messagingengine.com>
	<14865.1168454295@sss.pgh.pa.us>
	<1168454304.25047.1168597535@webmail.messagingengine.com>
	<1168454677.25902.1168599301@webmail.messagingengine.com>
	<15190.1168456955@sss.pgh.pa.us>
Subject: Re: Slow inner join, but left join is fast
In-Reply-To: <15190.1168456955@sss.pgh.pa.us>
Date: Wed, 10 Jan 2007 14:26:55 -0500

I did create and drop an index at some point while looking at this
issue.  But I definitely reran both of the queries (and explains) after
the index was dropped, so I don't understand why there would be a
difference between the inner and left query plans.  (which were run
back-to-back more than once)  Anyways - I'll let you know if something
similar happens again.

Thanks,
Jeremy Haile 


On Wed, 10 Jan 2007 14:22:35 -0500, "Tom Lane" <tgl@sss.pgh.pa.us> said:
> "Jeremy Haile" <jhaile@fastmail.fm> writes:
> > Another random idea - does PostgreSQL do any caching of query plans?
> 
> Only if the client specifies it, either by PREPARE or the equivalent
> protocol-level message.  I dunno what client software you were using,
> but I think few if any would PREPARE behind your back.  Might be worth
> checking into though, if you've eliminated autovacuum.
> 
> Actually there's another possibility --- did you create any indexes on
> the table in between?  CREATE INDEX doesn't do a full stats update, but
> it does count the rows and update pg_class.reltuples.  But it's hard to
> believe that'd have caused as big a rowcount shift as we see here ...
> 
> 			regards, tom lane