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: 20040116094935.P66040@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.

In response to

Responses

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-2014 The PostgreSQL Global Development Group