Re: [PERFORM] Any advice tuning this query ?

On Fri, Nov 11, 2016 at 7:19 AM, Henrik Ekenberg wrote: > Hi, > > I have a select moving around a lot of data and takes times > Any advice tuning this query ? > > EXPLAIN (ANALYZE ON, BUFFERS ON) > When accessing lots of data, sometimes the act of collecting timing on all of

Re: [PERFORM] Any advice tuning this query ?

I have a couple of suggestions which should lead to some minor improvements, but in general I am surprised by the huge size of the result set. Is your goal really to get a 43 million row result? When a query returns that many rows usually all possible query plans are more or less bad. 1) You

Re: [PERFORM] Any advice tuning this query ?

Hi, On Fri, 2016-11-11 at 16:19 +0100, Henrik Ekenberg wrote: > Sort Method: external merge  Disk: 16782928kB This query is generating 16GB temp file on disk. Is this the amount of data you want to sort? Regards, -- Devrim GÜNDÜZ EnterpriseDB: http://www.enterprisedb.com PostgreSQL

[PERFORM] Any advice tuning this query ?

Hi, I have a select moving around a lot of data and takes times Any advice tuning this query ? EXPLAIN (ANALYZE ON, BUFFERS ON)     select     d.books,     d.date publish_date,     extract(dow from d.date) publish_dow,     week_num_fixed,     coalesce(sum(case when i.invno is not null then 1