Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 38E2C634E2A
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 30 Mar 2009 15:15:44 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 64352-02
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 30 Mar 2009 15:15:37 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-fx0-f174.google.com (mail-fx0-f174.google.com
	[209.85.220.174])
	by mail.postgresql.org (Postfix) with ESMTP id EEC946372C5
	for <pgsql-performance@postgresql.org>;
	Mon, 30 Mar 2009 15:13:24 -0300 (ADT)
Received: by fxm22 with SMTP id 22so3609737fxm.19
	for <pgsql-performance@postgresql.org>;
	Mon, 30 Mar 2009 11:13:23 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type;
	bh=DulCpdnJk8V7ePHi0cwWyGwPc6oElMY2zsb9UnyAvr4=;
	b=Sku/plpsoGluF3iosaFMhqIT792QBOy6glBva2VKy6z4P+TJAlnh1s4fUevKHmC03e
	Rjusj8V+UDpqndBuW+FT1gfMiA7Wav9FHxNUFJ7aZAlsZSOt0CzcCeb7LcVHCbwyz9Yr
	F3A65VVF9d2HpPN0OygN29Yj3xA44Vgtxywlc=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=NyUCRIu+fUwcqF5g0oNiCodE1//bQKtO7QZWlT+b6SnXpU6uw8Uri06YCpD1f2Wjn3
	4q++0MZsokMib9d55zK+fB8qxPut5FANMLvtdxxYj6hZvbvJQ3SBul/RVZf28TTRaEPp
	rV0Z86P7CIjzoOJpWC+TFH44VcbB3L4N64Oug=
MIME-Version: 1.0
Received: by 10.103.192.2 with SMTP id u2mr1832045mup.95.1238436803472; Mon,
	30 Mar 2009 11:13:23 -0700 (PDT)
In-Reply-To: <alpine.DEB.2.00.0903301719230.21772@aragorn.flymine.org>
References: <alpine.DEB.2.00.0903261313290.21772@aragorn.flymine.org>
	<331e40660903270936u5db00807sb1169df66443ade3@mail.gmail.com>
	<alpine.DEB.2.00.0903271708080.21772@aragorn.flymine.org>
	<2298.1238179393@sss.pgh.pa.us>
	<alpine.DEB.2.00.0903301618190.21772@aragorn.flymine.org>
	<331e40660903300914x1c7747d0j94be7addeb2b31f5@mail.gmail.com>
	<alpine.DEB.2.00.0903301719230.21772@aragorn.flymine.org>
Date: Mon, 30 Mar 2009 21:13:23 +0300
Message-ID: <331e40660903301113x7371fb33n302fc3f2c720efb2@mail.gmail.com>
Subject: Re: Very specialised query
From: =?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= <tivv00@gmail.com>
To: Matthew Wakeling <matthew@flymine.org>
Cc: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=001636a7dabfbf6c8904665a092b
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200903/417
X-Sequence-Number: 33339

--001636a7dabfbf6c8904665a092b
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

>
>
> Yeah, that's nice.
>
> However, it is still the case that we can't trust the database to choose
> the correct plan. It is currently only choosing the correct plan now by
> chance, and some time later it may by chance switch to one that takes 40
> minutes.


What is the bad plan? Is it like the first plan from your first message?
You can sometimes tweak optimizer to make sure it will do correct plan. E.g.
when your database fits in memory, you can tweak page access costs. Also
don't forget to raise statistics target.

BTW: About aggregates: they can return arrays, but I can't imagine what you
can group by on... May be windowing functions from 8.4 could help.

Also, if your maximum length (select max(end-start) from location) is low
enough, you can try adding some more constraints to make optimizer happy
(have it more precise row count to select correct plan).

--001636a7dabfbf6c8904665a092b
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"borde=
r-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-le=
ft: 1ex;"><br>
Yeah, that&#39;s nice.<br>
<br>
However, it is still the case that we can&#39;t trust the database to choos=
e the correct plan. It is currently only choosing the correct plan now by c=
hance, and some time later it may by chance switch to one that takes 40 min=
utes.</blockquote>
<div><br>What is the bad plan? Is it like the first plan from your first me=
ssage?<br>You can sometimes tweak optimizer to make sure it will do correct=
 plan. E.g. when your database fits in memory, you can tweak page access co=
sts. Also don&#39;t forget to raise statistics target.<br>
<br>BTW: About aggregates: they can return arrays, but I can&#39;t imagine =
what you can group by on... May be windowing functions from 8.4 could help.=
<br><br>Also, if your maximum length (select max(end-start) from location) =
is low enough, you can try adding some more constraints to make optimizer h=
appy (have it more precise row count to select correct plan).<br>
<br></div></div><span style=3D"display: none; visibility: hidden;" id=3D"wi=
kEdSetupFlag"></span><span style=3D"display: none; visibility: hidden;" id=
=3D"wikEdDiffSetupFlag"></span>

--001636a7dabfbf6c8904665a092b--