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

help / color / mirror / Atom feed
From: Justin Pryzby <pryzby@telsasoft.com>
To: kimaidou <kimaidou@gmail.com>
Cc: Michael Lewis <mlewis@entrata.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Aggregate and many LEFT JOIN
Date: Tue, 26 Feb 2019 07:51:34 -0600
Message-ID: <20190226135134.GS28750@telsasoft.com> (raw)
In-Reply-To: <CAMKXKO5qvnLYCYRJux4yaN=Lj-phCgnE+KBere7VS7d31z78JA@mail.gmail.com>
References: <CAMKXKO6Ltz14uVgAyxy-cGSZoOQCbU5__2mjgVPv90snONZjNw@mail.gmail.com>
	<20190222155415.GE28750@telsasoft.com>
	<CAHOFxGpQ+RDdbfaVTPC8mHhRu5mQ48q3CTac3Vtfzo=7yn4U9Q@mail.gmail.com>
	<CAMKXKO5d8g45qYUtB6FvbQYW4JVEzjUBxN5yJtgvQ+90A0ukBg@mail.gmail.com>
	<CAHOFxGpHYTTVAdJW1Fi9K3N4P12nF384WyrvwbhgZBYpCMmC2g@mail.gmail.com>
	<17803.1550858759@sss.pgh.pa.us>
	<CAMKXKO4qEWi81UFHUQ5b=Aty-EDLkVf74FoBhFqTrwdUGHKMHg@mail.gmail.com>
	<CAMKXKO4NzL1goGKPUGjzCtC08JzrESDk6fHFcB5rK6hSS8LBqQ@mail.gmail.com>
	<CAHOFxGpb-MdAMWmmHJs48tV+DAxUi57P2XKoLnsqEaVSDXJcFA@mail.gmail.com>
	<CAMKXKO5qvnLYCYRJux4yaN=Lj-phCgnE+KBere7VS7d31z78JA@mail.gmail.com>

On Tue, Feb 26, 2019 at 01:54:00PM +0100, kimaidou wrote:
> I manage to avoid the disk sort after performing a VACUUM ANALYSE;
> And with a session work_mem = '250MB'
> 
> * SQL http://paste.debian.net/1070207/
> * EXPLAIN https://explain.depesz.com/s/nJ2y
> 
> It stills spent 16s
> It seems this kind of query will need better hardware to scale...

Once you've exhausted other ideas, you could consider making that a TEMPORARY
TABLE, and creating an index on it (and analyzing it) and then aggregating.
It'd be several separate queries.

Justin




view thread (12+ messages)  latest in thread

Message-ID: <20190226135134.GS28750@telsasoft.com>
Permalink:  ../20190226135134.GS28750@telsasoft.com/
Also on:    postgresql.org/message-id/20190226135134.GS28750@telsasoft.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: pryzby@telsasoft.com, kimaidou@gmail.com, mlewis@entrata.com, tgl@sss.pgh.pa.us
  Subject: Re: Aggregate and many LEFT JOIN
  In-Reply-To: <20190226135134.GS28750@telsasoft.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