pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: AndyG <andy.gumbrecht@orprovision.com>
To: pgsql-performance@postgresql.org
Subject: Re: Slow query, where am I going wrong?
Date: Wed, 31 Oct 2012 07:59:12 -0700 (PDT)
Message-ID: <1351695552185-5730185.post@n5.nabble.com> (raw)
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>

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.




view thread (11+ messages)

Message-ID: <1351695552185-5730185.post@n5.nabble.com>
Permalink:  ../1351695552185-5730185.post@n5.nabble.com/
Also on:    postgresql.org/message-id/1351695552185-5730185.post@n5.nabble.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: andy.gumbrecht@orprovision.com
  Subject: Re: Slow query, where am I going wrong?
  In-Reply-To: <1351695552185-5730185.post@n5.nabble.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox