Re: Increasing work_mem slows down query, why? - Mailing list pgsql-performance

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