Re: Query tuning: partitioning, DISTINCT ON, and indexing

[bricklen <bricklen@xxxxxxxxx>]

On Thu, Jun 20, 2013 at 6:24 PM, Maciek Sakrejda <m.sakrejda@xxxxxxxxx> wrote:

SELECT

  DISTINCT ON (type) ts, type, details

FROM

  observations

WHERE

  subject = '...'

ORDER BY

  type, ts DESC;

First thing: What is your "work_mem" set to, and how much RAM is in the machine? If you look at the plan, you'll immediately notice the "external merge Disk" line where it spills to disk on the sort. Try setting your work_mem to 120MB or so (depending on how much RAM you have, # concurrent sessions, complexity of queries etc)