DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type;
	b=vGPRV5JTKYQpytviNeSstQ2N0cSRbBhqnyufWWh7xUZSVP4AZpaC8ywn/z4AyLWmKASNDvccG7CiLjieTm/Qlo7Ulkm/jV1Vg38tKKOVEwSqS0QhfgEHYqWDdfEklGvdhTlQo30S+qE3YtdFLZW1KczKyx+o3hOmmbVzH/Ze6Ag=;
Message-ID: <630076.87313.qm@web112408.mail.gq1.yahoo.com>
References: <4DD0B8AD.3020906@orkash.com>
	<4DD0F5A3.1060007@postnewspapers.com.au>
Date: Mon, 16 May 2011 04:16:44 -0700 (PDT)
From: Denis de Bernardy <ddebernardy@yahoo.com>
Reply-To: Denis de Bernardy <ddebernardy@yahoo.com>
Subject: Re: Why query takes soo much time
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
In-Reply-To: <4DD0F5A3.1060007@postnewspapers.com.au>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1443786998-1305544604=:87313"

--0-1443786998-1305544604=:87313
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

[big nestloop with a huge number of rows]=0A=0AYou're in an edge case, and =
I doubt you'll get things to run much faster: you want the last 1k rows out=
 of an 18M row result set... It will be slow no matter what you do.=0A=0AWh=
at the plan is currently doing, is it's going through these 18M rows using =
a for each loop, until it returns the 1k requested rows. Without the offset=
, the plan is absolutely correct (and quite fast, I take it). With the enor=
mous offset, it's a different story as you've noted.=0A=0AAn alternative pl=
an could have been to hash join the tables together, to sort the result set=
, and to apply the limit/offset on the resulting set.=A0You can probably fo=
rce the planner to do so by rewriting your statement using a with statement=
, too:=0A=0AEXPLAIN ANALYZE=0AWITH rows AS (=0ASELECT c.clause, s.subject ,=
s.object , s.verb, s.subject_type, s.object_type ,s.doc_id ,s.svo_id=A0=0AF=
ROM clause2 c INNER JOIN svo2 s ON (c.clause_id=3Ds.clause_id AND c.source_=
id=3Ds.doc_id AND c.sentence_id=3Ds.sentence_id)=0A=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0 INNER JOIN page_content p ON (s.doc_id=3Dp.crawled_page_=
id)=0A)=0ASELECT *=0AFROM rows=0AORDER BY svo_id limit 1000 offset 17929000=
=0A=0A=0AI've my doubts that it'll make much of a different, though: you'll=
 still be extracting the last 1k rows out of 18M.=0A=0AD=0A
--0-1443786998-1305544604=:87313
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

<html><body><div style=3D"color:#000; background-color:#fff; font-family:ti=
mes new roman, new york, times, serif;font-size:12pt"><div><span>[big nestl=
oop with a huge number of rows]</span></div><div><span><br></span></div><di=
v><span>You're in an edge case, and I doubt you'll get things to run much f=
aster: you want the last 1k rows out of an 18M row result set... It will be=
 slow no matter what you do.</span></div><div><span><br></span></div><div><=
span>What the plan is currently doing, is it's going through these 18M rows=
 using a for each loop, until it returns the 1k requested rows. Without the=
 offset, the plan is absolutely correct (and quite fast, I take it). With t=
he enormous offset, it's a different story as you've noted.</span></div><di=
v><span><br></span></div><div>An alternative plan could have been to hash j=
oin the tables together, to sort the result set, and to apply the limit/off=
set on the resulting set.&nbsp;You can probably force the planner to
 do so by rewriting your statement using a with statement, too:</div><div><=
span><br></span></div><div><span><span class=3D"Apple-style-span" style=3D"=
font-family: monospace; ">EXPLAIN ANALYZE</span></span></div><div><span><sp=
an class=3D"Apple-style-span" style=3D"font-family: monospace; ">WITH rows =
AS (<br>SELECT c.clause, s.subject ,s.object , s.verb, s.subject_type, s.ob=
ject_type ,s.doc_id ,s.svo_id&nbsp;<br>FROM clause2 c INNER JOIN svo2 s ON =
(c.clause_id=3Ds.clause_id AND c.source_id=3Ds.doc_id AND c.sentence_id=3Ds=
.sentence_id)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp; INNER JOIN page_content p ON (s.doc_id=3Dp.crawl=
ed_page_id)</span></span></div><div><span><span class=3D"Apple-style-span" =
style=3D"font-family: monospace; ">)</span></span></div><div><span><span cl=
ass=3D"Apple-style-span" style=3D"font-family: monospace; ">SELECT *</span>=
</span></div><div><span><span class=3D"Apple-style-span" style=3D"font-fami=
ly: monospace; ">FROM
 rows<br>ORDER BY svo_id limit 1000 offset 17929000</span><br></span></div>=
<div><span><br></span></div><div><span>I've my doubts that it'll make much =
of a different, though: you'll still be extracting the last 1k rows out of =
18M.</span></div><div><span><br></span></div><div><span>D</span></div><div>=
<br></div></div></body></html>
--0-1443786998-1305544604=:87313--