Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TIrIN-0000Rl-NK for pgsql-sql@postgresql.org; Tue, 02 Oct 2012 01:29:13 +0000 Received: from nm14-vm0.bullet.mail.bf1.yahoo.com ([98.139.213.164]) by makus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1TIrIH-0003yL-VY for pgsql-sql@postgresql.org; Tue, 02 Oct 2012 01:29:08 +0000 Received: from [98.139.212.151] by nm14.bullet.mail.bf1.yahoo.com with NNFMP; 02 Oct 2012 01:28:49 -0000 Received: from [98.139.212.202] by tm8.bullet.mail.bf1.yahoo.com with NNFMP; 02 Oct 2012 01:28:49 -0000 Received: from [127.0.0.1] by omp1011.mail.bf1.yahoo.com with NNFMP; 02 Oct 2012 01:28:49 -0000 X-Yahoo-Newman-Id: 75939.48468.bm@omp1011.mail.bf1.yahoo.com Received: (qmail 10186 invoked from network); 2 Oct 2012 01:28:49 -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:References:In-Reply-To:Mime-Version:Content-Transfer-Encoding:Content-Type:Message-Id:Cc:X-Mailer:From:Subject:Date:To; b=GVv4/N02z8KKFdf+jOYOGuVIP9rVK360pgzl1f2wMms75ViimDhpcfoCptcBNqkOLNzrUqaWIkrxJxMsyUOUiAV20r1fBlTSRmHLjn6nXFo9CPev3V5denp/J2BghCXW29Al9iSnnJnDg67IewN66qMe6y6lIlNab8qQsuL4o7o= ; DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1349141329; bh=WF/fFrznDcGya9arTtzpG/lXKlF8tH0znPoq8MtoX7I=; h=X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:References:In-Reply-To:Mime-Version:Content-Transfer-Encoding:Content-Type:Message-Id:Cc:X-Mailer:From:Subject:Date:To; b=nfI4Ooh+9zTw7k3N3fAqZvodHq4aqNUz8lWPWunFRhwd5iM80F1GmKp2UKxlV2liUzxlcdHmc2luXEe33A1hEjmDNf2ZTHG0vIoH9wfLvGx3Eyl96vf+eQ1puPlwFhsD80aPYcjmYfnKYnWIV0+9MsgobK7XKbl/vccMLzlS0lc= X-Yahoo-Newman-Property: ymail-3 X-YMail-OSG: pRxCczkVM1k2GmbDXm1Hb9whEYHWOopEyiFGjOrZWAcBMXu VNjEozgQ_AnsmMgKJo.PjpFpDWneUeS8N3yUe0ubZyjwUM7cvzVQBT9AaSh7 lB78LWYdGOwpVIcmSjfiT9Kqt41GC2mfSZoUmqS95PpeaXTPl4EiH1cG.eM7 kVAXtoLbntdEl3zbmt630JAaf8NmKD60wdmgcBVStksVlxFSvQ41eFS66DhK OBn_PxLHpmN.L1OKVI0ix9UvOyB6YNpdOHArEbTpT_maVM0iY4pHtcwWSoD8 HXEqp64FE6xra4QQt6T9iboV0YExNcuYJsFysw4uLuFMHUZ8de62yJ7gxmgS _RolOMbN597lxiWNU4.GDvF1NjhxdgJJ.IY6U8OJJqIzIBbSjAbNb0dYElTO .8adh2ajYm_pSsxqKVrTfxlVQupI0tju61Z1vHWbO2UBxYAwmOHfuLRv1a2A F2loOdVRbX002JvNmMIVI3TE4iDDnVoXsXBBMhzzpS93Q3f7uCJJ7jYGkt1o _wYdj_A.P8elSU5bI5JgGp2D4TCs- X-Yahoo-SMTP: mpGJl6eswBD2IBufoVEg0Pa8gg-- Received: from [192.12.17.101] (polobo@24.93.23.188 with xymcookie) by smtp115-mob.biz.mail.bf1.yahoo.com with SMTP; 01 Oct 2012 18:28:48 -0700 PDT References: <001201cda03a$49a686d0$dcf39470$@yahoo.com> In-Reply-To: Mime-Version: 1.0 (1.0) Content-Transfer-Encoding: 7bit Content-Type: multipart/alternative; boundary=Apple-Mail-5717AE7D-F9E2-4794-A813-DC3ED724CEB6 Message-Id: <27B4C937-48DB-411C-B04B-98EDE5528BCA@yahoo.com> Cc: "pgsql-sql@postgresql.org" X-Mailer: iPad Mail (9B206) From: David Johnston Subject: Re: [noob] How to optimize this double pivot query? Date: Mon, 1 Oct 2012 21:28:48 -0400 To: Robert Buck X-Pg-Spam-Score: -4.0 (----) X-Archive-Number: 201210/7 X-Sequence-Number: 36878 --Apple-Mail-5717AE7D-F9E2-4794-A813-DC3ED724CEB6 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Two issues... 1. I do not know how you acquire the data or who controls how/what is genera= ted 2. I do not know the primary means of using said data If you capture a new metric you generally have to change quite a few things t= o actually use it so you might as well just add a column as well. If you wa= nt to be able to at least capture unknown input and store it somewhere that i= s where the hstore extension comes in. In effect you should store unknown d= ata there until you decide to update the schema to actually make use of it. = In situations where you want to use it without altering the schema you norm= ally simply list the unknowns and as such should output a row-like structure= . The query you provide will need to be updated in the same way a physical tab= le would be. So just use a table. Or do not provide a consolidated/wide qu= ery. If both, then deal with the performance hit one time per id and create= a materialized view - basically insert the results of the query into a phys= ical table and for live usage query that table. This is a cache and comes w= ith all the benefits and downsides thereof. David J. On Oct 1, 2012, at 21:13, Robert Buck wrote: > So as you can probably glean, the tables store performance metric data. Th= e reason I chose to use k-v is simply to avoid having to create an additiona= l column every time a new metric type come along. So those were the two opti= ons I thought of, straight k-v and column for every value type. >=20 > Are there other better options worth considering that you could point me t= owards that supports storing metrics viz. with an unbounded number of metric= types in my case? >=20 > Bob >=20 > On Mon, Oct 1, 2012 at 9:07 PM, David Johnston wrote: > From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.or= g] 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 > =20 >=20 > I have two tables that contain key-value data that I want to combine in pi= voted form into a single result set. They are related to two separate tables= . >=20 > The tables are: test_results, test_variables, metric_def, metadata_key. Th= e latter two tables are enum-like tables, basic descriptors of data stored i= n other tables. The former two tables are basically key-value tables (with i= ds as well); these k-v tables are related to the latter two tables via forei= gn keys. >=20 > The following SQL takes about 11 seconds to run on a high-end laptop. The l= argest table is about 54k records, pretty puny. >=20 > Can someone provide a hint as to why this is so slow? Again, I am a noob t= o SQL, so the SQL is probably poorly written. >=20 >=20 > =20 >=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 aspe= ct, that is killing you. >=20 > =20 >=20 > You may want to try: >=20 > =20 >=20 > SELECT * >=20 > FROM (SELECT id FROM =E2=80=A6) id_master >=20 > NATURAL LEFT JOIN (SELECT id, field_value AS =E2=80=A6 FROM =E2=80=A6 WHER= E fieldtype =3D =E2=80=98=E2=80=99) f1 >=20 > NATURAL LEFT JOIN (SELECT id, field_value AS =E2=80=A6 FROM =E2=80=A6 WHER= E fieldtype =3D =E2=80=98=E2=80=99) f2 >=20 > [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 anythin= g special anyway=E2=80=A6] >=20 > =20 >=20 > Mainly the above avoids the use of =E2=80=9Cmax()=E2=80=9D and instead use= s direct joins between the relevant tables. I have no clue whether that wil= l improve things but if you are going to lie in this bed you should at least= try different positions. >=20 > =20 >=20 > The better option is to educate yourself on better ways of constructing th= e tables so that you do not have to write this kind of god-awful query. In s= ome 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 l= ike the result of the query below. >=20 > =20 >=20 > As a second (not necessarily mutually exclusive) alternative: install and u= se the hstore extension. >=20 > =20 >=20 > David J. >=20 > =20 >=20 >=20 > Thanks in advance, >=20 > Bob >=20 > select >=20 > 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 p= ackage_version, > max(case when (m.id_name =3D 'database-vendor') then v.value end) as d= atabase_vendor, > max(case when (m.id_name =3D 'bean-name') then v.value end) as bean_na= me, > 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 ycs= b_workload, > max(case when (m.id_name =3D 'record-count') then v.value end) as reco= rd_count, > max(case when (m.id_name =3D 'transaction-engine-count') then v.value e= nd) 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) a= s test_instance_count, > max(case when (m.id_name =3D 'operation-count') then v.value end) as o= peration_count, > max(case when (m.id_name =3D 'update-percent') then v.value end) as up= date_percent, > max(case when (m.id_name =3D 'thread-count') then v.value end) as thre= ad_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 p= ercent_user, > max(case when (d.id_name =3D 'PercentCpuTime') then r.value end) as pe= rcent_cpu, > max(case when (d.id_name =3D 'UserMilliseconds') then r.value end) as u= ser_milliseconds, > max(case when (d.id_name =3D 'YcsbUpdateLatencyMicrosecs') then r.valu= e end) as update_latency, > max(case when (d.id_name =3D 'YcsbReadLatencyMicrosecs') then r.value e= nd) 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 rollbac= ks, > 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 ob= jects_created, > max(case when (d.id_name =3D 'FlowStalls') then r.value end) as flow_s= talls, > 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 clien= t_connections, > max(case when (d.id_name =3D 'YcsbSuccessCount') then r.value end) as s= uccess_count, > max(case when (d.id_name =3D 'YcsbWarnCount') then r.value end) as war= n_count, > max(case when (d.id_name =3D 'YcsbFailCount') then r.value end) as fai= l_count > =20 > from test as t >=20 > 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 >=20 > group by t.id_name >=20 > ; >=20 > "GroupAggregate (cost=3D5.87..225516.43 rows=3D926 width=3D81)" > " -> Nested Loop Left Join (cost=3D5.87..53781.24 rows=3D940964 width=3D= 81)" > " -> Nested Loop Left Join (cost=3D1.65..1619.61 rows=3D17235 wid= th=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=3D2= 9)" > " Hash Cond: (m.id =3D v.metadata_key_id)" > " -> Seq Scan on metadata_key m (cost=3D0.00..1.24 ro= ws=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 w= idth=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)" >=20 >=20 --Apple-Mail-5717AE7D-F9E2-4794-A813-DC3ED724CEB6 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
Two issues...
1.= I do not know how you acquire the data or who controls how/what is generate= d
2. I do not know the primary means of using said data
=
If you capture a new metric you generally have to change quit= e a few things to actually use it so you might as well just add a column as w= ell.  If you want to be able to at least capture unknown input and stor= e it somewhere that is where the hstore extension comes in.  In effect y= ou should store unknown data there until you decide to update the schema to a= ctually make use of it.  In situations where you want to use it without= altering the schema you normally simply list the unknowns and as such shoul= d output a row-like structure.

