Re: Join optimisation Quandry

On Sat, 17 Jan 2004, Ceri Storey wrote:

> On Fri, Jan 16, 2004 at 10:17:50AM -0800, Stephan Szabo wrote:
> > As a starting point, we're likely to need the exact query, explain analyze
> > output for the query and version information.
>
> Okay, from top to bottom:
>
> SELECT p1.chan_name, p1.prog_start AS now_start, p1.prog_id, p1.title_text,
> p2.prog_start AS next_start, p2.prog_id, p2.title_text,
> p1.title_wanted, p2.title_wanted, p1.chan_id
> FROM (programme natural join channel NATURAL JOIN title) AS p1
> LEFT OUTER JOIN (programme NATURAL JOIN title) AS p2
> ON p1.prog_next = p2.prog_id
> WHERE p1.prog_start <= timestamp 'now' AND p1.prog_stop > timestamp 'now'
> ORDER BY p1.chan_id ASC
>

Well the plan would seems reasonable to me if there really was only 1 row
coming from the where conditions on p1. As a first step, if you raise the
statistics target (see ALTER TABLE) for prog_start and prog_stop and
re-analyze the table, do you get a better estimate of the rows from that
condition? Secondly, what do you get if you enable_nestloop=off before
explain analyzing the query?