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.
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