The query you provid= e will need to be updated in the same way a physical table would be.  S= o just use a table.  Or do not provide a consolidated/wide query.  = ;If both, then deal with the performance hit one time per id and create a ma= terialized view - basically insert the results of the query into a physical t= able and for live usage query that table.  This is a cache and comes wi= th all the benefits and downsides thereof.

David J.=


On Oct 1, 2012, at 21:13, Robert Buc= k <buck.robert.j@gmail.com= > wrote:

So as you can probably glean, the tables store performance metric d= ata. The reason I chose to use k-v is simply to avoid having to create an ad= ditional column every time a new metric type come along. So those were the t= wo options I thought of, straight k-v and column for every value type.

Are there other better options worth considering that you could point me= towards that supports storing metrics viz. with an unbounded number of metr= ic types in my case?

Bob

On Mon, Oc= t 1, 2012 at 9:07 PM, David Johnston <polobo@yahoo.com> wrote:<= br>

From:= pgsql-s= ql-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.orgSubject: [SQL] [noob] How to optimize this double pivot query?

 

I h= ave 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. T= he latter two tables are enum-like tables, basic descriptors of data stored i= n other tables. The former two tables are basically key-value tables (with i= ds as well); these k-v tables are related to the latter two tables via forei= gn keys.

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

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

 

