From: Jeremy Haile <jhaile@fastmail.fm>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow inner join, but left join is fast
Date: Wed, 10 Jan 2007 14:26:55 -0500
Message-ID: <1168457215.31642.1168606495@webmail.messagingengine.com> (raw)
In-Reply-To: <15190.1168456955@sss.pgh.pa.us>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jhaile@fastmail.fm, tgl@sss.pgh.pa.us
Subject: Re: Slow inner join, but left join is fast
In-Reply-To: <1168457215.31642.1168606495@webmail.messagingengine.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox