Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TIqxv-0001Uo-Nb for pgsql-sql@postgresql.org; Tue, 02 Oct 2012 01:08:03 +0000 Received: from smtp109.prem.mail.ac4.yahoo.com ([76.13.13.92]) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1TIqxq-0007DH-BS for pgsql-sql@postgresql.org; Tue, 02 Oct 2012 01:08:02 +0000 Received: (qmail 20146 invoked from network); 2 Oct 2012 01:07:55 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:From:To:References:In-Reply-To:Subject:Date:Message-ID:MIME-Version:Content-Type:X-Mailer:Thread-Index:Content-Language; b=Iu4Zj1I4XzNm7C2BrvCyzawGXJu4ptyXFunLp2ZaTn4Bsd/Cuy1Vk6uZWeJSWh8OetITobJOufykl3QCtAVgeDfYf61/LNfflrCH5ntBZvU8Bh/SNfIR/pgYE52WQ4Qfa8ydKm4TOZEtpzBx0YDvnWSZnLdQMPpAVSyWo2jZoQE= ; DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1349140075; bh=tOi3fakB+UpNOXNV7UlwrY9aycJNvC4+bsxM3UtX/4M=; h=X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:From:To:References:In-Reply-To:Subject:Date:Message-ID:MIME-Version:Content-Type:X-Mailer:Thread-Index:Content-Language; b=Q2sGHMg1e8cRiCckYVgNvg97dA3PCS6Elslpfl08hcuuqMQCCUCpbFp8DyV8OC5Mes77p9Vau+eJkOuPE38J7o5NuVdbFLBbiwlx5T/Mw+xvVl+KgYCAIVu6HmOmffbGa9LHWqeJemxniM7P/cgLQsWwffl5XQcFeFpbA0OiDeI= X-Yahoo-Newman-Property: ymail-3 X-YMail-OSG: ELFfNx8VM1nOmejj3oyp.brQJd0JF0tDNZTbWA449JsO6qt _Q4cGLl_ZB97OXhLbvW2GleHGM0oiiwugYkPkgx3.GCK2ekdCpM_siqNfLDq xX.93ieBaVfQHAvVsTevjgfyhQp0e8C2pr6Px5ZY3Itj4xhAjqDTBeAtOBIX EqGhg9kBXqtSSc77n6v1E..1y1mRL26Pda4MJHYSH1UsQNQ.fKU7zIsE212o itpsP0_RSndqPP0ClfzUbIa7tPi.aAXLeNZKfuZcVV9dGfeqTTT_0sTFdP3k LJMYUzXgEpWnsZ6OzL1iMA8GYp2vKT.n6r20ZMKroPhkeuv5xFAplylg0yW4 uq30lDSCH4yNbV2kcwio5bxiHcbQeiRS5O8QNQvlaEbC.Ni07xyN3vDynxmO oTlKCKAUtXYMK7aDUMEqhBr26t.9rsUXj5a_EThViSLdOWL02749WSIYjial QfUfVLCSIepqeJUsw.Mp1ZU9c.UB1VbbCQ.cvaHDFRHB.AFJKSyVTH859keZ icVf2YdCVYZoviG0Ad3alBXlFOg-- X-Yahoo-SMTP: mpGJl6eswBD2IBufoVEg0Pa8gg-- Received: from WolfDog (polobo@24.93.23.188 with login) by smtp109.prem.mail.ac4.yahoo.com with SMTP; 01 Oct 2012 18:07:55 -0700 PDT From: "David Johnston" To: "'Robert Buck'" , References: In-Reply-To: Subject: Re: [noob] How to optimize this double pivot query? Date: Mon, 1 Oct 2012 21:07:26 -0400 Message-ID: <001201cda03a$49a686d0$dcf39470$@yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0013_01CDA018.C2990580" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQFoulUghlgeyULjRuKZgQdV23F4Rphu+4ag Content-Language: en-us X-Pg-Spam-Score: -4.1 (----) X-Archive-Number: 201210/5 X-Sequence-Number: 36876 This is a multipart message in MIME format. ------=_NextPart_000_0013_01CDA018.C2990580 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable From: pgsql-sql-owner@postgresql.org = [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Robert Buck Sent: Monday, October 01, 2012 8:47 PM To: pgsql-sql@postgresql.org Subject: [SQL] [noob] How to optimize this double pivot query? =20 I have two tables that contain key-value data that I want to combine in = pivoted form into a single result set. They are related to two separate = tables. The tables are: test_results, test_variables, metric_def, metadata_key. = The latter two tables are enum-like tables, basic descriptors of data = stored in other tables. The former two tables are basically key-value = tables (with ids as well); these k-v tables are related to the latter = two tables via foreign keys. The following SQL takes about 11 seconds to run on a high-end laptop. = The largest table is about 54k records, pretty puny. Can someone provide a hint as to why this is so slow? Again, I am a noob = to SQL, so the SQL is probably poorly written. =20 Your query, while maybe not great, isn=E2=80=99t the cause of your = problem. It is the table schema, specifically the = =E2=80=9Ckey-value=E2=80=9D aspect, that is killing you. =20 You may want to try: =20 SELECT * FROM (SELECT id FROM =E2=80=A6) id_master NATURAL LEFT JOIN (SELECT id, field_value AS =E2=80=A6 FROM =E2=80=A6 = WHERE fieldtype =3D =E2=80=98=E2=80=99) f1 NATURAL LEFT JOIN (SELECT id, field_value AS =E2=80=A6 FROM =E2=80=A6 = WHERE fieldtype =3D =E2=80=98=E2=80=99) f2 [repeat one left join for every field; though you will then need to = decide if/how to deal with NULL =E2=80=93 not that you are currently = doing anything special anyway=E2=80=A6] =20 Mainly the above avoids the use of =E2=80=9Cmax()=E2=80=9D and instead = uses direct joins between the relevant tables. I have no clue whether = that will improve things but if you are going to lie in this bed you = should at least try different positions. =20 The better option is to educate yourself on better ways of constructing = the tables so that you do not have to write this kind of god-awful = query. In some cases key-value has merit but usually only when done in = moderation. Not for the entire database. You likely should simply have = a table that looks like the result of the query below. =20 As a second (not necessarily mutually exclusive) alternative: install = and use the hstore extension. =20 David J. =20 Thanks in advance, Bob select t.id_name, max(t.begin_time) as begin_time, max(t.end_time) as end_time, =20 max(case when (m.id_name =3D 'package-version') then v.value end) as = package_version, max(case when (m.id_name =3D 'database-vendor') then v.value end) as = database_vendor, max(case when (m.id_name =3D 'bean-name') then v.value end) as = bean_name, max(case when (m.id_name =3D 'request-distribution') then v.value = end) as request_distribution, max(case when (m.id_name =3D 'ycsb-workload') then v.value end) as = ycsb_workload, max(case when (m.id_name =3D 'record-count') then v.value end) as = record_count, max(case when (m.id_name =3D 'transaction-engine-count') then = v.value end) as transaction_engine_count, max(case when (m.id_name =3D 'transaction-engine-maxmem') then = v.value end) as transaction_engine_maxmem, max(case when (m.id_name =3D 'storage-manager-count') then v.value = end) as storage_manager_count, max(case when (m.id_name =3D 'test-instance-count') then v.value = end) as test_instance_count, max(case when (m.id_name =3D 'operation-count') then v.value end) as = operation_count, max(case when (m.id_name =3D 'update-percent') then v.value end) as = update_percent, max(case when (m.id_name =3D 'thread-count') then v.value end) as = thread_count, =20 max(case when (d.id_name =3D 'tps') then r.value end) as tps, max(case when (d.id_name =3D 'Memory') then r.value end) as memory, max(case when (d.id_name =3D 'DiskWritten') then r.value end) as = disk_written, max(case when (d.id_name =3D 'PercentUserTime') then r.value end) as = percent_user, max(case when (d.id_name =3D 'PercentCpuTime') then r.value end) as = percent_cpu, max(case when (d.id_name =3D 'UserMilliseconds') then r.value end) = as user_milliseconds, max(case when (d.id_name =3D 'YcsbUpdateLatencyMicrosecs') then = r.value end) as update_latency, max(case when (d.id_name =3D 'YcsbReadLatencyMicrosecs') then = r.value end) as read_latency, max(case when (d.id_name =3D 'Updates') then r.value end) as = updates, max(case when (d.id_name =3D 'Deletes') then r.value end) as = deletes, max(case when (d.id_name =3D 'Inserts') then r.value end) as = inserts, max(case when (d.id_name =3D 'Commits') then r.value end) as = commits, max(case when (d.id_name =3D 'Rollbacks') then r.value end) as = rollbacks, max(case when (d.id_name =3D 'Objects') then r.value end) as = objects, max(case when (d.id_name =3D 'ObjectsCreated') then r.value end) as = objects_created, max(case when (d.id_name =3D 'FlowStalls') then r.value end) as = flow_stalls, max(case when (d.id_name =3D 'NodeApplyPingTime') then r.value end) = as node_apply_ping_time, max(case when (d.id_name =3D 'NodePingTime') then r.value end) as = node_ping_time, max(case when (d.id_name =3D 'ClientCncts') then r.value end) as = client_connections, max(case when (d.id_name =3D 'YcsbSuccessCount') then r.value end) = as success_count, max(case when (d.id_name =3D 'YcsbWarnCount') then r.value end) as = warn_count, max(case when (d.id_name =3D 'YcsbFailCount') then r.value end) as = fail_count =20 from test as t left join test_results as r on r.test_id =3D t.id left join test_variables as v on v.test_id =3D t.id left join metric_def as d on d.id =3D r.metric_def_id left join metadata_key as m on m.id =3D v.metadata_key_id group by t.id_name ; "GroupAggregate (cost=3D5.87..225516.43 rows=3D926 width=3D81)" " -> Nested Loop Left Join (cost=3D5.87..53781.24 rows=3D940964 = width=3D81)" " -> Nested Loop Left Join (cost=3D1.65..1619.61 rows=3D17235 = width=3D61)" " -> Index Scan using test_uc on test t = (cost=3D0.00..90.06 rows=3D926 width=3D36)" " -> Hash Right Join (cost=3D1.65..3.11 rows=3D19 = width=3D29)" " Hash Cond: (m.id =3D v.metadata_key_id)" " -> Seq Scan on metadata_key m (cost=3D0.00..1.24 = rows=3D24 width=3D21)" " -> Hash (cost=3D1.41..1.41 rows=3D19 width=3D16)" " -> Index Scan using = test_variables_test_id_idx on test_variables v (cost=3D0.00..1.41 = rows=3D19 width=3D16)" " Index Cond: (test_id =3D t.id)" " -> Hash Right Join (cost=3D4.22..6.69 rows=3D55 width=3D28)" " Hash Cond: (d.id =3D r.metric_def_id)" " -> Seq Scan on metric_def d (cost=3D0.00..1.71 = rows=3D71 width=3D20)" " -> Hash (cost=3D3.53..3.53 rows=3D55 width=3D16)" " -> Index Scan using test_results_test_id_idx on = test_results r (cost=3D0.00..3.53 rows=3D55 width=3D16)" " Index Cond: (test_id =3D t.id)" ------=_NextPart_000_0013_01CDA018.C2990580 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

From:= = pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] = On Behalf Of Robert Buck
Sent: Monday, October 01, 2012 = 8:47 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] = [noob] How to optimize this double pivot = query?

 

I have two tables that contain = key-value data that I want to combine in pivoted form into a single = result set. They are related to two separate tables.

The tables = are: test_results, test_variables, metric_def, metadata_key. The latter = two tables are enum-like tables, basic descriptors of data stored in = other tables. The former two tables are basically key-value tables (with = ids as well); these k-v tables are related to the latter two tables via = foreign keys.

The following SQL takes about 11 seconds to run on = a high-end laptop. The largest table is about 54k records, pretty = puny.

Can someone provide a hint as to why this is so slow? = Again, I am a noob to SQL, so the SQL is probably poorly = written.

 

Your query, while maybe not great, isn=E2=80=99t the cause of your = problem.=C2=A0 It is the table schema, specifically the = =E2=80=9Ckey-value=E2=80=9D aspect, that is killing = you.

 

You may want to try:

 

SELECT *

FROM (SELECT id FROM =E2=80=A6) id_master

NATURAL LEFT JOIN (SELECT id, field_value AS =E2=80=A6 FROM =E2=80=A6 = WHERE fieldtype =3D =E2=80=98=E2=80=99) f1

NATURAL LEFT JOIN (SELECT id, field_value AS =E2=80=A6 FROM =E2=80=A6 = WHERE fieldtype =3D =E2=80=98=E2=80=99) f2

