DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=myfNQO1if0gNHB/yFKyumvhpuwC3saFdoWS4W8URcXVMigJnbHvJ7+M7NkwDVQEKenDxLjqb9ch880G1m4PVBq7/P7UIlcrlruFaPwX1/qETY1jUyQd4YAP4igxAarSwXOnh/d0USkk1wNRlL1MlX+vzYjf7u1wdh5TmB+m+X70=
Message-ID: <dcc563d10802111337x2e19d2e4n74979077125079f1@mail.gmail.com>
Date: Mon, 11 Feb 2008 15:37:34 -0600
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "Thomas Zaksek" <zaksek@ptt.uni-due.de>
Subject: Re: Join Query Perfomance Issue
Cc: pgsql-performance@postgresql.org
In-Reply-To: <47B08F19.8000500@ptt.uni-due.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <47B08F19.8000500@ptt.uni-due.de>

On Feb 11, 2008 12:08 PM, Thomas Zaksek <zaksek@ptt.uni-due.de> wrote:
> I have serious performance problems with the following type of queries:
> /
> /explain analyse SELECT '12.11.2007 18:04:00 UTC' AS zeit,
>                        'M' AS datatyp,
>                        p.zs_nr AS zs_de,
>                    j_ges,
>                        de_mw_abh_j_lkw(mw_abh) AS j_lkw,
>                        de_mw_abh_v_pkw(mw_abh) AS v_pkw,
>                        de_mw_abh_v_lkw(mw_abh) AS v_lkw,
>                    de_mw_abh_p_bel(mw_abh) AS p_bel
>                    FROM  messpunkt p, messungen_v_dat_2007_11_12 m, de_mw w
>                        WHERE  m.ganglinientyp = 'M'
>                AND 381 = m.minute_tag
>                        AND (p.nr, p.mw_nr) = (m.messpunkt, w.nr);
>
> Explain analze returns
>
>  Nested Loop  (cost=0.00..50389.39 rows=3009 width=10) (actual
> time=0.503..320.872 rows=2189 loops=1)
>    ->  Nested Loop  (cost=0.00..30668.61 rows=3009 width=8) (actual
> time=0.254..94.116 rows=2189 loops=1)

This nested loop is using us most of your time.  Try increasing
work_mem and see if it chooses a better join plan, and / or turn off
nested loops for a moment and see if that helps.

set enable_nestloop = off

Note that set enable_xxx = off

Is kind of a hammer to the forebrain setting.  It's not subtle, and
the planner can't work around it.  So use them with caution.  That
said, I had one reporting query that simply wouldn't run fast without
turning off nested loops for that one.  But don't turn off nested
queries universally, they are still a good choice for smaller amounts
of data.