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 81F58D1B4A4 for ; Fri, 16 Jan 2004 18:18:21 +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 56502-07 for ; Fri, 16 Jan 2004 14:17:51 -0400 (AST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id C07ABD1B4AE for ; Fri, 16 Jan 2004 14:17:50 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 6102235298; Fri, 16 Jan 2004 10:17:50 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 5F7DA35297; Fri, 16 Jan 2004 10:17:50 -0800 (PST) Date: Fri, 16 Jan 2004 10:17:50 -0800 (PST) From: Stephan Szabo To: Ceri Storey Cc: pgsql-performance@postgresql.org Subject: Re: Join optimisation Quandry In-Reply-To: <20040114231014.GE53284@mrtall.compsoc.man.ac.uk> Message-ID: <20040116094935.P66040@megazone.bigpanda.com> References: <20040114231014.GE53284@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/178 X-Sequence-Number: 5352 On Wed, 14 Jan 2004, Ceri Storey wrote: > Hi there. > > I've got a database (the schema is: > http://compsoc.man.ac.uk/~cez/2004/01/14/tv-schema.sql) for television > data. Now, one of the things I want to use this for is a now and next > display. (much like http://teletext.com/tvplus/nownext.asp ). > > I've got a view defined like this: > CREATE VIEW progtit AS SELECT programme.*, title_seen, title_wanted, > title_text FROM (programme NATURAL JOIN title); > > And to select the programmes that are on currently and next, I'm doing > something like this: > > SELECT * > FROM progtit AS p1 LEFT JOIN progtit AS p2 ON p1.prog_next = p2.prog_id > WHERE prog_start <= '2004-01-14 23:09:11' > AND prog_stop > '2004-01-14 23:09:11'; > > Now, unfourtunately this runs rather slowly (takes around 1sec to > complete on my machine), as it (AFAIK) ends up building a complete > instance of the progtit view and then joining the current programmes > with that, instead of just finding the current set of programs and then > selecting the relevant rows from the view. > > Now, I know I could just two it in two seperate passes for the current > programmes and those after them, but I'd be neater to do it in one. > > So, is there any way to optimize the above query? Any clues, or > references would be wonderful. As a starting point, we're likely to need the exact query, explain analyze output for the query and version information.