[Spacewalk-list] Postgres spacewalk
John Hodrien
J.H.Hodrien at leeds.ac.uk
Fri Nov 4 17:25:25 UTC 2011
On Fri, 4 Nov 2011, Jan Pazdziora wrote:
>
>> spaceschema-# and cf.config_file_name_id = lookup_config_filename(E'/var/lib/sss/db/cache_default.ldb')
>
> to use index on the rhnConfigFile (cf) table, yet it does not happen:
>
>> -> Seq Scan on rhnconfigfile cf (cost=0.00..28.25 rows=1 width=29) (actual time=0.544..2.820 rows=1 loops=1)
>> Filter: (config_file_name_id = lookup_config_filename('/var/lib/sss/db/cache_default.ldb'::character varying))
>
> I believe the cause of the problem is that the functions (including
> the lookup_* functions) are volatile by default in PostgreSQL and
> PostgreSQL does not seem to be willing to use them for index lookups.
> We will need to make them stable, or even immutable.
>
> Can you rewrite the lookup_config_filename to be stable, to see
> if it makes a difference?
When I said I didn't know postgres I really meant it....
After a quick read of the manual, I've just done:
ALTER FUNCTION lookup_config_filename(name_in character varying) IMMUTABLE;
But then I'm thinking that's just crap as it's not really immutable as it does an INSERT.
So I tried:
CREATE FUNCTION test_lookup_config_filename(name_in character varying) RETURNS numeric
LANGUAGE plpgsql STABLE
AS $$
DECLARE
name_id NUMERIC;
BEGIN
SELECT id
INTO name_id
FROM rhnConfigFileName
WHERE path = name_in;
RETURN name_id;
END; $$;
Then tried the same long query with the function substituted and the
performance is the same.
But I think that's a red herring. If I throw away the function and just use
the result (how much more immutable can you get than "28"):
EXPLAIN ANALYZE select E'/var/lib/sss/db/cache_default.ldb' 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 = 1000010132
and scc.config_channel_id = cc.id
and cf.config_channel_id = cc.id
and cf.config_file_name_id = 28
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
;
Sort (cost=122074.14..122074.15 rows=1 width=753) (actual time=3549.136..3549.136 rows=1 loops=1)
Sort Key: cct.priority, scc."position"
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=101598.13..122074.13 rows=1 width=753) (actual time=3305.805..3549.121 rows=1 loops=1)
-> Nested Loop (cost=101598.13..122070.09 rows=1 width=709) (actual time=3305.719..3549.032 rows=1 loops=1)
-> Nested Loop (cost=101598.13..122069.72 rows=1 width=709) (actual time=3305.694..3549.006 rows=1 loops=1)
Join Filter: (cc.confchan_type_id = cct.id)
-> Hash Join (cost=101598.13..122068.65 rows=1 width=710) (actual time=3305.674..3548.982 rows=1 loops=1)
Hash Cond: ((cr.config_file_id = cf.id) AND (cr.id = cf.latest_config_revision_id))
-> Hash Left Join (cost=101585.36..122054.87 rows=133 width=689) (actual time=3304.328..3548.643 rows=139 loops=1)
Hash Cond: (ccont.checksum_id = c.id)
-> Hash Left Join (cost=11.27..16.12 rows=133 width=629) (actual time=0.219..0.570 rows=139 loops=1)
Hash Cond: (cr.config_content_id = ccont.id)
-> Seq Scan on rhnconfigrevision cr (cost=0.00..3.33 rows=133 width=42) (actual time=0.002..0.059 rows=139 loops=1)
-> Hash (cost=10.01..10.01 rows=101 width=601) (actual time=0.206..0.206 rows=107 loops=1)
-> Seq Scan on rhnconfigcontent ccont (cost=0.00..10.01 rows=101 width=601) (actual time=0.004..0.077 rows=107 loops=1)
-> Hash (cost=61008.92..61008.92 rows=1610013 width=77) (actual time=3303.538..3303.538 rows=1610021 loops=1)
-> Hash Join (cost=1.11..61008.92 rows=1610013 width=77) (actual time=0.019..1888.710 rows=1610021 loops=1)
Hash Cond: (c.checksum_type_id = ct.id)
-> Seq Scan on rhnchecksum c (cost=0.00..38870.13 rows=1610013 width=78) (actual time=0.003..388.036 rows=1610021 loops=1)
-> Hash (cost=1.05..1.05 rows=5 width=13) (actual time=0.007..0.007 rows=5 loops=1)
-> Seq Scan on rhnchecksumtype ct (cost=0.00..1.05 rows=5 width=13) (actual time=0.002..0.004 rows=5 loops=1)
-> Hash (cost=12.76..12.76 rows=1 width=49) (actual time=0.095..0.095 rows=1 loops=1)
-> Nested Loop (cost=0.00..12.76 rows=1 width=49) (actual time=0.053..0.090 rows=1 loops=1)
-> Nested Loop (cost=0.00..4.47 rows=1 width=56) (actual time=0.027..0.062 rows=1 loops=1)
Join Filter: (cc.id = cf.config_channel_id)
-> Seq Scan on rhnconfigfile cf (cost=0.00..3.25 rows=1 width=29) (actual time=0.019..0.047 rows=1 loops=1)
Filter: (config_file_name_id = 28::numeric)
-> Seq Scan on rhnconfigchannel cc (cost=0.00..1.10 rows=10 width=27) (actual time=0.003..0.007 rows=10 loops=1)
-> Index Scan using rhn_servercc_sid_ccid_uq on rhnserverconfigchannel scc (cost=0.00..8.27 rows=1 width=14) (actual time=0.024..0.026 rows=1 loops=1)
Index Cond: ((scc.server_id = 1000010132::numeric) AND (scc.config_channel_id = cc.id))
-> Seq Scan on rhnconfigchanneltype cct (cost=0.00..1.03 rows=3 width=13) (actual time=0.004..0.005 rows=3 loops=1)
-> Index Scan using rhn_conffiletype_id_pk on rhnconfigfiletype cft (cost=0.00..0.36 rows=1 width=14) (actual time=0.016..0.016 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.36 rows=1 width=58) (actual time=0.006..0.007 rows=1 loops=1)
Index Cond: (ci.id = cr.config_info_id)
SubPlan 1
-> Seq Scan on rhnconfigfilename (cost=0.00..3.67 rows=1 width=27) (actual time=0.020..0.056 rows=1 loops=1)
Filter: (id = $0)
Total runtime: 3549.394 ms
(40 rows)
I'm entirely out of my depth on this one, I'll run it past someone else
locally in case that's any help, as I'm not able to contribute a whole lot on
this personally.
jh
More information about the Spacewalk-list
mailing list