From: Tom Lane <tgl@sss.pgh.pa.us>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Silvio Moioli <moio@suse.de>
Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: Increasing work_mem slows down query, why?
Date: Mon, 30 Mar 2020 12:02:01 -0400
Message-ID: <18262.1585584121@sss.pgh.pa.us> (raw)
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>
Pavel Stehule <pavel.stehule@gmail.com> writes:
> po 30. 3. 2020 v 10:12 odesÃlatel Silvio Moioli <moio@suse.de> napsal:
>> -> Sort (cost=299108.00..300335.41 rows=490964 width=79)
>> (actual time=6475.147..6494.111 rows=462600 loops=1)
>> Output: rhnpackagecapability_1.name,
>> rhnpackagecapability_1.version, rhnpackagecapability_1.id
>> Sort Key: rhnpackagecapability_1.name
>> Sort Method: quicksort Memory: 79862kB
>> Buffers: shared hit=7217
>> -> Seq Scan on public.rhnpackagecapability rhnpackagecapability_1 (cost=0.00..252699.00 rows=490964 width=79) (actual time=0.016..59.976 rows=490964 loops=1)
>> -> Sort (cost=299108.00..300335.41 rows=490964
>> width=79) (actual time=6458.988..6477.151 rows=462600 loops=1)
>> Output: rhnpackagecapability.id,
>> rhnpackagecapability.name, rhnpackagecapability.version
>> Sort Key: rhnpackagecapability.name
>> Sort Method: quicksort Memory: 79862kB
>> Buffers: shared hit=7217
>> -> Seq Scan on public.rhnpackagecapability (cost=0.00..252699.00 rows=490964 width=79) (actual time=0.012..50.467 rows=490964 loops=1)
> I did some tests and it looks so a penalization for sort long keys is not
> 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
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: tgl@sss.pgh.pa.us, pavel.stehule@gmail.com, moio@suse.de, pgsql-performance@lists.postgresql.org
Subject: Re: Increasing work_mem slows down query, why?
In-Reply-To: <18262.1585584121@sss.pgh.pa.us>
* 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