Received: from magus.postgresql.org ([87.238.57.229])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <andy.gumbrecht@orprovision.com>) id 1TTZlH-0008Uc-Ft
	for pgsql-performance@postgresql.org; Wed, 31 Oct 2012 14:59:19 +0000
Received: from sam.nabble.com ([216.139.236.26])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <andy.gumbrecht@orprovision.com>) id 1TTZlC-0005es-KA
	for pgsql-performance@postgresql.org; Wed, 31 Oct 2012 14:59:18 +0000
Received: from [192.168.236.26] (helo=sam.nabble.com)
	by sam.nabble.com with esmtp (Exim 4.72)
	(envelope-from <andy.gumbrecht@orprovision.com>) id 1TTZlA-0004OJ-6o
	for pgsql-performance@postgresql.org; Wed, 31 Oct 2012 07:59:12 -0700
Date: Wed, 31 Oct 2012 07:59:12 -0700 (PDT)
From: AndyG <andy.gumbrecht@orprovision.com>
To: pgsql-performance@postgresql.org
Message-ID: <1351695552185-5730185.post@n5.nabble.com>
In-Reply-To: 
 <D960CB61B694CF459DCFB4B0128514C208A4E327@exadv11.host.magwien.gv.at>
References: <508F82E1.3020202@orprovision.com>
	<D960CB61B694CF459DCFB4B0128514C2089A60C2@exadv11.host.magwien.gv.at>
	<1351608872327-5730059.post@n5.nabble.com>
	<D960CB61B694CF459DCFB4B0128514C2089A62C4@exadv11.host.magwien.gv.at>
	<1351675103112-5730140.post@n5.nabble.com>
	<D960CB61B694CF459DCFB4B0128514C208A4E205@exadv11.host.magwien.gv.at>
	<1351678691550-5730145.post@n5.nabble.com>
	<1351682892740-5730151.post@n5.nabble.com>
	<D960CB61B694CF459DCFB4B0128514C208A4E327@exadv11.host.magwien.gv.at>
Subject: Re: Slow query, where am I going wrong?
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: 0.0 (/)
X-Archive-Number: 201210/404
X-Sequence-Number: 48363

Externalizing the limit has improved the speed a lot. Distinct is half a
second faster than group by.

http://explain.depesz.com/s/vP1

with tmp as (
select distinct tr.nr as tnr
, tr.time_end as tend
, c.id_board as cb
, c.id_board_mini as cbm
, ti.id_test_result as itr 
from test_item ti
, test_result tr
, component c 
, recipe_version rv 
where ti.id_test_result = tr.id 
and ti.id_component = c.id 
and tr.id_recipe_version = rv.id 
and (rv.id_recipe in ('6229bf04-ae38-11e1-a955-0021974df2b2')) 
and tr.time_end <> cast('1970-01-01 01:00:00.000' as timestamp) 
and tr.time_begin >= cast('2012-10-27 08:00:17.045' as timestamp)
and ti.type = 'Component' 
--group by tr.nr , tr.time_end , c.id_board , c.id_board_mini ,
ti.id_test_result 
order by tr.time_end asc) 
select * from tmp
limit 10000



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Slow-query-where-am-I-going-wrong-tp5730015p5730185.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.