pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeremy Haile <jhaile@fastmail.fm>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow inner join, but left join is fast
Date: Wed, 10 Jan 2007 14:22:35 -0500
Message-ID: <15190.1168456955@sss.pgh.pa.us> (raw)
In-Reply-To: <1168454677.25902.1168599301@webmail.messagingengine.com>
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>

"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

view thread (8+ messages)  latest in thread

Message-ID: <15190.1168456955@sss.pgh.pa.us>
Permalink:  ../15190.1168456955@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/15190.1168456955@sss.pgh.pa.us

copy link · copy postgr.es

reply

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: tgl@sss.pgh.pa.us, jhaile@fastmail.fm
  Subject: Re: Slow inner join, but left join is fast
  In-Reply-To: <15190.1168456955@sss.pgh.pa.us>

* 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