Re: Increasing work_mem slows down query, why?

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