Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id BC1F76347F0
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 31 Mar 2009 18:12:00 -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 15499-01
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 31 Mar 2009 18:11:52 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-bw0-f169.google.com (mail-bw0-f169.google.com
	[209.85.218.169])
	by mail.postgresql.org (Postfix) with ESMTP id 7921B6347F3
	for <pgsql-performance@postgresql.org>;
	Tue, 31 Mar 2009 18:11:55 -0300 (ADT)
Received: by bwz17 with SMTP id 17so4110794bwz.19
	for <pgsql-performance@postgresql.org>;
	Tue, 31 Mar 2009 14:11:52 -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=KFIBFT0vIZMfadJ3aFtgTR+ayCOU2seFFyNj50C0c3c=;
	b=bCr47BShFYPB9JRSOHQM6b5bgaRU5rxPA3KlaJNU6RQyrOppTAMglFRRul95tD95S1
	ty6xiFuDzSPyXxl+0tWRi5WeWhQnWN9EKnxxyzs6qCjRAhGhnjrSeM4R8rh0nGw98PZD
	foZKiMS8HH2JZUmkYSSO3q/JcPHh8LpxQodK8=
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=CmKOhmn6+/J7rVhti/42zZVJ7WVYgUP2p2gM8kpFSihLMPrPIhI3ERhP/U9bmIRFtp
	QVR8k2VyXifKggXnkyI5gRPjhsz3SGct4WpRGyUvW+3ct8sdcsT7qC1jpXrlzz+SvZpl
	57w4tljHKyZhBeTLvpzBXIofEM9uHRb4BSybQ=
MIME-Version: 1.0
Received: by 10.103.225.11 with SMTP id c11mr2485521mur.115.1238533912817;
	Tue, 31 Mar 2009 14:11:52 -0700 (PDT)
In-Reply-To: <alpine.DEB.2.00.0903311757030.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.0903311757030.21772@aragorn.flymine.org>
Date: Wed, 1 Apr 2009 00:11:52 +0300
Message-ID: <331e40660903311411k532df0e3jeeffdb184adba9e6@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=001636b2ae6bea71da046670a542
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/441
X-Sequence-Number: 33363

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

>
>
> The outer nested join has the VALUES as the main loop, and the complicated
> join as the leaf. So, the complicated overlap-finding join gets run twice.


That's weird. What do you have as statistics target? Planner is incorrect
few orders of magnitude, so increasing it may help.
BTW: One of constraints is redundant l1.start <= l2.start implies l1.start
<= l2.end, so latter can be removed as for me.


>
>
> Oh, there's also the great big sort and unique, but I think I can get rid
> of that.
>

As far as I can see, duplicates will occur if and only if l1.start ==
l2.start && l1.end == l2.end.
That can be easily filtered by adding "where n=1 or l1.start != l2.start or
l1.end != l2.end" to outer select.

--001636b2ae6bea71da046670a542
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>
The outer nested join has the VALUES as the main loop, and the complicated =
join as the leaf. So, the complicated overlap-finding join gets run twice.<=
/blockquote><div><br>That&#39;s weird. What do you have as statistics targe=
t? Planner is incorrect few orders of magnitude, so increasing it may help.=
<br>
BTW: One of constraints is redundant  l1.start &lt;=3D l2.start implies l1.=
start &lt;=3D l2.end, so latter can be removed as for me.<br>=A0<br></div><=
blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid rgb(204, 2=
04, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<br>
<br>
Oh, there&#39;s also the great big sort and unique, but I think I can get r=
id of that.<br>
</blockquote></div><br>As far as I can see, duplicates will occur if and on=
ly if l1.start =3D=3D l2.start &amp;&amp; l1.end =3D=3D l2.end.<br>That can=
 be easily filtered by adding &quot;where n=3D1 or l1.start !=3D l2.start o=
r l1.end !=3D l2.end&quot; to outer select.<br>
<br><span style=3D"display: none; visibility: hidden;" id=3D"wikEdSetupFlag=
"></span><span style=3D"display: none; visibility: hidden;" id=3D"wikEdDiff=
SetupFlag"></span>

--001636b2ae6bea71da046670a542--