slow query

[dangal]

Dear I have a question to ask you
I am having a slow problem with a query and I am seeing with the explain
that the current cost and time differ by 4 times
Postgres version 9.5.16 in centos 7.6
To try to solve this run the statistics to the table and the same problem
remains
It's a very big table 2 billion tuples
Do you have any idea what I can do to improve
Thank you very much for your time
Any data you need I can provide

I share a part of the explain

 Hash Right Join  (cost=4339.65..12172907.42 rows=886647 width=158)
(actual time=1906344.617..1963668.889 rows=3362294 loops=1)"
"Output: ba.bus_ent_inst_id_auto, ba.att_value_1,
ba.att_value_num_1, att_call.ent_inst_att_str_value,
att_nro.ent_inst_att_str_value, att_bis.ent_inst_att_str_value,
att_pad.ent_inst_att_str_value, att_manz.ent_inst_att_str_value, att_a
(...)"
"Hash Cond: ((att_barr.env_id = ba.env_id) AND
(att_barr.bus_ent_inst_id = ba.bus_ent_inst_id_auto))"
"Buffers: shared hit=5814458 read=1033324 dirtied=790"





--
Sent from: 
https://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html

Re: slow query

[Justin Pryzby]

On Fri, Apr 03, 2020 at 09:03:49AM -0700, dangal wrote:
> Dear I have a question to ask you
> I am having a slow problem with a query and I am seeing with the explain that 
> the current cost and time differ by 4 times

The "cost" is in arbitrary units, and the time is in units of milliseconds.
The cost is not an expected duration.

> Postgres version 9.5.16 in centos 7.6
> To try to solve this run the statistics to the table and the same problem
> remains
> It's a very big table 2 billion tuples
> Do you have any idea what I can do to improve
> Thank you very much for your time
> Any data you need I can provide

Please check here.
https://wiki.postgresql.org/wiki/Slow_Query_Questions

> I share a part of the explain

It's not very useful to see a fragment of it.

-- 
Justin

Re: slow query

[dangal]

Justin thank you very much for your answer, as you can also see the number of
rows differs a lot
I attach the complete explain, do not attach it because it is large

