embedded in often-executed plpgsql functions, for instance. Can you
identify which table the lseeks are issued against?
I wouldn't know how? I'm just using htop and "s" on the postgres process
to find these...
(Now, having said that, I don't see how that type of theory explains no
CPU load.
My bad sorry. I was relaying information from the guy administering the
server. It turns out that "no CPU load" really meant: only one of the
cores is being utilized. On a 16 core machine that looks like "no load"
but of course for the individual query still means 100%.
But you're really going to need to provide more info before
anyone can explain it, and finding out what the lseeks are on would be
one good step.)
I have attached two of the offending execution plans. Anything obviously
wrong with them?
thank you for looking into it!
Sören
"Insert (cost=51.21..51.24 rows=1 width=26) (actual time=0.016..0.016 rows=0
loops=1)"
" -> Subquery Scan on "*SELECT*" (cost=51.21..51.24 rows=1 width=26) (actual
time=0.014..0.014 rows=0 loops=1)"
" -> Sort (cost=51.21..51.22 rows=1 width=8) (actual time=0.014..0.014
rows=0 loops=1)"
" Sort Key: basic_blocks.id"
" Sort Method: quicksort Memory: 25kB"
" -> Nested Loop (cost=0.00..51.20 rows=1 width=8) (actual
time=0.010..0.010 rows=0 loops=1)"
" -> Nested Loop (cost=0.00..42.33 rows=1 width=20)
(actual time=0.010..0.010 rows=0 loops=1)"
" Join Filter: (bn_functions.address =
bn_function_views.function)"
" -> Index Scan using bn_functions_pkey on
bn_functions (cost=0.00..8.34 rows=1 width=8) (actual time=0.009..0.009 rows=0
loops=1)"
" Index Cond: (module_id = 82)"
" Filter: (type <> 'import'::function_type)"
" -> Index Scan using bn_function_views_module_id_idx
on bn_function_views (cost=0.00..33.77 rows=18 width=12) (never executed)"
" Index Cond: (bn_function_views.module_id = 82)"
" -> Index Scan using
ex_82_basic_blocks_parent_function_idx on ex_82_basic_blocks basic_blocks
(cost=0.00..8.63 rows=19 width=12) (never executed)"
" Index Cond: (basic_blocks.parent_function =
bn_functions.address)"
"Total runtime: 0.082 ms"
"Insert (cost=14581.76..19565.92 rows=11146 width=8) (actual
time=2426.967..2426.967 rows=0 loops=1)"
" -> Merge Join (cost=14581.76..19565.92 rows=11146 width=8) (actual
time=187.650..537.181 rows=166905 loops=1)"
" Merge Cond: (destination_function.function = callgraph.destination)"
" -> Index Scan using bn_function_nodes_module_id_function_idx on
bn_function_nodes destination_function (cost=0.00..4667.05 rows=43094
width=12) (actual time=0.068..15.326 rows=43346 loops=1)"
" Index Cond: (module_id = 94)"
" -> Sort (cost=14534.90..14643.30 rows=43360 width=12) (actual
time=187.365..225.911 rows=166905 loops=1)"
" Sort Key: callgraph.destination"
" Sort Method: quicksort Memory: 13968kB"
" -> Merge Join (cost=46.54..11195.29 rows=43360 width=12)
(actual time=0.016..121.661 rows=166905 loops=1)"
" Merge Cond: (source_function.function = callgraph.source)"
" -> Index Scan using
bn_function_nodes_module_id_function_idx on bn_function_nodes source_function
(cost=0.00..4667.05 rows=43094 width=12) (actual time=0.007..14.936 rows=41149
loops=1)"
" Index Cond: (module_id = 94)"
" -> Index Scan using ex_94_callgraph_source_idx on
ex_94_callgraph callgraph (cost=0.00..5773.07 rows=166905 width=16) (actual
time=0.006..60.455 rows=166905 loops=1)"
"Total runtime: 2434.736 ms"
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
