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-17 06:05:54
Message-ID: 20040116220146.N81715@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-performance
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?

In response to

Responses

pgsql-performance by date

Next:From: Ceri StoreyDate: 2004-01-17 10:40:49
Subject: Re: Join optimisation Quandry
Previous:From: Mark KirkwoodDate: 2004-01-17 01:51:26
Subject: Re: Potential Problem with PostgeSQL performance on SuSE

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