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.