<= p class=3D"MsoNormal"> Your query, while maybe not great, isn=E2=80=99t t= he cause of your problem.  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 *<= /span>

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 i= d, 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 e= very 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 th= e 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 posit= ions.

 =

The better option is to= educate yourself on better ways of constructing the tables so that you do n= ot 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 entir= e database.  You likely should simply have a table that looks like the r= esult of the query below.

 =

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

 =

David J.<= /span>

 


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-v= ersion') then v.value end) as package_version,
    max(case when (m.id_name =3D 'database-vendor') then v.va= lue 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_di= stribution,
    max(case when (m.id_name =3D 'ycsb-workload') then v.valu= e 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 tr= ansaction_engine_count,
    max(case when (m.id_name =3D 'transaction-engine-maxmem')= then v.value end) as transaction_engine_maxmem,
    max(c= ase when (m.id_name =3D 'storage-manager-count') then v.value end) as storag= e_manager_count,
    max(case when (m.id_name =3D 'test-instance-count') then v= .value end) as test_instance_count,
    max(case when (m.i= d_name =3D 'operation-count') then v.value end) as operation_count,
 = ;   max(case when (m.id_name =3D 'update-percent') then v.value en= d) 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,
    m= ax(case when (d.id_name =3D 'Memory') then r.value end) as memory,
    max(case when (d.id_name =3D 'DiskWritten') then r.value e= nd) as disk_written,
    max(case when (d.id_name =3D 'Per= centUserTime') 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.v= alue end) as user_milliseconds,
    max(case when (d.id_na= me =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') t= hen 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_n= ame =3D 'Objects') then r.value end) as objects,
    max(case when (d.id_name =3D 'ObjectsCreated') then r.val= ue end) as objects_created,
    max(case when (d.id_name =3D= 'FlowStalls') then r.value end) as flow_stalls,
    max(c= ase 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 succe= ss_count,
    max(case when (d.id_name =3D 'YcsbWarnCount') then r.valu= e end) as warn_count,
    max(case when (d.id_name =3D 'Yc= sbFailCount') then r.value end) as fail_count
   
from= test as t

    left join test_results as r on r.test_i= d =3D t.id
    left join test_variables as v on v.test_id =3D t.id
    left join met= ric_def as d on d.id =3D r.met= ric_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..2= 25516.43 rows=3D926 width=3D81)"
"  ->  Nested Loop Left Joi= n  (cost=3D5.87..53781.24 rows=3D940964 width=3D81)"
"        ->  Nested Loop Left Joi= n  (cost=3D1.65..1619.61 rows=3D17235 width=3D61)"
"  &nbs= p;           ->  I= ndex 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=3D1= 9 width=3D29)"
"            &nb= sp;       Hash Cond: (m.id =3D v.metadata_key_id)"
"   &nbs= p;            &n= bsp;   ->  Seq Scan on metadata_key m  (cost=3D0.00..= 1.24 rows=3D24 width=3D21)"
"       &n= bsp;            ->=   Hash  (cost=3D1.41..1.41 rows=3D19 width=3D16)"
"            &nb= sp;             -= >  Index Scan using test_variables_test_id_idx on test_variables v&n= bsp; (cost=3D0.00..1.41 rows=3D19 width=3D16)"
"    &= nbsp;            = ;            &nb= sp;  Index Cond: (test_id =3D t.id)"
"        ->  Hash Right Join = ; (cost=3D4.22..6.69 rows=3D55 width=3D28)"
"    &nbs= p;         Hash Cond: (d.id =3D r.metric_def_id)"
"  =             ->&nbs= p; Seq Scan on metric_def d  (cost=3D0.00..1.71 rows=3D71 width=3D20)"<= br> "            &nb= sp; ->  Hash  (cost=3D3.53..3.53 rows=3D55 width=3D16)"
"&nb= sp;            &= nbsp;      ->  Index Scan using test_result= s_test_id_idx on test_results r  (cost=3D0.00..3.53 rows=3D55 width=3D1= 6)"
"           &n= bsp;            =   Index Cond: (test_id =3D t.= id)"


= --Apple-Mail-5717AE7D-F9E2-4794-A813-DC3ED724CEB6--