Re: Increasing work_mem slows down query, why?
On 3/30/20 8:56 AM, Pavel Stehule wrote:
> please, can you send explain in text form?
Sure. With work_mem = 80MB:
QUERY PLAN
-
Unique (cost=608228.26..608228.27 rows=2 width=36) (actual
time=13360.241..13360.454 rows=1100 loops=1)
Output: wanted_capability.ordering, inserted_capability.id
Buffers: shared hit=14448
CTE wanted_capability
-> Values Scan on "*VALUES*" (cost=0.00..552.75 rows=1100 width=68)
(actual time=0.001..0.246 rows=1100 loops=1)
Output: "*VALUES*".column1, "*VALUES*".column2, "*VALUES*".column3
CTE missing_capability
-> Merge Left Join (cost=300263.57..303282.17 rows=1 width=68) (actual
time=6686.320..6686.320 rows=0 loops=1)
Output: wanted_capability_2.ordering, wanted_capability_2.name,
wanted_capability_2.version
Merge 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=7222
-> Sort (cost=1155.57..1158.32 rows=1100 width=68) (actual
time=10.011..10.053 rows=1100 loops=1)
Output: wanted_capability_2.ordering,
wanted_capability_2.name, wanted_capability_2.version
Sort Key: wanted_capability_2.name
Sort Method: quicksort Memory: 203kB
Buffers: shared hit=5
-> CTE Scan on wanted_capability wanted_capability_2
(cost=0.00..1100.00 rows=1100 width=68) (actual time=0.000..0.516 rows=1100
loops=1)
Output: wanted_capability_2.ordering,
wanted_capability_2.name, wanted_capability_2.version
-> 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)
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..1.51 rows=1 width=1080) (actual time=6686.322..6686.322 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=7222
-> Subquery Scan on "*SELECT*" (cost=0.00..1.51 rows=1 width=1080)
(actual time=6686.321..6686.321 rows=0 loops=1)
Output: "*SELECT*".nextval, "*SELECT*".name,
"*SELECT*".version, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
Buffers: shared hit=7222
-> CTE Scan on missing_capability (cost=0.00..1.00 rows=1
width=72) (actual time=6686.320..6686.320 rows=0 loops=1)
Output: nextval('rhn_pkg_capability_id_seq'::regclass),
missing_capability.name, missing_capability.version
Buffers: shared hit=7222
-> Sort (cost=304391.82..304391.83 rows=2 width=36) (actual
time=13360.240..13360.283 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=14448
-> Append (cost=1.50..304391.81 rows=2 width=36) (actual
time=13357.167..13360.051 rows=1100 loops=1)
Buffers: shared hit=14442
-> Hash Join (cost=1.50..1108.64 rows=1 width=36) (actual
time=6686.340..6686.340 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=7225
-> CTE Scan on wanted_capability (cost=0.00..1100.00
rows=1100 width=68) (actual time=0.002..0.002 rows=1 loops=1)
Output: wanted_capability.ordering,
wanted_capability.name, wanted_capabili
Re: Increasing work_mem slows down query, why?
po 30. 3. 2020 v 10:12 odesílatel Silvio Moioli napsal:
> On 3/30/20 8:56 AM, Pavel Stehule wrote:
> > please, can you send explain in text form?
>
> Sure. With work_mem = 80MB:
>
>
> QUERY PLAN
>
> -
> Unique (cost=608228.26..608228.27 rows=2 width=36) (actual
> time=13360.241..13360.454 rows=1100 loops=1)
>Output: wanted_capability.ordering, inserted_capability.id
>Buffers: shared hit=14448
>CTE wanted_capability
> -> Values Scan on "*VALUES*" (cost=0.00..552.75 rows=1100 width=68)
> (actual time=0.001..0.246 rows=1100 loops=1)
>Output: "*VALUES*".column1, "*VALUES*".column2,
> "*VALUES*".column3
>CTE missing_capability
> -> Merge Left Join (cost=300263.57..303282.17 rows=1 width=68)
> (actual time=6686.320..6686.320 rows=0 loops=1)
>Output: wanted_capability_2.ordering, wanted_capability_2.name,
> wanted_capability_2.version
>Merge 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=7222
>-> Sort (cost=1155.57..1158.32 rows=1100 width=68) (actual
> time=10.011..10.053 rows=1100 loops=1)
> Output: wanted_capability_2.ordering,
> wanted_capability_2.name, wanted_capability_2.version
> Sort Key: wanted_capability_2.name
> Sort Method: quicksort Memory: 203kB
> Buffers: shared hit=5
> -> CTE Scan on wanted_capability wanted_capability_2
> (cost=0.00..1100.00 rows=1100 width=68) (actual time=0.000..0.516 rows=1100
> loops=1)
>Output: wanted_capability_2.ordering,
> wanted_capability_2.name, wanted_capability_2.version
>-> 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)
>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..1.51 rows=1 width=1080) (actual time=6686.322..6686.322 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=7222
>-> Subquery Scan on "*SELECT*" (cost=0.00..1.51 rows=1
> width=1080) (actual time=6686.321..6686.321 rows=0 loops=1)
> Output: "*SELECT*".nextval, "*SELECT*".name,
> "*SELECT*".version, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
> Buffers: shared hit=7222
> -> CTE Scan on missing_capability (cost=0.00..1.00
> rows=1 width=72) (actual time=6686.320..6686.320 rows=0 loops=1)
>Output:
> nextval('rhn_pkg_capability_id_seq'::regclass), missing_capability.name,
> missing_capability.version
>Buffers: shared hit=7222
>-> Sort (cost=304391.82..304391.83 rows=2 width=36) (actual
> time=13360.240..13360.283 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=14448
> -> Append (cost=1.50..304391.81 rows=2 width=36) (actual
> time=13357.167..13360.051 rows=1100 loops=1)
>Buffers: shared hit=14442
>-> Hash Join (cost=1.50..1108.64 rows=1 width=36) (actual
> time=6686.340..6686.340 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=7225
> -> CTE Scan on wanted_capability
> (cost=0.00..1100.00 rows=1100 width=68) (a
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
Re: Increasing work_mem slows down query, why?
po 30. 3. 2020 v 15:09 odesílatel Silvio Moioli napsal:
> 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_capabilit
Re: Increasing work_mem slows down query, why?
> > 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. > if you have SSD, then you can decrease RANDOM_PAGE_COST to 2 maybe 1.5. But probably there will not be impact to this query. > Thanks! > > Regards, > -- > Silvio Moioli > SUSE Manager Development Team > > >
Re: Increasing work_mem slows down query, why?
Pavel Stehule writes: > po 30. 3. 2020 v 10:12 odesílatel Silvio Moioli 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
Re: Increasing work_mem slows down query, why?
po 30. 3. 2020 v 18:02 odesílatel Tom Lane napsal: > Pavel Stehule writes: > > po 30. 3. 2020 v 10:12 odesílatel Silvio Moioli 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? > There is another interesting thing -> 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 CTE scan has only 1100 rows, public.rhnpackagecapability has 490964 rows. But planner does hash from public.rhnpackagecapability table. It cannot be very effective. Pavel > regards, tom lane >
Re: Increasing work_mem slows down query, why?
Pavel Stehule writes: > CTE scan has only 1100 rows, public.rhnpackagecapability has 490964 rows. > But planner does hash from public.rhnpackagecapability table. It cannot be > very effective. [ shrug... ] Without stats on the CTE output, the planner is very leery of putting it on the inside of a hash join. The CTE might produce output that ends up in just a few hash buckets, degrading the join to something not much better than a nested loop. As long as there's enough memory to hash the known-well-distributed table, putting it on the inside is safer and no costlier. regards, tom lane
Re: Increasing work_mem slows down query, why?
po 30. 3. 2020 v 18:36 odesílatel Tom Lane napsal: > Pavel Stehule writes: > > CTE scan has only 1100 rows, public.rhnpackagecapability has 490964 > rows. > > But planner does hash from public.rhnpackagecapability table. It cannot > be > > very effective. > > [ shrug... ] Without stats on the CTE output, the planner is very > leery of putting it on the inside of a hash join. The CTE might > produce output that ends up in just a few hash buckets, degrading > the join to something not much better than a nested loop. As long > as there's enough memory to hash the known-well-distributed table, > putting it on the inside is safer and no costlier. > ok Regards Pavel > regards, tom lane >