"HashAggregate  (cost=12640757.46..12713163.46 rows=385 width=720) (actual
time=1971962.023..1971962.155 rows=306 loops=1)"
"  Output: bi.bus_ent_inst_name_num, bi.att_value_num_7, bi.att_value_10,
((SubPlan 1)), ((SubPlan 2)), a2.ent_inst_att_str_value, ba.att_value_1,
depto2.att_value_1, loc2.att_value_1, att_call.ent_inst_att_str_value,
att_nro.ent_inst_att_str_value, att_bis (...)"
"  Group Key: bi.bus_ent_inst_name_num, bi.att_value_num_7, bi.att_value_10,
(SubPlan 1), (SubPlan 2), a2.ent_inst_att_str_value, ba.att_value_1,
depto2.att_value_1, loc2.att_value_1, att_call.ent_inst_att_str_value,
att_nro.ent_inst_att_str_value, att_bis. (...)"
"  Buffers: shared hit=5817744 read=1034292 dirtied=790, local hit=2"
"  ->  Nested Loop  (cost=4347.52..12640740.13 rows=385 width=720)
(actual time=1906401.083..1971959.176 rows=306 loops=1)"
"Output: bi.bus_ent_inst_name_num, bi.att_value_num_7,
bi.att_value_10, (SubPlan 1), (SubPlan 2), a2.ent_inst_att_str_value,
ba.att_value_1, depto2.att_value_1, loc2.att_value_1,
att_call.ent_inst_att_str_value, att_nro.ent_inst_att_str_value, att_b
(...)"
"Buffers: shared hit=5817744 read=1034292 dirtied=790, local hit=2"
"->  Hash Join  (cost=4346.94..12228344.41 rows=1427 width=704)
(actual time=1906372.468..1964409.907 rows=306 loops=1)"
"  Output: bi.bus_ent_inst_name_num, bi.att_value_num_7,
bi.att_value_10, ba.bus_ent_inst_id_auto, ba.att_value_1,
att_call.ent_inst_att_str_value, att_nro.ent_inst_att_str_value,
att_bis.ent_inst_att_str_value, att_pad.ent_inst_att_str_value, a (...)"
"  Hash Cond: (ba.att_value_num_1 =
(bi.bus_ent_inst_name_num)::numeric)"
"  Buffers: shared hit=5814458 read=1033324 dirtied=790, local
hit=2"
"  ->  Hash Right Join  (cost=4339.65..12172907.42
rows=886647 width=158) (actual time=1906344.617..1963668.889 rows=3362294
loops=1)"
"Output: ba.bus_ent_inst_id_auto, ba.att_value_1,
ba.att_value_num_1, att_call.ent_inst_att_str_value,
att_nro.ent_inst_att_str_value, att_bis.ent_inst_att_str_value,
att_pad.ent_inst_att_str_value, att_manz.ent_inst_att_str_value, att_a
(...)"
"Hash Cond: ((att_barr.env_id = ba.env_id) AND
(att_barr.bus_ent_inst_id = ba.bus_ent_inst_id_auto))"
"Buffers: shared hit=5814458 read=1033324 dirtied=790"
"->  Index Only Scan using ix_bus_ent_inst_attr_03 on
public.bus_ent_inst_attribute att_barr  (cost=0.83..1024093.06 rows=4508264
width=24) (actual time=10.435..52888.091 rows=4244011 loops=1)"
"  Output: att_barr.att_id,
att_barr.ent_inst_att_str_value, att_barr.env_id, att_barr.bus_ent_inst_id,
att_barr.reg_status"
"  Index Cond: (att_barr.att_id = 1115)"
"  Heap Fetches: 120577"
"  Buffers: shared hit=503194 read=31197 dirtied=5"
"->  Hash  (cost=11101039.12..11101039.12 rows=886647
width=146) (actual time=1906329.888..1906329.888 rows=3362294 loops=1)"
"  Output: ba.bus_ent_inst_id_auto, ba.att_value_1,
ba.env_id, ba.att_value_num_1, att_call.ent_inst_att_str_value,
att_nro.ent_inst_att_str_value, att_bis.ent_inst_att_str_value,
att_pad.ent_inst_att_str_value, att_manz.ent_inst_att (...)"
"  Buckets: 4194304 (originally 1048576)  Batches: 1
(originally 1)  Memory Usage: 396824kB"
"  Buffers: shared hit=5311264 read=1002127
dirtied=785"
"  ->  Hash Right Join 
(cost=10328938.09..11101039.12 rows=886647 width=146) (actual
time=1867557.718..1904218.946 rows=3362294 loops=1)"
"Output: ba.bus_ent_inst_id_auto,
ba.att_value_1, ba.env_id, ba.att_value_num_1,
att_call.ent_inst_att_str_value, att_nro.ent_inst_att_str_value,
att_bis.ent_inst_att_str_value, att_pad.ent_inst_att_str_value,
att_manz.ent_in (...)"
"Hash Cond: ((att_apt.env_id = ba.env_id)
AND (att_apt.bus_ent_inst_id = ba.bus_ent_inst_id_auto))"
"Buffers: shared hit=5311264 read=1002127
dirtied=785"
"->  Index Only Scan using
ix_bus_ent_inst_attr_03 on public.bus_ent_inst_attribute att_apt 
(cost=0.83..746958.06 rows=3287982 width=24) (actual time=0.091..32788.731
rows=3491599 loops=1)"
"  Output: att_apt.att_id,
att_apt.ent_inst_att_str_value, att_apt.env_id, att_apt.bus_ent_inst_id,
att_apt.reg_status"
"  Index Cond: (att_apt.att_id = 1113)"
"  Heap Fetches: 88910"
"  Buffers: shared hit=178090 read=25341
dirtied=5"
"->  Hash  (cost=

Re: Increasing work_mem slows down query, why?

[Silvio Moioli]

On 3/30/20 6:02 PM, Tom Lane wrote:
> 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?

LC_COLLATE is en_US.UTF-8, and I cannot really change that for the whole 
database. I could, in principle, use the "C" collation for this particular 
column, I tried that and it helps (time goes down from ~13s to ~500ms).

Nevertheless, adding an explicit new index on the column (CREATE INDEX 
rhn_pkg_cap_name ON rhnPackageCapability (name)) helps even more, with the 
query time going down to ~60ms, no matter work_mem.

So ultimately I think I am going to remove the custom cpu_tuple_cost parameter 
and add the index, unless you have different suggestions.

Thank you very much so far!

Regards,
--
Silvio Moioli
SUSE Manager Development Team