Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 2E7199FB264 for ; Wed, 10 Jan 2007 15:22:49 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 50313-01 for ; Wed, 10 Jan 2007 15:22:30 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 0FA2F9FB26A for ; Wed, 10 Jan 2007 15:22:37 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l0AJMZcr015191; Wed, 10 Jan 2007 14:22:35 -0500 (EST) To: "Jeremy Haile" cc: pgsql-performance@postgresql.org Subject: Re: Slow inner join, but left join is fast 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> Comments: In-reply-to "Jeremy Haile" message dated "Wed, 10 Jan 2007 13:44:37 -0500" Date: Wed, 10 Jan 2007 14:22:35 -0500 Message-ID: <15190.1168456955@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/138 X-Sequence-Number: 22739 "Jeremy Haile" 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