pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Silvio Moioli <moio@suse.de>
To: pgsql-performance@lists.postgresql.org
Subject: Re: Increasing work_mem slows down query, why?
Date: Mon, 30 Mar 2020 15:09:12 +0200
Message-ID: <f20e2bc4-c8c0-d2d2-7a77-bd5c8cfb215c@suse.de> (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 >
On 3/30/20 12:12 PM, Pavel Stehule wrote:
> Do you have some planner variables changed - like seq_page_cost?
That one was not changed but another one is - cpu_tuple_cost (to 0.5). Indeed bringing it back to its default does improve the query time significantly:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=36735.61..36735.63 rows=2 width=36) (actual time=357.825..358.036 rows=1100 loops=1)
Output: wanted_capability.ordering, inserted_capability.id
Buffers: shared hit=14443
CTE wanted_capability
-> Values Scan on "*VALUES*" (cost=0.00..13.75 rows=1100 width=68) (actual time=0.001..0.355 rows=1100 loops=1)
Output: "*VALUES*".column1, "*VALUES*".column2, "*VALUES*".column3
CTE missing_capability
-> Hash Left Join (cost=18263.69..18347.78 rows=1 width=68) (actual time=183.826..183.826 rows=0 loops=1)
Output: wanted_capability_2.ordering, wanted_capability_2.name, wanted_capability_2.version
Hash Cond: (wanted_capability_2.name = (rhnpackagecapability_1.name)::text)
Join Filter: (NOT (wanted_capability_2.version IS DISTINCT FROM (rhnpackagecapability_1.version)::text))
Filter: (rhnpackagecapability_1.id IS NULL)
Rows Removed by Filter: 1100
Buffers: shared hit=7217
-> CTE Scan on wanted_capability wanted_capability_2 (cost=0.00..22.00 rows=1100 width=68) (actual time=0.000..0.729 rows=1100 loops=1)
Output: wanted_capability_2.ordering, wanted_capability_2.name, wanted_capability_2.version
-> Hash (cost=12126.64..12126.64 rows=490964 width=79) (actual time=181.477..181.477 rows=490964 loops=1)
Output: rhnpackagecapability_1.name, rhnpackagecapability_1.version, rhnpackagecapability_1.id
Buckets: 524288 Batches: 1 Memory Usage: 53907kB
Buffers: shared hit=7217
-> Seq Scan on public.rhnpackagecapability rhnpackagecapability_1 (cost=0.00..12126.64 rows=490964 width=79) (actual time=0.009..57.663 rows=490964 loops=1)
Output: rhnpackagecapability_1.name, rhnpackagecapability_1.version, rhnpackagecapability_1.id
Buffers: shared hit=7217
CTE inserted_capability
-> Insert on public.rhnpackagecapability rhnpackagecapability_2 (cost=0.00..0.04 rows=1 width=1080) (actual time=183.828..183.828 rows=0 loops=1)
Output: rhnpackagecapability_2.id, rhnpackagecapability_2.name, rhnpackagecapability_2.version
Conflict Resolution: NOTHING
Tuples Inserted: 0
Conflicting Tuples: 0
Buffers: shared hit=7217
-> Subquery Scan on "*SELECT*" (cost=0.00..0.04 rows=1 width=1080) (actual time=183.827..183.827 rows=0 loops=1)
Output: "*SELECT*".nextval, "*SELECT*".name, "*SELECT*".version, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
Buffers: shared hit=7217
-> CTE Scan on missing_capability (cost=0.00..0.02 rows=1 width=72) (actual time=183.827..183.827 rows=0 loops=1)
Output: nextval('rhn_pkg_capability_id_seq'::regclass), missing_capability.name, missing_capability.version
Buffers: shared hit=7217
-> Sort (cost=18374.04..18374.04 rows=2 width=36) (actual time=357.825..357.862 rows=1100 loops=1)
Output: wanted_capability.ordering, inserted_capability.id
Sort Key: wanted_capability.ordering, inserted_capability.id
Sort Method: quicksort Memory: 100kB
Buffers: shared hit=14443
-> Append (cost=0.03..18374.03 rows=2 width=36) (actual time=357.071..357.660 rows=1100 loops=1)
Buffers: shared hit=14437
-> Hash Join (cost=0.03..26.23 rows=1 width=36) (actual time=183.847..183.847 rows=0 loops=1)
Output: wanted_capability.ordering, inserted_capability.id
Hash Cond: (wanted_capability.name = (inserted_capability.name)::text)
Join Filter: (NOT (wanted_capability.version IS DISTINCT FROM (inserted_capability.version)::text))
Buffers: shared hit=7220
-> CTE Scan on wanted_capability (cost=0.00..22.00 rows=1100 width=68) (actual time=0.002..0.002 rows=1 loops=1)
Output: wanted_capability.ordering, wanted_capability.name, wanted_capability.version
-> Hash (cost=0.02..0.02 rows=1 width=1064) (actual time=183.829..183.829 rows=0 loops=1)
Output: inserted_capability.id, inserted_capability.name, inserted_capability.version
Buckets: 1024 Batches: 1 Memory Usage: 8kB
Buffers: shared hit=7217
-> CTE Scan on inserted_capability (cost=0.00..0.02 rows=1 width=1064) (actual time=183.828..183.828 rows=0 loops=1)
Output: inserted_capability.id, inserted_capability.name, inserted_capability.version
Buffers: shared hit=7217
-> Hash Join (cost=18263.69..18347.78 rows=1 width=10) (actual time=173.223..173.750 rows=1100 loops=1)
Output: wanted_capability_1.ordering, rhnpackagecapability.id
Hash Cond: (wanted_capability_1.name = (rhnpackagecapability.name)::text)
Join Filter: (NOT (wanted_capability_1.version IS DISTINCT FROM (rhnpackagecapability.version)::text))
Buffers: shared hit=7217
-> CTE Scan on wanted_capability wanted_capability_1 (cost=0.00..22.00 rows=1100 width=68) (actual time=0.000..0.070 rows=1100 loops=1)
Output: wanted_capability_1.ordering, wanted_capability_1.name, wanted_capability_1.version
-> Hash (cost=12126.64..12126.64 rows=490964 width=79) (actual time=172.220..172.220 rows=490964 loops=1)
Output: rhnpackagecapability.id, rhnpackagecapability.name, rhnpackagecapability.version
Buckets: 524288 Batches: 1 Memory Usage: 53922kB
Buffers: shared hit=7217
-> Seq Scan on public.rhnpackagecapability (cost=0.00..12126.64 rows=490964 width=79) (actual time=0.008..52.573 rows=490964 loops=1)
Output: rhnpackagecapability.id, rhnpackagecapability.name, rhnpackagecapability.version
Buffers: shared hit=7217
Planning time: 2.145 ms
Execution time: 358.773 ms
Is that an unreasonable value? For the sake of this discussison, I am targeting fairly average bare-metal SSD-backed servers with recent CPUs (let's say 3 year old maximum), with ample available RAM.
Thanks!
Regards,
--
Silvio Moioli
SUSE Manager Development Team
view thread (12+ messages) latest in thread
Message-ID: <f20e2bc4-c8c0-d2d2-7a77-bd5c8cfb215c@suse.de>
Permalink: ../f20e2bc4-c8c0-d2d2-7a77-bd5c8cfb215c@suse.de/
Also on: postgresql.org/message-id/f20e2bc4-c8c0-d2d2-7a77-bd5c8cfb215c@suse.de
copy link · copy postgr.es
reply 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: moio@suse.de, pgsql-performance@lists.postgresql.org
Subject: Re: Increasing work_mem slows down query, why?
In-Reply-To: <f20e2bc4-c8c0-d2d2-7a77-bd5c8cfb215c@suse.de>
* 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