I did some digging into this query because it is one that is very obviously
slow when working with real systems.
It seems that the left join to rhnchecksum is the culprit of this slow query.
Here's the effect on execution time by changing it to an inner join:
LEFT JOIN: 2973.886 ms
INNER JOIN: 1.444 ms
Based on what this query is used for and the columns we're selecting, this
seems safe to me; the inner join is just going to exclude rows that don't exist
in both, but that should never happen under normal operation. I have not
tested this on Oracle, but have verified that client operations are still
operating normally. The real world effect is that this takes a ~50s
'rhncfg-client diff' on the client down to ~3s. I tested by updating the query
in /usr/share/rhn/server/handlers/config/rhn_config_management.py.
Jan,
What's your take on this?
Details:
## LEFT JOIN rhnChecksumView:
explain analyze select E'/etc/ssh/sshd_config' path,
cc.label config_channel,
ccont.contents file_contents,
ccont.is_binary is_binary,
c.checksum_type,
c.checksum,
ccont.delim_start, ccont.delim_end,
cr.revision,
cf.modified,
ci.username,
ci.groupname,
ci.filemode,
cft.label,
cct.priority,
ci.selinux_ctx,
case
when cft.label='symlink' then (select path from rhnConfigFileName where id =
ci.SYMLINK_TARGET_FILENAME_ID)
else ''
end as symlink
from rhnConfigChannel cc,
rhnConfigInfo ci,
rhnConfigRevision cr
left join rhnConfigContent ccont
on cr.config_content_id = ccont.id
left join rhnChecksumView c
on ccont.checksum_id = c.id,
rhnServerConfigChannel scc,
rhnConfigFile cf,
rhnConfigFileType cft,
rhnConfigChannelType cct
where scc.server_id = 1000010017
and scc.config_channel_id = cc.id
and cf.config_channel_id = cc.id
and cf.config_file_name_id = lookup_config_filename(E'/etc/ssh/sshd_config')
and cr.config_file_id = cf.id
and cr.config_info_id = ci.id
and cf.latest_config_revision_id = cr.id
and cr.config_file_type_id = cft.id
and cct.id = cc.confchan_type_id
order by cct.priority, scc.position;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=154053.71..154053.72 rows=1 width=886) (actual
time=2973.534..2973.534 rows=1 loops=1)
Sort Key: cct.priority, scc."position"
Sort Method: quicksort Memory: 26kB
-> Nested Loop (cost=130288.39..154053.70 rows=1 width=886) (actual
time=2665.864..2973.483 rows=1 loops=1)
-> Nested Loop (cost=130288.39..154051.84 rows=1 width=812) (actual
time=2665.848..2973.465 rows=1 loops=1)
-> Nested Loop (cost=130288.39..154051.23 rows=1 width=812)
(actual time=2665.820..2973.436 rows=1 loops=1)
Join Filter: (cc.confchan_type_id = cct.id)
-> Hash Join (cost=130288.39..154050.16 rows=1
width=813) (actual time=2665.791..2973.405 rows=1 loops=1)
Hash Cond: ((cr.config_file_id = cf.id) AND (cr.id =
cf.latest_config_revision_id))
-> Hash Left Join (cost=130284.93..154046.41
rows=36 width=793) (actual time=2611.257..2973.268 rows=36 loops=1)
Hash Cond: (ccont.checksum_id = c.id)
-> Hash Left Join (cost=8.79..10.64 rows=36
width=751) (actual time=0.197..0.246 rows=36 loops=1)
Hash Cond: (cr.config_content_id =
ccont.id)
-> Seq Scan on rhnconfigrevision cr
(cost=0.00..1.36 rows=36 width=42) (actual time=0.009..0.016 rows=36 loops=1)
-> Hash (cost=8.35..8.35 rows=35
width=723) (actual time=0.170..0.170 rows=35 loops=1)
-> Seq Scan on rhnconfigcontent
ccont (cost=0.00..8.35 rows=35 width=723) (actual time=0.015..0.073 rows=35
loops=1)
-> Hash (cost=78887.89..78887.89
rows=2210980 width=59) (actual time=2610.697..2610.697 rows=2211051 loops=1)
-> Hash Join (cost=1.11..78887.89
rows=2210980 width=59) (actual time=0.055..1619.600 rows=2211051 loops=1)
Hash Cond: (c.checksum_type_id =
ct.id)
-> Seq Scan on rhnchecksum c
(cost=0.00..48485.80 rows=2210980 width=60) (actual time=0.021..245.576
rows=2211051 loops=1)
-> Hash (cost=1.05..1.05 rows=5
width=13) (actual time=0.015..0.015 rows=5 loops=1)
-> Seq Scan on
rhnchecksumtype ct (cost=0.00..1.05 rows=5 width=13) (actual time=0.007..0.008
rows=5 loops=1)
-> Hash (cost=3.45..3.45 rows=1 width=48) (actual
time=0.065..0.065 rows=1 loops=1)
-> Nested Loop (cost=0.00..3.45 rows=1
width=48) (actual time=0.051..0.057 rows=1 loops=1)
Join Filter: (cc.id =
scc.config_channel_id)
-> Nested Loop (cost=0.00..2.35 rows=1
width=55) (actual time=0.031..0.035 rows=1 loops=1)
Join Filter: (cc.id =
cf.config_channel_id)
-> Seq Scan on rhnconfigfile cf
(cost=0.00..1.26 rows=1 width=29) (actual time=0.020..0.022 rows=1 loops=1)
Filter: (config_file_name_id
= 8::numeric)
-> Seq Scan on rhnconfigchannel
cc (cost=0.00..1.04 rows=4 width=26) (actual time=0.004..0.004 rows=4 loops=1)
-> Seq Scan on rhnserverconfigchannel
scc (cost=0.00..1.06 rows=3 width=14) (actual time=0.013..0.015 rows=3 loops=1)
Filter: (scc.server_id =
1000010017::numeric)
-> Seq Scan on rhnconfigchanneltype cct (cost=0.00..1.03
rows=3 width=13) (actual time=0.019..0.019 rows=3 loops=1)
-> Index Scan using rhn_conffiletype_id_pk on rhnconfigfiletype
cft (cost=0.00..0.60 rows=1 width=14) (actual time=0.024..0.025 rows=1 loops=1)
Index Cond: (cft.id = cr.config_file_type_id)
-> Index Scan using rhn_confinfo_id_pk on rhnconfiginfo ci
(cost=0.00..0.60 rows=1 width=88) (actual time=0.010..0.011 rows=1 loops=1)
Index Cond: (ci.id = cr.config_info_id)
SubPlan 1
-> Seq Scan on rhnconfigfilename (cost=0.00..1.25 rows=1 width=20)
(never executed)
Filter: (id = $0)
Total runtime: 2973.886 ms
(41 rows)
## INNER JOIN rhnChecksumView:
explain analyze select E'/etc/ssh/sshd_config' path,
cc.label config_channel,
ccont.contents file_contents,
ccont.is_binary is_binary,
c.checksum_type,
c.checksum,
ccont.delim_start, ccont.delim_end,
cr.revision,
cf.modified,
ci.username,
ci.groupname,
ci.filemode,
cft.label,
cct.priority,
ci.selinux_ctx,
case
when cft.label='symlink' then (select path from rhnConfigFileName where id =
ci.SYMLINK_TARGET_FILENAME_ID)
else ''
end as symlink
from rhnConfigChannel cc,
rhnConfigInfo ci,
rhnConfigRevision cr
left join rhnConfigContent ccont
on cr.config_content_id = ccont.id
inner join rhnChecksumView c
on ccont.checksum_id = c.id,
rhnServerConfigChannel scc,
rhnConfigFile cf,
rhnConfigFileType cft,
rhnConfigChannelType cct
where scc.server_id = 1000010017
and scc.config_channel_id = cc.id
and cf.config_channel_id = cc.id
and cf.config_file_name_id = lookup_config_filename(E'/etc/ssh/sshd_config')
and cr.config_file_id = cf.id
and cr.config_info_id = ci.id
and cf.latest_config_revision_id = cr.id
and cr.config_file_type_id = cft.id
and cct.id = cc.confchan_type_id
order by cct.priority, scc.position;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=321.58..321.58 rows=1 width=886) (actual time=1.077..1.077 rows=1
loops=1)
Sort Key: cct.priority, scc."position"
Sort Method: quicksort Memory: 26kB
-> Nested Loop (cost=2.92..321.57 rows=1 width=886) (actual
time=0.987..1.029 rows=1 loops=1)
-> Nested Loop (cost=2.92..319.70 rows=1 width=812) (actual
time=0.961..1.002 rows=1 loops=1)
-> Nested Loop (cost=2.92..319.09 rows=1 width=812) (actual
time=0.929..0.969 rows=1 loops=1)
Join Filter: ((cf.id = cr.config_file_id) AND
(cf.latest_config_revision_id = cr.id))
-> Nested Loop (cost=0.00..4.52 rows=1 width=47) (actual
time=0.079..0.088 rows=1 loops=1)
Join Filter: (cc.id = scc.config_channel_id)
-> Nested Loop (cost=0.00..3.42 rows=1 width=54)
(actual time=0.057..0.064 rows=1 loops=1)
Join Filter: (cc.confchan_type_id = cct.id)
-> Nested Loop (cost=0.00..2.35 rows=1
width=55) (actual time=0.033..0.037 rows=1 loops=1)
Join Filter: (cc.id =
cf.config_channel_id)
-> Seq Scan on rhnconfigfile cf
(cost=0.00..1.26 rows=1 width=29) (actual time=0.020..0.021 rows=1 loops=1)
Filter: (config_file_name_id =
8::numeric)
-> Seq Scan on rhnconfigchannel cc
(cost=0.00..1.04 rows=4 width=26) (actual time=0.004..0.005 rows=4 loops=1)
-> Seq Scan on rhnconfigchanneltype cct
(cost=0.00..1.03 rows=3 width=13) (actual time=0.017..0.018 rows=3 loops=1)
-> Seq Scan on rhnserverconfigchannel scc
(cost=0.00..1.06 rows=3 width=14) (actual time=0.015..0.017 rows=3 loops=1)
Filter: (scc.server_id = 1000010017::numeric)
-> Hash Join (cost=2.92..314.03 rows=36 width=793)
(actual time=0.292..0.852 rows=36 loops=1)
Hash Cond: (ccont.id = cr.config_content_id)
-> Hash Join (cost=1.11..311.68 rows=35 width=765)
(actual time=0.216..0.744 rows=35 loops=1)
Hash Cond: (c.checksum_type_id = ct.id)
-> Nested Loop (cost=0.00..310.09 rows=35
width=766) (actual time=0.130..0.621 rows=35 loops=1)
-> Seq Scan on rhnconfigcontent ccont
(cost=0.00..8.35 rows=35 width=723) (actual time=0.021..0.053 rows=35 loops=1)
-> Index Scan using rhnchecksum_pk on
rhnchecksum c (cost=0.00..8.61 rows=1 width=60) (actual time=0.014..0.014
rows=1 loops=35)
Index Cond: (c.id =
ccont.checksum_id)
-> Hash (cost=1.05..1.05 rows=5 width=13)
(actual time=0.021..0.021 rows=5 loops=1)
-> Seq Scan on rhnchecksumtype ct
(cost=0.00..1.05 rows=5 width=13) (actual time=0.009..0.011 rows=5 loops=1)
-> Hash (cost=1.36..1.36 rows=36 width=42) (actual
time=0.054..0.054 rows=36 loops=1)
-> Seq Scan on rhnconfigrevision cr
(cost=0.00..1.36 rows=36 width=42) (actual time=0.011..0.026 rows=36 loops=1)
-> Index Scan using rhn_conffiletype_id_pk on rhnconfigfiletype
cft (cost=0.00..0.60 rows=1 width=14) (actual time=0.026..0.027 rows=1 loops=1)
Index Cond: (cft.id = cr.config_file_type_id)
-> Index Scan using rhn_confinfo_id_pk on rhnconfiginfo ci
(cost=0.00..0.60 rows=1 width=88) (actual time=0.020..0.021 rows=1 loops=1)
Index Cond: (ci.id = cr.config_info_id)
SubPlan 1
-> Seq Scan on rhnconfigfilename (cost=0.00..1.25 rows=1 width=20)
(never executed)
Filter: (id = $0)
Total runtime: 1.444 ms
(39 rows)
/aron
------------------------------
Message: 7
Date: Mon, 21 Nov 2011 10:41:06 +0000 (GMT)
From: John Hodrien <[email protected]>
To: "[email protected]" <[email protected]>
Subject: Re: [Spacewalk-list] Postgres spacewalk
Message-ID: <[email protected]>
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed
On Mon, 21 Nov 2011, Jan Pazdziora wrote:
> On Mon, Nov 21, 2011 at 10:13:38AM +0000, John Hodrien wrote:
>>>
>>> Do you have your database freshly analyzed, by the way?
>>
>> No. ANALYSE; or is there more to it?
>
> Not sure about ANALYSE but ANALYZE should work. You can do
>
> ANALYZE VERBOSE
>
> to see the progress.
Done.
jh
_______________________________________________
Spacewalk-list mailing list
[email protected]
https://www.redhat.com/mailman/listinfo/spacewalk-list
