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: 
 <CAFj8pRADkvF1BwpX7Dv3cYOkcEFAHNNcRX1rM3eqbkGe7rbTbg@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>
Comments: In-reply-to Pavel Stehule <pavel.stehule@gmail.com>
	message dated "Mon, 30 Mar 2020 12:12:33 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <18261.1585584121.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 30 Mar 2020 12:02:01 -0400
Message-ID: <18262.1585584121@sss.pgh.pa.us>
Precedence: bulk

Pavel Stehule <pavel.stehule@gmail.com> writes:
> po 30. 3. 2020 v 10:12 odes=C3=ADlatel Silvio Moioli <moio@suse.de> naps=
al:
>> ->  Sort  (cost=3D299108.00..300335.41 rows=3D490964 width=3D79)
>>         (actual time=3D6475.147..6494.111 rows=3D462600 loops=3D1)
>>         Output: rhnpackagecapability_1.name,
>>         rhnpackagecapability_1.version, rhnpackagecapability_1.id
>>         Sort Key: rhnpackagecapability_1.name
>>         Sort Method: quicksort  Memory: 79862kB
>>         Buffers: shared hit=3D7217
>>         ->  Seq Scan on public.rhnpackagecapability rhnpackagecapabilit=
y_1  (cost=3D0.00..252699.00 rows=3D490964 width=3D79) (actual time=3D0.01=
6..59.976 rows=3D490964 loops=3D1)

>> ->  Sort  (cost=3D299108.00..300335.41 rows=3D490964
>>         width=3D79) (actual time=3D6458.988..6477.151 rows=3D462600 loo=
ps=3D1)
>>         Output: rhnpackagecapability.id,
>>         rhnpackagecapability.name, rhnpackagecapability.version
>>         Sort Key: rhnpackagecapability.name
>>         Sort Method: quicksort  Memory: 79862kB
>>         Buffers: shared hit=3D7217
>>         ->  Seq Scan on public.rhnpackagecapability (cost=3D0.00..25269=
9.00 rows=3D490964 width=3D79) (actual time=3D0.012..50.467 rows=3D490964 =
loops=3D1)

> I did some tests and it looks so a penalization for sort long keys is no=
t
> too high. In your case it is reason why sort is very slow (probably due
> slow locales). Then the cost of hash join and sort is similar, although =
in
> reality it is not true.

Yeah, the run time of the slow query seems to be almost entirely expended
in these two sort steps, while the planner doesn't think that they'll be
very expensive.  Tweaking unrelated cost settings to work around that is
not going to be helpful.  What you'd be better off trying to do is fix
the slow sorting.  Is rhnpackagecapability.name some peculiar datatype?
If it's just relatively short text strings, as one would guess from the
column name, then what you must be looking at is really slow locale-based
sorting.  What's the database's LC_COLLATE setting?  Can you get away
with switching it to C?

			regards, tom lane