pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Ceri Storey <cez@necrofish.org.uk>
Cc: pgsql-performance@postgresql.org
Subject: Re: Join optimisation Quandry
Date: Fri, 16 Jan 2004 10:17:50 -0800 (PST)
Message-ID: <20040116094935.P66040@megazone.bigpanda.com> (raw)
In-Reply-To: <20040114231014.GE53284@mrtall.compsoc.man.ac.uk>
References: <20040114231014.GE53284@mrtall.compsoc.man.ac.uk>


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.



view thread (7+ messages)  latest in thread

Message-ID: <20040116094935.P66040@megazone.bigpanda.com>
Permalink:  ../20040116094935.P66040@megazone.bigpanda.com/
Also on:    postgresql.org/message-id/20040116094935.P66040@megazone.bigpanda.com

 · 

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: sszabo@megazone.bigpanda.com, cez@necrofish.org.uk
  Subject: Re: Join optimisation Quandry
  In-Reply-To: <20040116094935.P66040@megazone.bigpanda.com>

* 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