[repeat one left join for every field; though you will then need to = decide if/how to deal with NULL =E2=80=93 not that you are currently = doing anything special anyway=E2=80=A6]

 

Mainly the above avoids the use of =E2=80=9Cmax()=E2=80=9D and = instead uses direct joins between the relevant tables.=C2=A0 I have no = clue whether that will improve things but if you are going to lie in = this bed you should at least try different = positions.

 

The better option is to educate yourself on better ways of = constructing the tables so that you do not have to write this kind of = god-awful query.=C2=A0 In some cases key-value has merit but usually = only when done in moderation.=C2=A0 Not for the entire database.=C2=A0 = You likely should simply have a table that looks like the result of the = query below.

 

As a second (not necessarily mutually exclusive) alternative: install = and use the hstore extension.

 

David J.

 


Thanks in = advance,

Bob

select

    = t.id_name,
    max(t.begin_time) as = begin_time,
    max(t.end_time) as = end_time,
   
    max(case when = (m.id_name =3D 'package-version') then v.value end) as = package_version,
    max(case when (m.id_name =3D = 'database-vendor') then v.value end) as = database_vendor,
    max(case when (m.id_name =3D = 'bean-name') then v.value end) as bean_name,
    = max(case when (m.id_name =3D 'request-distribution') then v.value end) = as request_distribution,
    max(case when (m.id_name = =3D 'ycsb-workload') then v.value end) as = ycsb_workload,
    max(case when (m.id_name =3D = 'record-count') then v.value end) as record_count,
    = max(case when (m.id_name =3D 'transaction-engine-count') then v.value = end) as transaction_engine_count,
    max(case when = (m.id_name =3D 'transaction-engine-maxmem') then v.value end) as = transaction_engine_maxmem,
    max(case when = (m.id_name =3D 'storage-manager-count') then v.value end) as = storage_manager_count,
    max(case when (m.id_name = =3D 'test-instance-count') then v.value end) as = test_instance_count,
    max(case when (m.id_name =3D = 'operation-count') then v.value end) as = operation_count,
    max(case when (m.id_name =3D = 'update-percent') then v.value end) as = update_percent,
    max(case when (m.id_name =3D = 'thread-count') then v.value end) as thread_count,
    =
    max(case when (d.id_name =3D 'tps') then r.value = end) as tps,
    max(case when (d.id_name =3D = 'Memory') then r.value end) as memory,
    max(case = when (d.id_name =3D 'DiskWritten') then r.value end) as = disk_written,
    max(case when (d.id_name =3D = 'PercentUserTime') then r.value end) as = percent_user,
    max(case when (d.id_name =3D = 'PercentCpuTime') then r.value end) as = percent_cpu,
    max(case when (d.id_name =3D = 'UserMilliseconds') then r.value end) as = user_milliseconds,
    max(case when (d.id_name =3D = 'YcsbUpdateLatencyMicrosecs') then r.value end) as = update_latency,
    max(case when (d.id_name =3D = 'YcsbReadLatencyMicrosecs') then r.value end) as = read_latency,
    max(case when (d.id_name =3D = 'Updates') then r.value end) as updates,
    max(case = when (d.id_name =3D 'Deletes') then r.value end) as = deletes,
    max(case when (d.id_name =3D 'Inserts') = then r.value end) as inserts,
    max(case when = (d.id_name =3D 'Commits') then r.value end) as = commits,
    max(case when (d.id_name =3D 'Rollbacks') = then r.value end) as rollbacks,
    max(case when = (d.id_name =3D 'Objects') then r.value end) as = objects,
    max(case when (d.id_name =3D = 'ObjectsCreated') then r.value end) as = objects_created,
    max(case when (d.id_name =3D = 'FlowStalls') then r.value end) as flow_stalls,
    = max(case when (d.id_name =3D 'NodeApplyPingTime') then r.value end) as = node_apply_ping_time,
    max(case when (d.id_name =3D = 'NodePingTime') then r.value end) as = node_ping_time,
    max(case when (d.id_name =3D = 'ClientCncts') then r.value end) as = client_connections,
    max(case when (d.id_name =3D = 'YcsbSuccessCount') then r.value end) as = success_count,
    max(case when (d.id_name =3D = 'YcsbWarnCount') then r.value end) as warn_count,
    = max(case when (d.id_name =3D 'YcsbFailCount') then r.value end) as = fail_count
   
