Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gyd96-0006yV-RC for pgsql-performance@arkaria.postgresql.org; Tue, 26 Feb 2019 13:51:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gyd94-0005EO-M2 for pgsql-performance@arkaria.postgresql.org; Tue, 26 Feb 2019 13:51:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gyd94-0005EG-9U for pgsql-performance@lists.postgresql.org; Tue, 26 Feb 2019 13:51:42 +0000 Received: from mail-io1-xd42.google.com ([2607:f8b0:4864:20::d42]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gyd90-0001wB-RS for pgsql-performance@postgresql.org; Tue, 26 Feb 2019 13:51:41 +0000 Received: by mail-io1-xd42.google.com with SMTP id x3so10498887ior.6 for ; Tue, 26 Feb 2019 05:51:38 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=telsasoft-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to:user-agent; bh=2G3ipf+8hJ8MGb/b7PrFeFQESTlBcRbYwSPV8Zq+z90=; b=V6nn1uiAwkkLQ5t+bjF3P+BN1vqSYmc5bf/pXzeocYyzW3qIVmXqDHmLokBq188D9R lO0JKEolL53DXc9DrWPLoBWvn86XFHkcOiOin2zkijQw3btITR1XNfWznfdotUgJ1jeB Ve9B8ek3NJ7J4vWwQnHL7fyoD1sS8xLkJ7/Evj7S/bveSD9CaCoJEGiMqa/W0fOvlYrR Zw/+3eu7JHGnBPftR+/rFOc/1XHZ7KK4acZIeq4PO1qni4QapHi0IacJmYtOtaJVPgLm G40NHvG0NafAiJjbYNG+SAqfoiDimhfcAJMt2p2QHCH3iqRPbXd4pj3UmGg2Oo0j1M+j m9lg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to:user-agent; bh=2G3ipf+8hJ8MGb/b7PrFeFQESTlBcRbYwSPV8Zq+z90=; b=EOGlE12E60aCJOl0JEVs59rGuqI9q+wJ5qiUXMcNdD3SuKvysDEm8/6yYe+qnEp0kG q/C3RuD9E7EywgMaFnoJX2TE80u0oXgTWdwtztpsgMeiacMeUZan3OOeXyFpYBwzRKnp pi11RUCK0XWUqrU5Trcd9TSXFGi3fWSp2iTSaHS6AdEGVKvJTo4GvJ3bxg79AQSmKq0Q bT7XkGhBiIfxEalUL4Syv5klmlbgG35QQvSD8Lvw0Q6X9gBgUeVo44aBMVgoG5X0gJ1w M+Xf7IjQaD/mjhvh9wKEhEIAN+RunnadMlcuBwl2iXj7kjt8oJWf17zINypfuLFweANi g8+g== X-Gm-Message-State: AHQUAubXi/vDEopvgVkuoqK/kpT7w/FuUPHr3vQkVJDPv53DgDDFw5IT nQFGmGINPlRe6vPWGOC6gwCBjg== X-Google-Smtp-Source: AHgI3IZN8R+8y7R0WjyQLi1TuI+qVxvlsYOF6WMaychaWrLcbbKxd1uuCyG+ORqM20Gmq0UPnXvPNg== X-Received: by 2002:a5d:8d02:: with SMTP id p2mr13094735ioj.113.1551189096470; Tue, 26 Feb 2019 05:51:36 -0800 (PST) Received: from pryzbyj (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id o26sm4979716iob.73.2019.02.26.05.51.35 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Tue, 26 Feb 2019 05:51:35 -0800 (PST) Received: by pryzbyj (Postfix, from userid 1000) id 4DF36800C83; Tue, 26 Feb 2019 07:51:34 -0600 (CST) Date: Tue, 26 Feb 2019 07:51:34 -0600 From: Justin Pryzby To: kimaidou Cc: Michael Lewis , Tom Lane , postgres performance list Subject: Re: Aggregate and many LEFT JOIN Message-ID: <20190226135134.GS28750@telsasoft.com> References: <20190222155415.GE28750@telsasoft.com> <17803.1550858759@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.23 (2014-03-12) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk 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