Skip site navigation (1) Skip section navigation (2)

Re: Join optimisation Quandry

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Ceri Storey <cez(at)necrofish(dot)org(dot)uk>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Join optimisation Quandry
Date: 2004-01-16 18:17:50
Message-ID: (view raw or whole thread)
Lists: pgsql-performance
On Wed, 14 Jan 2004, Ceri Storey wrote:

> Hi there.
> I've got a database (the schema is:
> for television
> data. Now, one of the things I want to use this for is a now and next
> display. (much like ).
> 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:
> 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.

In response to


pgsql-performance by date

Next:From: Tom LaneDate: 2004-01-16 19:26:18
Subject: Re: Question about space usage:
Previous:From: Stephan SzaboDate: 2004-01-16 17:48:18
Subject: Re: subquery and table join, index not use for table

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group