from test as = t

    left join test_results as r on r.test_id =3D = t.id
    left join = test_variables as v on v.test_id =3D t.id
    left join metric_def = as d on d.id =3D = r.metric_def_id
    left join metadata_key as m on m.id =3D v.metadata_key_id

group by = t.id_name

;

"GroupAggregate  = (cost=3D5.87..225516.43 rows=3D926 width=3D81)"
"  = ->  Nested Loop Left Join  (cost=3D5.87..53781.24 = rows=3D940964 = width=3D81)"
"        = ->  Nested Loop Left Join  (cost=3D1.65..1619.61 = rows=3D17235 = width=3D61)"
"       &nbs= p;      ->  Index Scan using test_uc on = test t  (cost=3D0.00..90.06 rows=3D926 = width=3D36)"
"       &nbs= p;      ->  Hash Right Join  = (cost=3D1.65..3.11 rows=3D19 = width=3D29)"
"       &nbs= p;            = Hash Cond: (m.id =3D = v.metadata_key_id)"
"      &nb= sp;           &nbs= p; ->  Seq Scan on metadata_key m  (cost=3D0.00..1.24 = rows=3D24 = width=3D21)"
"       &nbs= p;            = ->  Hash  (cost=3D1.41..1.41 rows=3D19 = width=3D16)"
"       &nbs= p;            = ;      ->  Index Scan using = test_variables_test_id_idx on test_variables v  (cost=3D0.00..1.41 = rows=3D19 = width=3D16)"
"       &nbs= p;            = ;            = Index Cond: (test_id =3D t.id)"
"    &nb= sp;   ->  Hash Right Join  (cost=3D4.22..6.69 = rows=3D55 = width=3D28)"
"       &nbs= p;      Hash Cond: (d.id =3D = r.metric_def_id)"
"       = ;       ->  Seq Scan on metric_def = d  (cost=3D0.00..1.71 rows=3D71 = width=3D20)"
"       &nbs= p;      ->  Hash  = (cost=3D3.53..3.53 rows=3D55 = width=3D16)"
"       &nbs= p;            = ->  Index Scan using test_results_test_id_idx on test_results = r  (cost=3D0.00..3.53 rows=3D55 = width=3D16)"
"       &nbs= p;            = ;      Index Cond: (test_id =3D t.id)"

------=_NextPart_000_0013_01CDA018.C2990580--