Date: Wed, 13 Nov 2019 21:47:37 +0100
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: =?utf-8?B?THXDrXM=?= Roberto Weck <luisroberto@siscobra.com.br>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Parallel Query
Message-ID: <20191113204737.l6uny3qdwl6a4u7m@development>
References: <732b7303-8b60-2f33-7f00-3b0cd70a7c5b@siscobra.com.br>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1; format=flowed
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <732b7303-8b60-2f33-7f00-3b0cd70a7c5b@siscobra.com.br>
User-Agent: NeoMutt/20180716
Precedence: bulk

On Wed, Nov 13, 2019 at 05:16:44PM -0300, Luís Roberto Weck wrote:
>Hi!
>
>Is there a reason query 3 can't use parallel workers? Using q1 and q2 
>they seem very similar but can use up to 4 workers to run faster:
>
>q1: https://pastebin.com/ufkbSmfB
>q2: https://pastebin.com/Yt32zRNX
>q3: https://pastebin.com/dqh7yKPb
>
>The sort node on q3 takes almost 12 seconds, making the query run on 
>68  if I had set enough work_mem to make it all in memory.
>

Most likely because it'd be actually slower. The trouble is the
aggregation does not actually reduce the cardinality, or at least the
planner does not expect that - the Sort and GroupAggregate are expected
to produce 3454539 rows. The last step of the aggregation has to receive
and merge data from all workers, which is not exactly free, and if there
is no reduction of cardinality it's likely cheaper to just do everything
in a single process serially.

How does the explain analyze output look like without the HAVING clause?

Try setting parallel_setup_cost and parallel_tuple_cost to 0. That might
trigger parallel query.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services