From: Ceri Storey <cez@necrofish.org.uk>
To: pgsql-performance@postgresql.org
Subject: Join optimisation Quandry
Date: Wed, 14 Jan 2004 23:10:15 +0000
Message-ID: <20040114231014.GE53284@mrtall.compsoc.man.ac.uk> (raw)
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 <cez@necrofish.org.uk>
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: cez@necrofish.org.uk
Subject: Re: Join optimisation Quandry
In-Reply-To: <20040114231014.GE53284@mrtall.compsoc.man.ac.uk>
* 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