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 354B6D1B4CE for ; Wed, 14 Jan 2004 23:11:05 +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 45454-07 for ; Wed, 14 Jan 2004 19:10:36 -0400 (AST) Received: from serenity.mcc.ac.uk (serenity.mcc.ac.uk [130.88.200.93]) by svr1.postgresql.org (Postfix) with ESMTP id CBF65D1B49B for ; Wed, 14 Jan 2004 19:10:31 -0400 (AST) Received: from compsoc.umu.man.ac.uk ([130.88.22.5] helo=mrtall.compsoc.man.ac.uk ident=exim) by serenity.mcc.ac.uk with esmtp (Exim 4.20) id 1Agu9P-000GC3-FL for pgsql-performance@postgresql.org; Wed, 14 Jan 2004 23:10:15 +0000 Received: from cez by mrtall.compsoc.man.ac.uk with local (Exim 3.33 #1) id 1Agu9P-000I3R-00 for pgsql-performance@postgresql.org; Wed, 14 Jan 2004 23:10:15 +0000 Date: Wed, 14 Jan 2004 23:10:15 +0000 From: Ceri Storey To: pgsql-performance@postgresql.org Subject: Join optimisation Quandry Message-ID: <20040114231014.GE53284@mrtall.compsoc.man.ac.uk> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.4i X-Scanner: exiscan for exim4 (http://duncanthrax.net/exiscan/) *1Agu9P-000GC3-FL*LIryK6lKCrA* X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/173 X-Sequence-Number: 5347 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. Thanks. -- Ceri Storey