From: Tom Lane <tgl@sss.pgh.pa.us>
To: Pavel Stehule <pavel.stehule@gmail.com>
cc: Silvio Moioli <moio@suse.de>,
        Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: Increasing work_mem slows down query, why?
In-reply-to: 
 <CAFj8pRA+teK=u9ei82WXcxAXF2o+-6NShsKt8XBo20ycUcPVCQ@mail.gmail.com>
References: <79dd683d-3296-1b21-ab4a-28fdc2d98807@suse.de>
 <CAFj8pRDmvK8hZXLHs9hEEyEjVOWYw0=fUSirBKu5OV5qmiu7jg@mail.gmail.com>
 <6d829078-b34a-ca4d-909a-5427aa22ae47@suse.de>
 <CAFj8pRADkvF1BwpX7Dv3cYOkcEFAHNNcRX1rM3eqbkGe7rbTbg@mail.gmail.com>
 <18262.1585584121@sss.pgh.pa.us>
 <CAFj8pRA+teK=u9ei82WXcxAXF2o+-6NShsKt8XBo20ycUcPVCQ@mail.gmail.com>
Comments: In-reply-to Pavel Stehule <pavel.stehule@gmail.com>
	message dated "Mon, 30 Mar 2020 18:18:17 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <19892.1585586177.1@sss.pgh.pa.us>
Date: Mon, 30 Mar 2020 12:36:17 -0400
Message-ID: <19893.1585586177@sss.pgh.pa.us>
Precedence: bulk

Pavel Stehule <pavel.stehule@gmail.com> writes:
> CTE scan has only 1100 rows, public.rhnpackagecapability  has 490964 rows.
> But planner does hash from public.rhnpackagecapability table. It cannot be
> very effective.

[ shrug... ]  Without stats on the CTE output, the planner is very
leery of putting it on the inside of a hash join.  The CTE might
produce output that ends up in just a few hash buckets, degrading
the join to something not much better than a nested loop.  As long
as there's enough memory to hash the known-well-distributed table,
putting it on the inside is safer and no costlier.

			regards, tom lane