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

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