X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 5014ED1B4AA for ; Sat, 17 Jan 2004 06:06:26 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 79851-10 for ; Sat, 17 Jan 2004 02:05:55 -0400 (AST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 7CDA3D1B4D5 for ; Sat, 17 Jan 2004 02:05:54 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 5345935356; Fri, 16 Jan 2004 22:05:54 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 51D5C35352; Fri, 16 Jan 2004 22:05:54 -0800 (PST) Date: Fri, 16 Jan 2004 22:05:54 -0800 (PST) From: Stephan Szabo To: Ceri Storey Cc: pgsql-performance@postgresql.org Subject: Re: Join optimisation Quandry In-Reply-To: <20040117010334.GA43530@mrtall.compsoc.man.ac.uk> Message-ID: <20040116220146.N81715@megazone.bigpanda.com> References: <20040114231014.GE53284@mrtall.compsoc.man.ac.uk> <20040116094935.P66040@megazone.bigpanda.com> <20040117010334.GA43530@mrtall.compsoc.man.ac.uk> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/184 X-Sequence-Number: 5358 On Sat, 17 Jan 2004, Ceri Storey wrote: > On Fri, Jan 16, 2004 at 10:17:50AM -0800, Stephan Szabo wrote: > > As a starting point, we're likely to need the exact query, explain analyze > > output for the query and version information. > > Okay, from top to bottom: > > SELECT p1.chan_name, p1.prog_start AS now_start, p1.prog_id, p1.title_text, > p2.prog_start AS next_start, p2.prog_id, p2.title_text, > p1.title_wanted, p2.title_wanted, p1.chan_id > FROM (programme natural join channel NATURAL JOIN title) AS p1 > LEFT OUTER JOIN (programme NATURAL JOIN title) AS p2 > ON p1.prog_next = p2.prog_id > WHERE p1.prog_start <= timestamp 'now' AND p1.prog_stop > timestamp 'now' > ORDER BY p1.chan_id ASC > Well the plan would seems reasonable to me if there really was only 1 row coming from the where conditions on p1. As a first step, if you raise the statistics target (see ALTER TABLE) for prog_start and prog_stop and re-analyze the table, do you get a better estimate of the rows from that condition? Secondly, what do you get if you enable_nestloop=off before explain analyzing the query?