[noob] How to optimize this double pivot query? - Mailing list pgsql-sql

I have two tables that contain key-value data that I want to combine in pivoted form into a single result set. They are
relatedto two separate tables.<br /><br />The tables are: test_results, test_variables, metric_def, metadata_key. The
lattertwo tables are enum-like tables, basic descriptors of data stored in other tables. The former two tables are
basicallykey-value tables (with ids as well); these k-v tables are related to the latter two tables via foreign
keys.<br/><br />The following SQL takes about 11 seconds to run on a high-end laptop. The largest table is about 54k
records,pretty puny.<br /><br />Can someone provide a hint as to why this is so slow? Again, I am a noob to SQL, so the
SQLis probably poorly written.<br /><br />Thanks in advance,<br /><br />Bob<br /><br />select<br /><br />   
t.id_name,<br/>    max(t.begin_time) as begin_time,<br />    max(t.end_time) as end_time,<br />    <br />    max(case
when(m.id_name = 'package-version') then v.value end) as package_version,<br />     max(case when (m.id_name =
'database-vendor')then v.value end) as database_vendor,<br />    max(case when (m.id_name = 'bean-name') then v.value
end)as bean_name,<br />    max(case when (m.id_name = 'request-distribution') then v.value end) as
request_distribution,<br/>     max(case when (m.id_name = 'ycsb-workload') then v.value end) as ycsb_workload,<br />   
max(casewhen (m.id_name = 'record-count') then v.value end) as record_count,<br />    max(case when (m.id_name =
'transaction-engine-count')then v.value end) as transaction_engine_count,<br />     max(case when (m.id_name =
'transaction-engine-maxmem')then v.value end) as transaction_engine_maxmem,<br />    max(case when (m.id_name =
'storage-manager-count')then v.value end) as storage_manager_count,<br />     max(case when (m.id_name =
'test-instance-count')then v.value end) as test_instance_count,<br />    max(case when (m.id_name = 'operation-count')
thenv.value end) as operation_count,<br />    max(case when (m.id_name = 'update-percent') then v.value end) as
update_percent,<br/>     max(case when (m.id_name = 'thread-count') then v.value end) as thread_count,<br />    <br
/>   max(case when (d.id_name = 'tps') then r.value end) as tps,<br />    max(case when (d.id_name = 'Memory') then
r.valueend) as memory,<br />     max(case when (d.id_name = 'DiskWritten') then r.value end) as disk_written,<br />   
max(casewhen (d.id_name = 'PercentUserTime') then r.value end) as percent_user,<br />    max(case when (d.id_name =
'PercentCpuTime')then r.value end) as percent_cpu,<br />     max(case when (d.id_name = 'UserMilliseconds') then
r.valueend) as user_milliseconds,<br />    max(case when (d.id_name = 'YcsbUpdateLatencyMicrosecs') then r.value end)
asupdate_latency,<br />    max(case when (d.id_name = 'YcsbReadLatencyMicrosecs') then r.value end) as read_latency,<br
/>    max(case when (d.id_name = 'Updates') then r.value end) as updates,<br />    max(case when (d.id_name =
'Deletes')then r.value end) as deletes,<br />    max(case when (d.id_name = 'Inserts') then r.value end) as inserts,<br
/>    max(case when (d.id_name = 'Commits') then r.value end) as commits,<br />    max(case when (d.id_name =
'Rollbacks')then r.value end) as rollbacks,<br />    max(case when (d.id_name = 'Objects') then r.value end) as
objects,<br/>     max(case when (d.id_name = 'ObjectsCreated') then r.value end) as objects_created,<br />    max(case
when(d.id_name = 'FlowStalls') then r.value end) as flow_stalls,<br />    max(case when (d.id_name =
'NodeApplyPingTime')then r.value end) as node_apply_ping_time,<br />     max(case when (d.id_name = 'NodePingTime')
thenr.value end) as node_ping_time,<br />    max(case when (d.id_name = 'ClientCncts') then r.value end) as
client_connections,<br/>    max(case when (d.id_name = 'YcsbSuccessCount') then r.value end) as success_count,<br />
   max(case when (d.id_name = 'YcsbWarnCount') then r.value end) as warn_count,<br />    max(case when (d.id_name =
'YcsbFailCount')then r.value end) as fail_count<br />    <br />from test as t<br /><br />    left join test_results as
ron r.test_id = <a href="http://t.id">t.id</a><br />     left join test_variables as v on v.test_id = <a
href="http://t.id">t.id</a><br/>    left join metric_def as d on <a href="http://d.id">d.id</a> = r.metric_def_id<br
/>   left join metadata_key as m on <a href="http://m.id">m.id</a> = v.metadata_key_id<br /><br />group by t.id_name<br
/><br/>;<br /><br />"GroupAggregate  (cost=5.87..225516.43 rows=926 width=81)"<br />"  ->  Nested Loop Left Join 
(cost=5.87..53781.24rows=940964 width=81)"<br />"        ->  Nested Loop Left Join  (cost=1.65..1619.61 rows=17235
width=61)"<br/> "              ->  Index Scan using test_uc on test t  (cost=0.00..90.06 rows=926 width=36)"<br
/>"             ->  Hash Right Join  (cost=1.65..3.11 rows=19 width=29)"<br />"                    Hash Cond: (<a
href="http://m.id">m.id</a>= v.metadata_key_id)"<br /> "                    ->  Seq Scan on metadata_key m 
(cost=0.00..1.24rows=24 width=21)"<br />"                    ->  Hash  (cost=1.41..1.41 rows=19 width=16)"<br
/>"                         ->  Index Scan using test_variables_test_id_idx on test_variables v  (cost=0.00..1.41
rows=19width=16)"<br /> "                                Index Cond: (test_id = <a href="http://t.id">t.id</a>)"<br
/>"       ->  Hash Right Join  (cost=4.22..6.69 rows=55 width=28)"<br />"              Hash Cond: (<a
href="http://d.id">d.id</a>= r.metric_def_id)"<br /> "              ->  Seq Scan on metric_def d  (cost=0.00..1.71
rows=71width=20)"<br />"              ->  Hash  (cost=3.53..3.53 rows=55 width=16)"<br />"                    -> 
IndexScan using test_results_test_id_idx on test_results r  (cost=0.00..3.53 rows=55 width=16)"<br />
"                         Index Cond: (test_id = <a href="http://t.id">t.id</a>)"<br />