# Crunching the Crunched Numbers



## Aquinus (Nov 23, 2017)

Back over in the WCG 13th Birthday Challenge (11/16-11/22/2017)- Calling all crunchers!!! thread, @Norton had said:


Norton said:


> *Have a project for one of you web gurus.... *
> 
> SETI.Germany is offering code to setup a personal stats webpage that will read the database from WCG and allow a cruncher to view their stats in greater detail.
> 
> ...



I had poked at the APIs that IBM exposes and while they're not exactly well documented or consistent, I was able to pull apart the member API. First of all, you can't get older historical data for what work you've done in the past. IBM only exposes the last several days worth of results that have been processed. There comes a point where results become stale and no longer show up in the API. This requires a service that's capable of constantly checking and storing both new results and the difference on exists ones since the state of a result gets updated over time (time it was received, if it verified, it's status, etc.)

Once I figured out what was going on, I whipped out my handy go-to dev tools and went to work. I've made a basic schema in PostgreSQL and a small service that is capable of fetching the remote data, parsing it, and storing it in PostgreSQL. For me, the next step would be to turn it into a stream-based service based on the members stored in the database (which contains the username and verification code, both of which are required to fetch a member's stats.) That will be enough for a service that can run long-term and start building a historical database of the information coming out of WCG (IBM.) However, that will take time to gather enough data to be useful if any way. So, I would like to ask TPU's crunchers, what would you like to see from the data that gets gathered from all the crunching that you do?

Stats can be broken down obviously by date and time but, the "results" table in my database mimics the API in the sense that I capture everything that gets sent over the wire:

```
wcg=> \d wcg.results
                     Table "wcg.results"
      Column       |            Type             | Modifiers
-------------------+-----------------------------+-----------
 result-id         | bigint                      | not null
 member-id         | integer                     | not null
 app-id            | smallint                    | not null
 claimed-credit    | double precision            | not null
 cpu-time          | double precision            | not null
 elapsed-time      | double precision            | not null
 exit-status       | smallint                    | not null
 granted-credit    | double precision            | not null
 device-id         | integer                     | not null
 mod-time          | bigint                      | not null
 workunit-id       | bigint                      | not null
 name              | text                        | not null
 outcome           | smallint                    | not null
 received-time     | timestamp without time zone |
 report-deadline   | timestamp without time zone | not null
 sent-time         | timestamp without time zone | not null
 server-state      | smallint                    | not null
 validate-state    | smallint                    | not null
 file-delete-state | smallint                    | not null
Indexes:
    "results_pkey" PRIMARY KEY, btree ("result-id")
Foreign-key constraints:
    "app-fk" FOREIGN KEY ("app-id") REFERENCES apps("app-id")
    "device-fk" FOREIGN KEY ("device-id") REFERENCES devices("device-id")
    "member-fk" FOREIGN KEY ("member-id") REFERENCES members("member-id")

wcg=> select * from wcg.results limit 20;
 result-id  | member-id | app-id |  claimed-credit  |     cpu-time      |   elapsed-time    | exit-status |  granted-credit  | device-id |  mod-time  | workunit-id |                        name                         | outcome |    received-time    |   report-deadline   |      sent-time      | server-state | validate-state | file-delete-state
------------+-----------+--------+------------------+-------------------+-------------------+-------------+------------------+-----------+------------+-------------+-----------------------------------------------------+---------+---------------------+---------------------+---------------------+--------------+----------------+-------------------
 1944779575 |         2 |     10 |                0 |                 0 |                 0 |           0 |                0 |   4147721 | 1511446829 |   375352955 | ZIKA_000291302_x4mvn_Saur_SplApr_Inhib_chA_A_0398_1 |       0 |                     | 2017-12-03 09:20:29 | 2017-11-23 09:20:29 |            4 |              0 |                 0
 1944622928 |         2 |      5 |                0 |                 0 |                 0 |           0 |                0 |   4147721 | 1511442807 |   373622333 | OET1_0005179_x4GV3p_rig_28905_1                     |       0 |                     | 2017-12-03 08:13:27 | 2017-11-23 08:13:27 |            4 |              0 |                 0
 1944623069 |         2 |      5 |                0 |                 0 |                 0 |           0 |                0 |   4147721 | 1511442807 |   373621728 | OET1_0005179_x4GV3p_rig_23807_1                     |       0 |                     | 2017-12-03 08:13:27 | 2017-11-23 08:13:27 |            4 |              0 |                 0
 1940100016 |         2 |      9 | 113.678794959877 |  3.42911111111111 |  3.43268434694444 |           0 |                0 |   4147721 | 1511446829 |   376093930 | MCM1_0138293_1973_1                                 |       1 | 2017-11-23 09:20:29 | 2017-11-30 00:49:38 | 2017-11-23 00:49:38 |            5 |              0 |                 0
 1941690661 |         2 |      7 | 168.486486174654 |          6.002775 |  6.00404577805556 |           0 | 168.486486174654 |   4147721 | 1511450842 |   377230844 | FAH2_001911_avx17587-3_000003_000019_005_0          |       1 | 2017-11-23 10:27:16 | 2017-11-23 22:31:56 | 2017-11-22 22:31:56 |            5 |              1 |                 0
 1940659594 |         2 |      9 |                0 |                 0 |                 0 |           0 |                0 |   4147721 | 1511442128 |   376490337 | MCM1_0138300_5560_1                                 |       0 |                     | 2017-11-30 08:02:08 | 2017-11-23 08:02:08 |            4 |              0 |                 0
 1940474040 |         2 |      9 | 112.753212327416 |  3.39741944444444 |  3.39905427027778 |           0 |                0 |   4147721 | 1511458069 |   376358739 | MCM1_0138297_2767_0                                 |       1 | 2017-11-23 12:27:49 | 2017-11-30 04:42:42 | 2017-11-23 04:42:42 |            5 |              0 |                 0
 1944779602 |         2 |     10 |                0 |                 0 |                 0 |           0 |                0 |   4147721 | 1511446829 |   375353038 | ZIKA_000291303_x4mvn_Saur_SplApr_Inhib_chA_A_0196_1 |       0 |                     | 2017-12-03 09:20:29 | 2017-11-23 09:20:29 |            4 |              0 |                 0
 1940102407 |         2 |      9 | 112.552704046652 |  3.39271944444444 |        3.39701266 |           0 |                0 |   4147721 | 1511442807 |   376096095 | MCM1_0138293_1581_0                                 |       1 | 2017-11-23 08:13:27 | 2017-11-30 00:49:38 | 2017-11-23 00:49:38 |            5 |              0 |                 0
 1944779633 |         2 |     10 |                0 |                 0 |                 0 |           0 |                0 |   4147721 | 1511446829 |   375353024 | ZIKA_000291303_x4mvn_Saur_SplApr_Inhib_chA_A_0124_1 |       0 |                     | 2017-12-03 09:20:29 | 2017-11-23 09:20:29 |            4 |              0 |                 0
 1939997280 |         2 |      9 | 114.485224757391 |  3.45490833333333 |  3.45533908916667 |           0 | 114.388920818186 |   4147721 | 1511455369 |   376022608 | MCM1_0138291_7325_1                                 |       1 | 2017-11-23 08:13:27 | 2017-11-29 22:43:14 | 2017-11-22 22:43:14 |            5 |              1 |                 0
 1941887450 |         2 |      7 | 31.5086387184113 |  1.19480111111111 |  1.19673545694444 |           0 | 31.5086387184113 |   4147721 | 1511442813 |   377364209 | FAH2_001534_avx38743-1_000009_000085_007_0          |       1 | 2017-11-23 08:13:27 | 2017-11-24 01:13:54 | 2017-11-23 01:13:54 |            5 |              1 |                 0
 1940376831 |         2 |      9 | 112.894154366055 |  3.40276111111111 |  3.40330310388889 |           0 |                0 |   4147721 | 1511458069 |   376276524 | MCM1_0138296_1826_1                                 |       1 | 2017-11-23 12:27:49 | 2017-11-30 03:42:09 | 2017-11-23 03:42:09 |            5 |              0 |                 0
 1939862654 |         2 |      9 | 115.446118926124 |          3.478325 |  3.48295114305556 |           0 |                0 |   4147721 | 1511433982 |   375910450 | MCM1_0138290_2082_0                                 |       1 | 2017-11-23 05:46:22 | 2017-11-29 21:24:03 | 2017-11-22 21:24:03 |            5 |              0 |                 0
 1940379864 |         2 |      9 | 111.656539888297 |  3.36397222222222 |      3.3659940225 |           0 |                0 |   4147721 | 1511458069 |   376279210 | MCM1_0138296_0333_1                                 |       1 | 2017-11-23 12:27:49 | 2017-11-30 03:42:09 | 2017-11-23 03:42:09 |            5 |              0 |                 0
 1940695147 |         2 |      8 |                0 |                 0 |                 0 |           0 |                0 |   4147721 | 1511446829 |   376511213 | MIP1_00026328_0590_0                                |       0 |                     | 2017-12-03 09:20:29 | 2017-11-23 09:20:29 |            4 |              0 |                 0
 1936221814 |         2 |      5 | 49.0047204465838 | 0.882578055555556 | 0.883728396944444 |           0 |                0 |   4147721 | 1511426529 |   373321970 | OET1_0005178_x4GV3p_rig_36946_0                     |       1 | 2017-11-23 03:42:09 | 2017-12-02 22:31:56 | 2017-11-22 22:31:56 |            5 |              0 |                 0
 1939980888 |         2 |      9 | 114.041287003792 |  3.43873611111111 |      3.4415333825 |           0 |                0 |   4147721 | 1511442128 |   376009182 | MCM1_0138291_0595_0                                 |       1 | 2017-11-23 08:02:08 | 2017-11-29 22:31:56 | 2017-11-22 22:31:56 |            5 |              0 |                 0
 1940060297 |         2 |      9 | 113.045950913802 |  3.41115833333333 |  3.41189960444444 |           0 |                0 |   4147721 | 1511442807 |   376070391 | MCM1_0138292_6023_1                                 |       1 | 2017-11-23 08:13:27 | 2017-11-29 23:45:08 | 2017-11-22 23:45:08 |            5 |              0 |                 0
 1940137074 |         2 |      8 | 34.1818833143194 | 0.806429444444445 | 0.807077143888889 |           0 | 34.1818833143194 |   4147721 | 1511446838 |   376118938 | MIP1_00026200_0646_0                                |       1 | 2017-11-23 09:20:29 | 2017-12-03 02:26:13 | 2017-11-23 02:26:13 |            5 |              1 |                 0
(20 rows)
```

How would you like to see this data broken down and represented? What would like to get out of this information? Once I get the polling of data setup, I can get this running on the 3820 crunching in the attic and expose whatever I'm doing to all of you who are interested. Also, if you would like to donate your crunching statistics to the cause of science, I could use your username and verification code to make the API calls to watch your stat history as well.

Questions, comments, suggestions?


----------



## stinger608 (Nov 23, 2017)

One thing that I'd like to see is the separate systems each member has and see what kind of PPD he or she is getting. Don't know if it's possible, but to see each system overclocks, if any, as well as what operating system is being used.


----------



## Aquinus (Nov 23, 2017)

stinger608 said:


> One thing that I'd like to see is the separate systems each member has and see what kind of PPD he or she is getting. Don't know if it's possible, but to see each system overclocks, if any, as well as what operating system is being used.


I think that devices *might* have their own API but it's not documented and things like clock speeds will vary over time and aren't likely consistent even for a single result. Things like cores and memory should though but, that can still change for a single result so, I'm not sure. As far as the member stats API is concerned, all I get is an integer device id and a textual device name, likely the machine's hostname. These numbers can definitely be broken down by device though. What I thought was interesting is that they provide a validation status and how many points "claimed" by the client and how many actually get granted by WCG. In addition to breaking it down by device, it could show validated and invalidated results in case a device is unstable (no one wants their results thrown away!) So, I think there is a lot that can be derived from this information.

With that said though, there isn't a reason why this information couldn't be manually added by a user but, that would be down the road. Extracting information from APIs tends to be easier than designing and implementing interfaces for people to interact with.

Apparently most of the statistics pages have at least a XML version available just by adding an "xml=true" query parameter. Interesting information but, probably less useful than gathering result data itself since most of the statistic pages don't offer as granular information. This is probably handy if someone wanted to gather stats based on the team as a whole though.

```
<?xml version="1.0" encoding="UTF-8"?>
<TeamStats>
    <LastUpdated>2017-11-22T23:59:59</LastUpdated>
    <Team>
        <Name>TechPowerUp!</Name>
        <TeamId>S8TLJ6TFV1</TeamId>
        <Captain>Norton01</Captain>
        <URL>http://www.techpowerup.com</URL>
        <DateCreated>2009-02-20</DateCreated>
        <Description>Techpowerup.com</Description>
        <Type>Unclassified</Type>
        <BoincId>22175</BoincId>
    </Team>
    <StatisticsTotals>
        <CurrentMembers>672</CurrentMembers>
        <CurrentMembersRank>59</CurrentMembersRank>
        <RetiredMembers>73</RetiredMembers>
        <AllTimeMembers>745</AllTimeMembers>
        <AllTimeMembersRank>60</AllTimeMembersRank>
        <AllTimeDevices>5983</AllTimeDevices>
        <RunTime>289575552310</RunTime>
        <RunTimeRank>16</RunTimeRank>
        <Points>18004440186</Points>
        <PointsRank>11</PointsRank>
        <Results>42941430</Results>
        <ResultsRank>9</ResultsRank>
    </StatisticsTotals>
    <StatisticsAverages>
        <RunTimePerDay>90520648</RunTimePerDay>
        <RunTimePerResult>6743</RunTimePerResult>
        <PointsPerHourRunTime>223.830997308</PointsPerHourRunTime>
        <PointsPerDay>5628146.353860582</PointsPerDay>
        <PointsPerResult>419.27900831434005</PointsPerResult>
        <ResultsPerDay>13423.391684901531</ResultsPerDay>
    </StatisticsAverages>
    <TeamStatsByProjects>
        <Project>
            <ProjectName>Microbiome Immunity Project</ProjectName>
            <RunTime>1081367218</RunTime>
            <Points>56595813</Points>
            <Results>163659</Results>
        </Project>
        <Project>
            <ProjectName>Smash Childhood Cancer</ProjectName>
            <RunTime>4357771448</RunTime>
            <Points>249177603</Points>
            <Results>882952</Results>
        </Project>
        <Project>
            <ProjectName>OpenZika</ProjectName>
            <RunTime>9255776409</RunTime>
            <Points>449451719</Points>
            <Results>1234727</Results>
        </Project>
        <Project>
            <ProjectName>Help Stop TB</ProjectName>
            <RunTime>575731486</RunTime>
            <Points>30229247</Points>
            <Results>15631</Results>
        </Project>
        <Project>
            <ProjectName>FightAIDS@Home - Phase 2</ProjectName>
            <RunTime>11371397147</RunTime>
            <Points>525576329</Points>
            <Results>224606</Results>
        </Project>
        <Project>
            <ProjectName>Outsmart Ebola Together</ProjectName>
            <RunTime>49089508966</RunTime>
            <Points>2873147376</Points>
            <Results>11222806</Results>
        </Project>
        <Project>
            <ProjectName>Mapping Cancer Markers</ProjectName>
            <RunTime>86308592513</RunTime>
            <Points>4176264126</Points>
            <Results>5330461</Results>
        </Project>
        <Project>
            <ProjectName>FightAIDS@Home</ProjectName>
            <RunTime>40288962539</RunTime>
            <Points>1990703957</Points>
            <Results>8132631</Results>
        </Project>
        <Project>
            <ProjectName>Beta Testing</ProjectName>
            <RunTime>535223438</RunTime>
            <Points>25968932</Points>
            <Results>37473</Results>
        </Project>
        <Project>
            <ProjectName>Uncovering Genome Mysteries</ProjectName>
            <RunTime>9000944202</RunTime>
            <Points>428146212</Points>
            <Results>660738</Results>
        </Project>
        <Project>
            <ProjectName>Computing for Sustainable Water</ProjectName>
            <RunTime>897079173</RunTime>
            <Points>51646123</Points>
            <Results>196518</Results>
        </Project>
        <Project>
            <ProjectName>Say No to Schistosoma</ProjectName>
            <RunTime>4679698255</RunTime>
            <Points>231953234</Points>
            <Results>442701</Results>
        </Project>
        <Project>
            <ProjectName>GO Fight Against Malaria</ProjectName>
            <RunTime>5283018553</RunTime>
            <Points>255159157</Points>
            <Results>288355</Results>
        </Project>
        <Project>
            <ProjectName>Drug Search for Leishmaniasis</ProjectName>
            <RunTime>6431279603</RunTime>
            <Points>306928389</Points>
            <Results>345911</Results>
        </Project>
        <Project>
            <ProjectName>Computing for Clean Water</ProjectName>
            <RunTime>6415024793</RunTime>
            <Points>269607804</Points>
            <Results>593519</Results>
        </Project>
        <Project>
            <ProjectName>The Clean Energy Project - Phase 2</ProjectName>
            <RunTime>7207613574</RunTime>
            <Points>350003583</Points>
            <Results>303733</Results>
        </Project>
        <Project>
            <ProjectName>Discovering Dengue Drugs - Together - Phase 2</ProjectName>
            <RunTime>147240594</RunTime>
            <Points>6406141</Points>
            <Results>20552</Results>
        </Project>
        <Project>
            <ProjectName>Help Cure Muscular Dystrophy - Phase 2</ProjectName>
            <RunTime>10663696838</RunTime>
            <Points>465955813</Points>
            <Results>757353</Results>
        </Project>
        <Project>
            <ProjectName>Influenza Antiviral Drug Search</ProjectName>
            <RunTime>370159759</RunTime>
            <Points>12411160</Points>
            <Results>19778</Results>
        </Project>
        <Project>
            <ProjectName>Help Fight Childhood Cancer</ProjectName>
            <RunTime>9126781421</RunTime>
            <Points>364816261</Points>
            <Results>462818</Results>
        </Project>
        <Project>
            <ProjectName>The Clean Energy Project</ProjectName>
            <RunTime>85365447</RunTime>
            <Points>2810766</Points>
            <Results>3197</Results>
        </Project>
        <Project>
            <ProjectName>Nutritious Rice for the World</ProjectName>
            <RunTime>1998167499</RunTime>
            <Points>72071149</Points>
            <Results>79093</Results>
        </Project>
        <Project>
            <ProjectName>Help Conquer Cancer</ProjectName>
            <RunTime>15406653416</RunTime>
            <Points>4464368853</Points>
            <Results>11095413</Results>
        </Project>
        <Project>
            <ProjectName>Discovering Dengue Drugs - Together</ProjectName>
            <RunTime>222980332</RunTime>
            <Points>7810567</Points>
            <Results>12378</Results>
        </Project>
        <Project>
            <ProjectName>Human Proteome Folding - Phase 2</ProjectName>
            <RunTime>8775517687</RunTime>
            <Points>337229872</Points>
            <Results>414427</Results>
        </Project>
    </TeamStatsByProjects>
    <ResourceList>
        <Resource>
            <Url>https://www.worldcommunitygrid.org/team/challenge/viewTeamChallengeHistory.do?teamId=S8TLJ6TFV1&amp;listType=1&amp;xml=true</Url>
            <Description>Upcoming Challenges</Description>
        </Resource>
        <Resource>
            <Url>https://www.worldcommunitygrid.org/team/challenge/viewTeamChallengeHistory.do?teamId=S8TLJ6TFV1&amp;listType=2&amp;xml=true</Url>
            <Description>Current Challenges</Description>
        </Resource>
        <Resource>
            <Url>https://www.worldcommunitygrid.org/team/challenge/viewTeamChallengeHistory.do?teamId=S8TLJ6TFV1&amp;listType=3&amp;xml=true</Url>
            <Description>Past Challenges</Description>
        </Resource>
        <Resource>
            <Url>https://www.worldcommunitygrid.org/team/viewTeamMemberDetail.do?teamId=S8TLJ6TFV1&amp;sort=cpu&amp;xml=true</Url>
            <Description>Team Members by Run Time</Description>
        </Resource>
        <Resource>
            <Url>https://www.worldcommunitygrid.org/team/viewTeamMemberDetail.do?teamId=S8TLJ6TFV1&amp;sort=points&amp;xml=true</Url>
            <Description>Team Members by Points</Description>
        </Resource>
        <Resource>
            <Url>https://www.worldcommunitygrid.org/team/viewTeamMemberDetail.do?teamId=S8TLJ6TFV1&amp;sort=results&amp;xml=true</Url>
            <Description>Team Members by Results</Description>
        </Resource>
        <Resource>
            <Url>https://www.worldcommunitygrid.org/team/viewTeamMemberDetail.do?teamId=S8TLJ6TFV1&amp;sort=name&amp;xml=true</Url>
            <Description>Team Members by Name</Description>
        </Resource>
        <Resource>
            <Url>https://www.worldcommunitygrid.org/team/viewTeamMemberDetail.do?teamId=S8TLJ6TFV1&amp;sort=status&amp;xml=true</Url>
            <Description>Team Members by Date Joined</Description>
        </Resource>
        <Resource>
            <Url>https://www.worldcommunitygrid.org/team/viewTeamStatHistory.do?teamId=S8TLJ6TFV1&amp;xml=true</Url>
            <Description>Team Statistics History</Description>
        </Resource>
    </ResourceList>
</TeamStats>
```

https://www.worldcommunitygrid.org/team/viewTeamInfo.do?teamId=S8TLJ6TFV1&xml=true

I'm actively accepting usernames and verification codes to help me dev against. I can work with just my own but, more devices and members to dev against gives me a better picture when I go to start making an interface to regurgitate this information. In case you're worried about giving it away, you shouldn't. This kind of thing is what it was intended for:



> What is the Verification Code and what is it used for?
> 
> The Verification Code is a private, personal code that you can use to verify your team membership and team stats. There are teams that provide incentives and/or rewards to members for being a part of their team. World Community Grid is providing the Verification Code as a way for these teams to verify their members' statistics and team membership without requiring members to give the team their password.
> 
> ...


https://www.worldcommunitygrid.org/help/viewSearch.do?searchString=Verification+Code

Thanks to @Norton's donation of his verification code, I learned very quickly that I needed to be able to handle large amounts of data at once such as paging the API requests and inserting new records a chunk at a time. What's nifty is that there is enough data where I can do things like query how many points were earned in a day by member and even further, by device:

```
wcg=> select "member-id", SUM("granted-credit") from wcg.results where "received-time"::date = NOW()::date - INTERVAL '1 day' GROUP BY "member-id";
 member-id |       sum     
-----------+------------------
         2 | 5245.97557681395
         3 | 66964.4821919866
(2 rows)

wcg=> select "member-id", "device-id", SUM("granted-credit") from wcg.results where "received-time"::date = NOW()::date - INTERVAL '1 day' GROUP BY "member-id", "device-id";
 member-id | device-id |       sum     
-----------+-----------+------------------
         2 |   4147721 | 5245.97557681395
         3 |   2656516 | 6027.92572390353
         3 |   2853709 |  6473.3430370545
         3 |   3193884 |  21368.575360062
         3 |   3591341 | 4908.10795091952
         3 |   3984182 | 12043.5505308277
         3 |   4052433 | 16142.9795892193
(7 rows)
```

Last night (while semi-intoxicated,) I got the service running with an HTTP server and got a template using Bootstrap in place. It currently just spits out usernames and result counts but, that's it. I haven't added any routing into the mix so, it's really just a place where I can quickly prototype. My hope is to have something people can poke around at by the end of the year however, the data gathering part right now is almost stable (I need to do error handling,) so, I'm planning on running the interfaceless service to continue gathering data because a lot of things like plotting data over time requires data to see. Also as further incentive to donate your verification code, your data will already be partially populated when I open it up to the world to poke around at so you won't have to wait to start seeing data and possibly some pretty graphs.

For what it's worth, I'll probably publish the service on GitHub as open source software when it's closer to having a v1 completed.


----------



## Norton (Nov 25, 2017)

Looks like you put quite a lot of work into this! 

*Would be great if a few more team members contributed their info to help @Aquinus build this out- send him a PM if you want to contribute your info to this project*


----------



## stinger608 (Nov 25, 2017)

Norton said:


> Would be great if a few more team members contributed their info to help @Aquinus build this out- send him a PM if you want to contribute your info to this project



I'd be more than happy to send whatever he needs. Just not sure where you get the verification code at?


----------



## Aquinus (Nov 25, 2017)

stinger608 said:


> I'd be more than happy to send whatever he needs. Just not sure where you get the verification code at?


It's in the "Profile" section at the WCG website. You can get there directly from here: https://www.worldcommunitygrid.org/ms/viewMyProfile.do


----------



## stinger608 (Nov 25, 2017)

Okay, I see something on my profile page that says verification code. Is that what you need?


----------



## twilyth (Nov 26, 2017)

@Aquinus you've got mail


----------



## Hugis (Nov 26, 2017)

Sent my code & user name @Aquinus

Could be interesting as I haven't crunched in a while, lost a laptop to a storm and a tablet went belly up..


----------



## 4x4n (Nov 26, 2017)

pm sent


----------



## Aquinus (Nov 26, 2017)

Thank you everyone for your contribution. Even between only a handful of members, it's really quite a large set of data for just a few days.

For example, this is just for completed work yesterday:

```
wcg=> select username, "device-name", SUM("granted-credit"), COUNT("result-id") from results join members using ("member-id") join devices using ("device-id") where "received-time"::date = NOW()::date - INTERVAL '1 day' GROUP BY username, "device-name";
  username  |     device-name     |       sum        | count
------------+---------------------+------------------+-------
 4x4n_tpu   | DH-PC               | 7736.15819838233 |   138
 4x4n_tpu   | mpower              | 19224.8523669279 |   438
 4x4n_tpu   | taichi-desktop      | 24275.7667378746 |   553
 aquinus    | smite               | 5541.81245691666 |    78
 Norton01   | DESKTOP-M6MN4N2     | 12347.4803879874 |   294
 Norton01   | ELLA-PC             | 4027.82146718246 |    55
 Norton01   | julia               | 6438.83071894064 |    84
 Norton01   | norton2011-desktop  | 18181.9472218535 |   408
 Norton01   | norton4p-Altus-1804 | 24299.5195763465 |   466
 Norton01   | tricia              |  6065.2347922258 |    70
 stinger608 | cruncher            | 3587.52525522426 |    46
 stinger608 | DeanMachine         |   3955.367090909 |    51
 stinger608 | DESKTOP-900270A     | 2402.88825371186 |    46
 stinger608 | WIN-UFMJ0MCV9J2     | 11320.7182622099 |   223
 twilyth    | 14CS                | 24972.6093114645 |   356
 twilyth    | 2600k2              |  8641.1379639476 |   114
 twilyth    | DESKTOP-57KS3G1     | 2016.65340216146 |    29
 twilyth    | i7-2600k2           | 3519.78272345441 |    47
(18 rows)
```



Hugis said:


> Sent my code & user name @Aquinus
> 
> Could be interesting as I haven't crunched in a while, lost a laptop to a storm and a tablet went belly up..


I got your code and username and validated that they work however, since you haven't crunched in a while, no results are showing up. IBM only provides the most recent result data and at some point, they eventually become "stale" and stop showing up in the API. As a result, I can't get historical data, I have to constantly keep my own history up to date by constantly checking the API. It's not entirely ideal but, it's better than nothing.

Edit: Since it's checking every 10 minutes, my 3930k warms up and makes the fans speeds up every 10 minutes when it goes to update the database with more data and I get audio hiccups on resampled streams when it occurs.  I might have to move it to the 3820 sooner rather than later.


----------



## XZero450 (Nov 27, 2017)

What kind of service are you running to interrogate the API?


----------



## Aquinus (Nov 27, 2017)

XZero450 said:


> What kind of service are you running to interrogate the API?


I'm using Clojure. I just wrote something to regularly hit the API with the members stored in the PostgreSQL database. It massages the data a little bit and puts it into PostgreSQL.

As far as extracting the data from the API itself, it's really quite simple. I used Aleph for HTTP since I'm using it for the regular job to pull the data and for the HTTP server to barf out information and Cheshire for handling JSON. Getting the data is really this simple, at least in Clojure:

```
(defn make-url [username code limit offset]                                                                                                                                                 
  (str                                                                                                                                                                                       
    "https://www.worldcommunitygrid.org/api/members/"                                                                                                                                       
    username "/results?code=" code "&limit=" limit "&offset=" offset))                                                                                                                       
                                                                                                                                                                                             
(defn query-member-api [username code limit offset]                                                                                                                                         
  (json/parse-stream                                                                                                                                                                         
    (clojure.java.io/reader                                                                                                                                                                 
      (:body @(http/get (make-url username code limit offset)))) true))                                                                                                                     
                                                                                                                                                                                             
(defn get-data                                                                                                                                                                               
  [username code]                                                                                                                                                                           
  (loop [offset 0                                                                                                                                                                           
         return (list)]                                                                                                                                                                     
    (let [data (query-member-api username code 250 offset)                                                                                                                                   
          {results-available :ResultsAvailable                                                                                                                                               
           results-returned :ResultsReturned                                                                                                                                                 
           results :Results} (:ResultsStatus data)                                                                                                                                           
          updated-return (into return results)                                                                                                                                               
          new-offset (+ (Integer. results-returned) offset)]                                                                                                                                 
      (if (<= (Integer. results-available) new-offset)                                                                                                                                       
        updated-return (recur new-offset updated-return)))))
```

In short, it's a service that I'm building, not one I found.


----------



## twilyth (Nov 27, 2017)

I remember that DAWS(?) used to do something like this but it choked once you started getting into thousands of results.  I guess the real issue is going to be how well the system scales.


----------



## Aquinus (Nov 27, 2017)

twilyth said:


> I remember that DAWS(?) used to do something like this but it choked once you started getting into thousands of results.  I guess the real issue is going to be how well the system scales.


I've spent a lot of time in my last job optimizing a PostgreSQL database where some historical tables had several million records with a couple over 10m. If you know how you're going to query it and in what ways the data set is going to grow, you can plan around it. PostgreSQL also has tools to help me figure out how it planned on executing the query and how it actually panned out, which is nice from a tuning perspective.

So, I'm going to walk you through how I would optimize a poorly written query (by me,) that doesn't scale well in PostgreSQL. It's a great learning opportunity.

So lets say we have this many results:

```
wcg=> select count(*) from wcg.results ;
 count
-------
 26498
(1 row)

wcg=> explain analyze select count(*) from wcg.results ;
                                                   QUERY PLAN                                                  
-----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=939.23..939.24 rows=1 width=8) (actual time=10.151..10.151 rows=1 loops=1)
   ->  Seq Scan on results  (cost=0.00..872.98 rows=26498 width=0) (actual time=0.014..5.755 rows=26498 loops=1)
 Planning time: 0.082 ms
 Execution time: 10.203 ms
(4 rows)
```

It gets even more interesting when you try to do say, a query that aggregates data:

```
wcg=> select username, "device-name", SUM("granted-credit"), COUNT("result-id") from results join members using ("member-id") join devices using ("device-id") where "received-time"::date = NOW()::date - INTERVAL '1 day' GROUP BY username, "device-name";
  username  |     device-name     |       sum        | count
------------+---------------------+------------------+-------
 4x4n_tpu   | DH-PC               | 8952.24814170299 |   130
 4x4n_tpu   | mpower              | 19214.1775239653 |   432
 4x4n_tpu   | taichi-desktop      | 23946.2451875794 |   539
 aquinus    | smite               | 4438.34912934682 |    74
 Norton01   | DESKTOP-M6MN4N2     | 12752.7377335826 |   290
 Norton01   | ELLA-PC             |  4299.5168611983 |    62
 Norton01   | julia               | 5971.11323818165 |    68
 Norton01   | norton2011-desktop  | 17821.9336815164 |   400
 Norton01   | norton4p-Altus-1804 |  21458.223920495 |   350
 Norton01   | tricia              | 6043.73143995742 |    84
 stinger608 | cruncher            | 4098.88799049337 |    68
 stinger608 | DeanMachine         | 4623.43757457595 |    74
 stinger608 | DESKTOP-900270A     | 3664.34179903938 |    44
 stinger608 | WIN-UFMJ0MCV9J2     | 11059.2137892713 |   180
 twilyth    | 14CS                | 22655.2403945448 |   315
 twilyth    | 2600k2              |  8663.4214587507 |   103
 twilyth    | DESKTOP-57KS3G1     | 1921.78183320034 |    25
 twilyth    | i7-2600k2           | 1339.58374298233 |   193
 XZero450   | Enyo                | 15148.7135864938 |   191
 XZero450   | Kryos               | 13206.0733791159 |   190
 XZero450   | Phobos              | 9123.14956541549 |   126
(21 rows)

wcg=> explain analyze select username, "device-name", SUM("granted-credit"), COUNT("result-id") from results join members using ("member-id") join devices using ("device-id") where "received-time"::date = NOW()::date - INTERVAL '1 day' GROUP BY username, "device-name";
                                                             QUERY PLAN                                                            
------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=1214.17..1215.83 rows=1 width=30) (actual time=1361.792..1362.690 rows=21 loops=1)
   Group Key: members.username, devices."device-name"
   ->  Sort  (cost=1214.17..1214.50 rows=132 width=30) (actual time=1361.757..1361.950 rows=3938 loops=1)
         Sort Key: members.username, devices."device-name"
         Sort Method: quicksort  Memory: 404kB
         ->  Nested Loop  (cost=0.00..1209.53 rows=132 width=30) (actual time=9.610..1360.271 rows=3938 loops=1)
               Join Filter: (results."device-id" = devices."device-id")
               Rows Removed by Join Filter: 94512
               ->  Seq Scan on devices  (cost=0.00..1.01 rows=1 width=10) (actual time=0.014..0.017 rows=25 loops=1)
               ->  Nested Loop  (cost=0.00..1206.87 rows=132 width=28) (actual time=4.079..54.194 rows=3938 loops=25)
                     Join Filter: (results."member-id" = members."member-id")
                     Rows Removed by Join Filter: 19690
                     ->  Seq Scan on members  (cost=0.00..1.01 rows=1 width=12) (actual time=0.001..0.002 rows=6 loops=25)
                     ->  Seq Scan on results  (cost=0.00..1204.20 rows=132 width=24) (actual time=0.094..8.763 rows=3938 loops=150)
                           Filter: (("received-time")::date = ((now())::date - '1 day'::interval))
                           Rows Removed by Filter: 22560
 Planning time: 0.527 ms
 Execution time: 1362.792 ms
(18 rows)
```

This is a great case where my query ran slow on a relatively small (<100,000 records,) set of data. This is absolutely terrible because we can't be taking over a second to make a query to display data to a user. People are impatient, and so am I but, what you'll see here is that there are two nested loops and that always spells exponential time. This particular query can be re-written to be a little more efficient, even though this was the easy way to write it. In order to speed this up, we need to give up on querying directly on the results because that's what's killing performance. The two smaller tables are getting joined up to large set of data where most of the data is getting thrown away. That's a lot of wasted effort on the computer's part because the reality is that querying on devices and members isn't very costly:


```
wcg=> explain analyze SELECT username, "device-name" FROM wcg.devices JOIN wcg.members USING ("member-id");
                                               QUERY PLAN                                               
---------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..2.03 rows=1 width=14) (actual time=0.026..0.135 rows=25 loops=1)
   Join Filter: (devices."member-id" = members."member-id")
   Rows Removed by Join Filter: 125
   ->  Seq Scan on devices  (cost=0.00..1.01 rows=1 width=10) (actual time=0.013..0.014 rows=25 loops=1)
   ->  Seq Scan on members  (cost=0.00..1.01 rows=1 width=12) (actual time=0.001..0.002 rows=6 loops=25)
 Planning time: 0.197 ms
 Execution time: 0.172 ms
(7 rows)
```

Since wcg.results has a foreign key on both "wcg."devices"."device-id" and "wcg"."members"."member-id", we can look up those records fairly quickly. The hardest part is checking the time range because there isn't an index on it. We can isolate the aggregation to each unique device by doing a sub-query instead of a JOIN.


```
wcg=> SELECT username, "device-name", (SELECT SUM("granted-credit") FROM "wcg"."results" WHERE wcg.results."device-id" = wcg.devices."device-id" AND "received-time"::date = NOW()::date - INTERVAL '1 day') AS "total-granted-credit", (SELECT COUNT(*) FROM wcg.results WHERE wcg.results."device-id" = wcg.devices."device-id" AND "received-time"::date = NOW()::date - INTERVAL '1 day') AS "total-results" FROM wcg.devices JOIN wcg.members USING ("member-id");
  username  |     device-name     | total-granted-credit | total-results 
------------+---------------------+----------------------+---------------
 aquinus    | smite               |     4438.34912934682 |            74
 Norton01   | norton2011-desktop  |     17821.9336815164 |           400
 Norton01   | DESKTOP-M6MN4N2     |     12800.8226101572 |           290
 Norton01   | tricia              |     6043.73143995742 |            84
 Norton01   | julia               |     5971.11323818165 |            68
 Norton01   | ELLA-PC             |      4299.5168611983 |            62
 Norton01   | norton4p-Altus-1804 |     21499.8998872169 |           350
 stinger608 | cruncher            |     4098.88799049337 |            68
 stinger608 | cruncher            |                      |             0
 stinger608 | DeanMachine         |     4623.43757457595 |            74
 stinger608 | WIN-UFMJ0MCV9J2     |     11059.2137892713 |           180
 stinger608 | DESKTOP-900270A     |     3664.34179903938 |            44
 stinger608 | DESKTOP-2RG7LMF     |                      |             0
 stinger608 | DESKTOP-7GGICF0     |                      |             0
 twilyth    | i7-2600k2           |     361.985833127019 |             7
 twilyth    | DESKTOP-57KS3G1     |     1921.78183320034 |            25
 twilyth    | 14CS                |     22655.2403945448 |           315
 twilyth    | 2600k2              |     8663.42145875069 |           103
 4x4n_tpu   | mpower              |     19214.1775239653 |           432
 4x4n_tpu   | DH-PC               |     8952.24814170299 |           130
 4x4n_tpu   | taichi-desktop      |     23946.2451875794 |           539
 twilyth    | i7-2600k2           |     977.597909855306 |           186
 XZero450   | Phobos              |      9123.1495654155 |           126
 XZero450   | Enyo                |     15148.7135864938 |           191
 XZero450   | Kryos               |     13206.0733791159 |           190
(25 rows)

wcg=> explain analyze SELECT username, "device-name", (SELECT SUM("granted-credit") FROM "wcg"."results" WHERE wcg.results."device-id" = wcg.devices."device-id" AND "received-time"::date = NOW()::date - INTERVAL '1 day') AS "total-granted-credit", (SELECT COUNT(*) FROM wcg.results WHERE wcg.results."device-id" = wcg.devices."device-id" AND "received-time"::date = NOW()::date - INTERVAL '1 day') AS "total-results" FROM wcg.devices JOIN wcg.members USING ("member-id");
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..2563.58 rows=1 width=30) (actual time=12.068..147.161 rows=25 loops=1)
   Join Filter: (devices."member-id" = members."member-id")
   Rows Removed by Join Filter: 125
   ->  Seq Scan on devices  (cost=0.00..1.01 rows=1 width=14) (actual time=0.012..0.014 rows=25 loops=1)
   ->  Seq Scan on members  (cost=0.00..1.01 rows=1 width=12) (actual time=0.001..0.001 rows=6 loops=25)
   SubPlan 1
     ->  Aggregate  (cost=1280.76..1280.77 rows=1 width=8) (actual time=3.068..3.068 rows=1 loops=25)
           ->  Seq Scan on results  (cost=0.00..1280.75 rows=5 width=8) (actual time=1.082..3.056 rows=158 loops=25)
                 Filter: (("device-id" = devices."device-id") AND (("received-time")::date = ((now())::date - '1 day'::interval)))
                 Rows Removed by Filter: 26405
   SubPlan 2
     ->  Aggregate  (cost=1280.76..1280.77 rows=1 width=8) (actual time=2.811..2.811 rows=1 loops=25)
           ->  Seq Scan on results results_1  (cost=0.00..1280.75 rows=5 width=0) (actual time=1.032..2.800 rows=158 loops=25)
                 Filter: (("device-id" = devices."device-id") AND (("received-time")::date = ((now())::date - '1 day'::interval)))
                 Rows Removed by Filter: 26405
 Planning time: 0.421 ms
 Execution time: 147.246 ms
(17 rows)
```

Look at that! Not only does the new query take only 147ms instead of the initial 1362ms, but we're now seeing all the devices that don't actually have earned credit! However what you might notice is that both of the sub-queries aren't using an index and while the sub-queries themselves don't take much time (~2ms,) they're occurring 25 times for each sub-query so, the bulk of the time is still being spent on aggregating but, it's a lot less. The main reason is because sequential scans are costly. Whenever possible, we want to use an index because it will prevent us from having to filter unnecessary records by doing a scan of the index instead of the table.

In this case, a single generic index on wcg.results."received-time" could get us some improvement, then we're only filtering on the actual times. We could index on "received-time", which could further speed up the query but, if we're tuning to make this specific query fast, the best index would either be on "device-id" then on "received-time" or the opposite with the date coming first (less likely.) The first lets us still quickly query all results with a particular device id, the second helps query all results given a time range. So, depending on what other queries you're going to be doing. In this case alone, the ideal index would probably be on device, then the time range because no filtering should have to occur:

```
wcg=> create index "daily-device-results-idx" ON wcg.results ("device-id", "received-time");
CREATE INDEX
wcg=> explain analyze SELECT username, "device-name", (SELECT SUM("granted-credit") FROM "wcg"."results" WHERE wcg.results."device-id" = wcg.devices."device-id" AND "received-time"::date = NOW()::date - INTERVAL '1 day') AS "total-granted-credit", (SELECT COUNT(*) FROM wcg.results WHERE wcg.results."device-id" = wcg.devices."device-id" AND "received-time"::date = NOW()::date - INTERVAL '1 day') AS "total-results" FROM wcg.devices JOIN wcg.members USING ("member-id");
                                                                       QUERY PLAN                                                                       
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..1383.47 rows=1 width=30) (actual time=3.617..36.977 rows=25 loops=1)
   Join Filter: (devices."member-id" = members."member-id")
   Rows Removed by Join Filter: 125
   ->  Seq Scan on devices  (cost=0.00..1.01 rows=1 width=14) (actual time=0.009..0.011 rows=25 loops=1)
   ->  Seq Scan on members  (cost=0.00..1.01 rows=1 width=12) (actual time=0.001..0.002 rows=6 loops=25)
   SubPlan 1
     ->  Aggregate  (cost=690.71..690.72 rows=1 width=8) (actual time=0.928..0.929 rows=1 loops=25)
           ->  Bitmap Heap Scan on results  (cost=28.28..690.70 rows=5 width=8) (actual time=0.288..0.910 rows=158 loops=25)
                 Recheck Cond: ("device-id" = devices."device-id")
                 Filter: (("received-time")::date = ((now())::date - '1 day'::interval))
                 Rows Removed by Filter: 908
                 Heap Blocks: exact=2517
                 ->  Bitmap Index Scan on "daily-device-results-idx"  (cost=0.00..28.28 rows=1066 width=0) (actual time=0.121..0.121 rows=1066 loops=25)
                       Index Cond: ("device-id" = devices."device-id")
   SubPlan 2
     ->  Aggregate  (cost=690.71..690.72 rows=1 width=8) (actual time=0.543..0.543 rows=1 loops=25)
           ->  Bitmap Heap Scan on results results_1  (cost=28.28..690.70 rows=5 width=0) (actual time=0.192..0.530 rows=158 loops=25)
                 Recheck Cond: ("device-id" = devices."device-id")
                 Filter: (("received-time")::date = ((now())::date - '1 day'::interval))
                 Rows Removed by Filter: 908
                 Heap Blocks: exact=2517
                 ->  Bitmap Index Scan on "daily-device-results-idx"  (cost=0.00..28.28 rows=1066 width=0) (actual time=0.075..0.075 rows=1066 loops=25)
                       Index Cond: ("device-id" = devices."device-id")
 Planning time: 0.909 ms
 Execution time: 37.086 ms
(25 rows)
```

Notice how both sequential scans now have become a bitmap index scan with a condition on... the device id? This is because we're casting the time field to a date to make everything easier for for the person writing it (me.) We can probably fix this by doing a query against a range instead of just casting the target field to a date:

```
wcg=> explain analyze SELECT username, "device-name", (SELECT SUM("granted-credit") FROM "wcg"."results" WHERE wcg.results."device-id" = wcg.devices."device-id" AND "received-time" BETWEEN NOW()::date - INTERVAL '1 day' AND NOW()::date) AS "total-granted-credit", (SELECT COUNT(*) FROM wcg.results WHERE wcg.results."device-id" = wcg.devices."device-id" AND "received-time" BETWEEN NOW()::date - INTERVAL '1 day' AND NOW()::date) AS "total-results" FROM wcg.devices JOIN wcg.members USING ("member-id");
                                                                                 QUERY PLAN                                                                                 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..748.53 rows=1 width=30) (actual time=0.275..9.108 rows=25 loops=1)
   Join Filter: (devices."member-id" = members."member-id")
   Rows Removed by Join Filter: 125
   ->  Seq Scan on devices  (cost=0.00..1.01 rows=1 width=14) (actual time=0.011..0.023 rows=25 loops=1)
   ->  Seq Scan on members  (cost=0.00..1.01 rows=1 width=12) (actual time=0.002..0.003 rows=6 loops=25)
   SubPlan 1
     ->  Aggregate  (cost=373.24..373.25 rows=1 width=8) (actual time=0.198..0.198 rows=1 loops=25)
           ->  Bitmap Heap Scan on results  (cost=6.31..372.84 rows=158 width=8) (actual time=0.059..0.143 rows=158 loops=25)
                 Recheck Cond: (("device-id" = devices."device-id") AND ("received-time" >= ((now())::date - '1 day'::interval)) AND ("received-time" <= (now())::date))
                 Heap Blocks: exact=619
                 ->  Bitmap Index Scan on "daily-device-results-idx"  (cost=0.00..6.28 rows=158 width=0) (actual time=0.048..0.048 rows=158 loops=25)
                       Index Cond: (("device-id" = devices."device-id") AND ("received-time" >= ((now())::date - '1 day'::interval)) AND ("received-time" <= (now())::date))
   SubPlan 2
     ->  Aggregate  (cost=373.24..373.25 rows=1 width=8) (actual time=0.153..0.153 rows=1 loops=25)
           ->  Bitmap Heap Scan on results results_1  (cost=6.31..372.84 rows=158 width=0) (actual time=0.050..0.121 rows=158 loops=25)
                 Recheck Cond: (("device-id" = devices."device-id") AND ("received-time" >= ((now())::date - '1 day'::interval)) AND ("received-time" <= (now())::date))
                 Heap Blocks: exact=619
                 ->  Bitmap Index Scan on "daily-device-results-idx"  (cost=0.00..6.28 rows=158 width=0) (actual time=0.041..0.041 rows=158 loops=25)
                       Index Cond: (("device-id" = devices."device-id") AND ("received-time" >= ((now())::date - '1 day'::interval)) AND ("received-time" <= (now())::date))
 Planning time: 0.644 ms
 Execution time: 9.237 ms
(21 rows)
```

So, by changing the query and adding a single index, I was able to make a query that was taking well over a second to take under 10ms. A lot of times, I would see 37ms and say, "that's good enough," but, I thought that going that extra step further would show you can really optimize databases and queries to be very performant even with massive amounts of data.

This is also with a 100% stock PostgreSQL installation. I could tune the server itself to use a lot more system memory (it's typically very conservative on memory,) to make it go even faster.

The thing I would point out though is that the PostgreSQL query planner got the query cost dead wrong which means stats are out of date and the cost for I/O needs to be adjusted to account for the fact that it's running on a RAID-0 SSD array and not a traditional rotational media drive so, that could further help PostgreSQL optimize how it's going to make the query.

With that said, whenever I'm going to be dealing with large amounts of data that need to be queried upon and changed quickly and often, I will always opt for PostgreSQL because it's open source, very fast, and has a boatload of features and this only skims the surface.


----------



## twilyth (Nov 29, 2017)

I didn't follow most of that but thanks for putting in the time you are on this.  It's great you're optimizing everything for large datasets now while you're building the system.


----------



## Aquinus (Dec 1, 2017)

twilyth said:


> I didn't follow most of that but thanks for putting in the time you are on this.  It's great you're optimizing everything for large datasets now while you're building the system.


I want to go even one step further now. On the earlier post there were 26498 results total, now there are:

```
wcg=> select count(*) from wcg.results ;
 count
-------
 40262
(1 row)
```
Which is a nice healthy increase of just over 50%. If I run that same query:

```
wcg=> explain analyze SELECT username, "device-name", (SELECT SUM("granted-credit") FROM "wcg"."results" WHERE wcg.results."device-id" = wcg.devices."device-id" AND "received-time" BETWEEN NOW()::date - INTERVAL '1 day' AND NOW()::date) AS "total-granted-credit", (SELECT COUNT(*) FROM wcg.results WHERE wcg.results."device-id" = wcg.devices."device-id" AND "received-time" BETWEEN NOW()::date - INTERVAL '1 day' AND NOW()::date) AS "total-results" FROM wcg.devices JOIN wcg.members USING ("member-id");
                                                                                 QUERY PLAN                                                                                
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..422.52 rows=1 width=30) (actual time=0.179..4.806 rows=25 loops=1)
   Join Filter: (devices."member-id" = members."member-id")
   Rows Removed by Join Filter: 125
   ->  Seq Scan on devices  (cost=0.00..1.01 rows=1 width=14) (actual time=0.008..0.010 rows=25 loops=1)
   ->  Seq Scan on members  (cost=0.00..1.01 rows=1 width=12) (actual time=0.001..0.002 rows=6 loops=25)
   SubPlan 1
     ->  Aggregate  (cost=408.49..408.50 rows=1 width=8) (actual time=0.136..0.137 rows=1 loops=25)
           ->  Bitmap Heap Scan on results  (cost=10.18..408.13 rows=147 width=8) (actual time=0.037..0.106 rows=146 loops=25)
                 Recheck Cond: (("device-id" = devices."device-id") AND ("received-time" >= ((now())::date - '1 day'::interval)) AND ("received-time" <= (now())::date))
                 Heap Blocks: exact=1065
                 ->  Bitmap Index Scan on "daily-device-results-idx"  (cost=0.00..10.14 rows=147 width=0) (actual time=0.028..0.028 rows=146 loops=25)
                       Index Cond: (("device-id" = devices."device-id") AND ("received-time" >= ((now())::date - '1 day'::interval)) AND ("received-time" <= (now())::date))
   SubPlan 2
     ->  Aggregate  (cost=11.98..11.99 rows=1 width=8) (actual time=0.049..0.049 rows=1 loops=25)
           ->  Index Only Scan using "daily-device-results-idx" on results results_1  (cost=0.30..11.61 rows=147 width=0) (actual time=0.006..0.032 rows=146 loops=25)
                 Index Cond: (("device-id" = devices."device-id") AND ("received-time" >= ((now())::date - '1 day'::interval)) AND ("received-time" <= (now())::date))
                 Heap Fetches: 0
 Planning time: 0.557 ms
 Execution time: 4.881 ms
(19 rows)
```

This is what a query that will scale looks like. 

I haven't had time to work on anything during the week since I have my day job but, I have been letting the service run so I can get as much data as I can for when I have time to work on it. I might have some time to work on it a little bit over the weekend.


----------



## Aquinus (Dec 2, 2017)

...and the struggle continues! I've taken the above "yesterday's stats" query and threw it into a view, so I don't have to replicate the query using HoneySQL. I find that a lot of times, when a query starts to become complex, sometimes the best option is to throw it into a view, particularly if it's something like this where it's relatively static and the only variable is what the day is, so it works out well. So, there's a new "wcg"."view-yesterday-stats" and I'm throwing it on the single page that the HTTP server is currently barfing out (locally.) With only the lifetime gathered stats it was rather empty but, having yesterday's stats in there makes it feel like it's actually doing something. 

I need to figure out what routing library I'm going to use to handle multiple pages though. I've built my own several years ago but, there are things about it that I would like to change. I'm not sure if I want to use it as it is, change it first, or use something else. I suspect that I'll add a navigation bar to the top of the page that lets you get to various pages with generalized stats as I produce the queries to make them. Bootstrap doesn't really have a very good sidebar nav and my CSS skills are very limited so, I'm probably going to avoid that.






As I said before, if there is something you would like to see out of this data, I'm sure I can make something to show it if the data is available. This isn't a tool for me as much as it would be a tool for all of you. I'm just using this as a project to keep certain skills of mine sharp while I'm doing other things in my day job (different languages, databases, etc.) Plus, I like doing things for the community when I can. I would write open source software all day long if I didn't have to pay the bills with it. 

I'm also accepting any creative names for the service as well. WCG Stats is just my boring "I need a name" name.


----------



## Norton (Dec 2, 2017)

Aquinus said:


> As I said before, if there is something you would like to see out of this data, I'm sure I can make something to show it if the data is available. This isn't a tool for me as much as it would be a tool for all of you. I'm just using this as a project to keep certain skills of mine sharp while I'm doing other things in my day job (different languages, databases, etc.) Plus, I like doing things for the community when I can. I would write open source software all day long if I didn't have to pay the bills with it.
> .



That output looks great so far! Really appreciate the time you're putting into this! 


Would be great to start off with a daily totals by member, possibly a conversion from WCG points to FreeDC/BOINC points (7 WCG points= 1 FreeDC point). Not sure what other members want to see- maybe we can start a poll?

*EDIT-* the stats you posted may already be in BOINC points but will need to be checked?



Aquinus said:


> I'm also accepting any creative names for the service as well. WCG Stats is just my boring "I need a name" name.


Going to post this question on the team thread to see if we can get some replies


----------



## T.R. (Dec 4, 2017)

Aquinus said:


> I'm also accepting any creative names for the service as well. WCG Stats is just my boring "I need a name" name.



GridPowerUp


----------



## Aquinus (Dec 4, 2017)

T.R. said:


> GridPowerUp


I actually kind of like that one. I wouldn't want to intrude on @W1zzard as the (something)PowerUp naming scheme is kind of his thing. Either way, I saw that the GridPowerUp.com domain was available for very little money so, I grabbed it, just in case.


----------



## phill (Dec 4, 2017)

I could have sworn I posted in this thread a few days ago...  Still, definitely something PowerUp.com is something I'd like to see


----------



## W1zzard (Dec 5, 2017)

I don't mind you using that name, if this turns out into something more mature we could probably host it for you or make it an official part of TPU, if you are interested


----------



## twilyth (Dec 8, 2017)

Aquinus said:


> As I said before, if there is something you would like to see out of this data, I'm sure I can make something to show it if the data is available.


I'd like to see a least squares regression for the past 30 days for the points generated by each host as well as for total points.

.

.

. 

I'm kidding of course . . . . unless . . .


----------



## Aquinus (Dec 8, 2017)

twilyth said:


> I'd like to see a least squares regression for the past 30 days for the points generated by each host as well as for total points.
> 
> .
> 
> ...


It's not unrealistic and might actually not be too difficult as PostgreSQL happens to already have some built-in aggregation functions for statistics and might have enough for me to do a simple least squares linear regression directly using just SQL.
https://www.postgresql.org/docs/9.5...ate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE

In order to spit out things like daily, weekly, or hourly statistics for hosts or users, I already would need to be grouping by time so, I can include things like this when I go to start dumping out aggregate data against regular time intervals. This is something I already wanted to do already and adding this to it shouldn't be a big deal. I also have experience producing this kind of output from data in PostgreSQL. It would probably take me more time to wire up C3.js to display it. 

With that said, I've loaded a routing library in and I've started hashing out some routes for the different pages that could be useful. Maybe tonight or tomorrow I'll craft up a query or something that produces this kind out output.


----------



## twilyth (Dec 10, 2017)

Thanks but I wouldn't bother with that if no one else wants to see it.  It's a lot of additional work just to show something that you can more or less plot by eye just by looking at a graph.


----------



## Aquinus (Dec 10, 2017)

twilyth said:


> Thanks but I wouldn't bother with that if no one else wants to see it.  It's a lot of additional work just to show something that you can more or less plot by eye just by looking at a graph.


Lets revisit the idea when I have aggregate stats over time in place and getting calculated. At that point, it should be a relatively light lift and isn't really a whole lot of extra work but, I need to get there first.

Quick little update, you've seen this page but, I've added breadcrumbs to the top of the page and updated the name because I like "GridPowerUp" but, you might also notice that usernames are now links:





The device links aren't wired up yet but, I've started on a profile page. Since yesterday's stats were already handy, I started by using that and calculated a couple totals as a start. At the bottom what you're seeing is some debugging I added to the page. It's really just dumping the request by the time it gets to the page handler just so I could make sure that stuff was working as I was putting it together. You can also see how the breadcrumbs work, where "Home" goes back to the first page I made.




With that said, I'm currently thinking about how I'm going to handle aggregating data while keeping it fast (using a view for this tends to not scale when you get >1m records,) and PostgreSQL's materialized views are a little clunky in the sense that refreshing them recalculates everything in the view which makes incremental updates difficult to do. I can already do things like generate a time series for any time interval I want, that's easy:

```
wcg=> select generate_series AS "lower-bound", (generate_series + INTERVAL '1 day' - INTERVAL '1 microsecond') AS "upper-bound" from generate_series(NOW()::date - INTERVAL '30 days', NOW()::date, INTERVAL '1 day');
     lower-bound     |        upper-bound         
---------------------+----------------------------
 2017-11-10 00:00:00 | 2017-11-10 23:59:59.999999
 2017-11-11 00:00:00 | 2017-11-11 23:59:59.999999
 2017-11-12 00:00:00 | 2017-11-12 23:59:59.999999
 2017-11-13 00:00:00 | 2017-11-13 23:59:59.999999
 2017-11-14 00:00:00 | 2017-11-14 23:59:59.999999
 2017-11-15 00:00:00 | 2017-11-15 23:59:59.999999
 2017-11-16 00:00:00 | 2017-11-16 23:59:59.999999
 2017-11-17 00:00:00 | 2017-11-17 23:59:59.999999
 2017-11-18 00:00:00 | 2017-11-18 23:59:59.999999
 2017-11-19 00:00:00 | 2017-11-19 23:59:59.999999
 2017-11-20 00:00:00 | 2017-11-20 23:59:59.999999
 2017-11-21 00:00:00 | 2017-11-21 23:59:59.999999
 2017-11-22 00:00:00 | 2017-11-22 23:59:59.999999
 2017-11-23 00:00:00 | 2017-11-23 23:59:59.999999
 2017-11-24 00:00:00 | 2017-11-24 23:59:59.999999
 2017-11-25 00:00:00 | 2017-11-25 23:59:59.999999
 2017-11-26 00:00:00 | 2017-11-26 23:59:59.999999
 2017-11-27 00:00:00 | 2017-11-27 23:59:59.999999
 2017-11-28 00:00:00 | 2017-11-28 23:59:59.999999
 2017-11-29 00:00:00 | 2017-11-29 23:59:59.999999
 2017-11-30 00:00:00 | 2017-11-30 23:59:59.999999
 2017-12-01 00:00:00 | 2017-12-01 23:59:59.999999
 2017-12-02 00:00:00 | 2017-12-02 23:59:59.999999
 2017-12-03 00:00:00 | 2017-12-03 23:59:59.999999
 2017-12-04 00:00:00 | 2017-12-04 23:59:59.999999
 2017-12-05 00:00:00 | 2017-12-05 23:59:59.999999
 2017-12-06 00:00:00 | 2017-12-06 23:59:59.999999
 2017-12-07 00:00:00 | 2017-12-07 23:59:59.999999
 2017-12-08 00:00:00 | 2017-12-08 23:59:59.999999
 2017-12-09 00:00:00 | 2017-12-09 23:59:59.999999
 2017-12-10 00:00:00 | 2017-12-10 23:59:59.999999
(31 rows)
```
Just to note in case anyone notices it, I'm subtracting 1 microsecond from the upper bound because PostgreSQL's timestamp has a resolution of 1 microsecond and that's just enough to have the next lower-bound to abut the last upper-bound and not leave a gap or to overlap (don't need to count any results more than once or accidentally not count one.)

What becomes costly is taking this generated series and splitting up the results by them as the number of results grow. A potential solution might be to update aggregate statistics when results are created and updated but, that could slow down the rate at which results can be updated. I'm also trying to look forward because I can think of cases where we might want to describe a time frame for something like a challenge so we could have stats for just that so, it requires a little bit of thought. We'll see where it goes.


----------



## stinger608 (Dec 10, 2017)

This is getting great @Aquinus !!!! 

Thanks for all your hard work!


----------



## Aquinus (Dec 10, 2017)

stinger608 said:


> This is getting great @Aquinus !!!!
> 
> Thanks for all your hard work!


Slow and steady wins the race. 

Edit: I personally like the idea of tracking output during challenges. That could be used for special statistics just for a challenge and could be used for things like determining prize eligibility as well as any metrics we wish to derive from it. Challenge pie could be cool. I'm starting to craft up the schema for storing "time frames" and "time groups". You can think of a time group as something like "hourly stats" which has no time bounds or something like a challenge which does. Something like this might fit the bill (although I might need to add other small things to it,) but if I go this route, I want it to be customizable by an administrator.


```
wcg=> \d wcg."time-group"
                          Table "wcg.time-group"
       Column       |            Type             |       Modifiers       
--------------------+-----------------------------+------------------------
time-group-id      | integer                     | not null
name               | text                        | not null
description        | text                        |
lower-bound        | timestamp without time zone |
upper-bound        | timestamp without time zone |
gather-statistics? | boolean                     | not null default false
step-size          | interval                    |
Indexes:
    "time-group-pk" PRIMARY KEY, btree ("time-group-id")
Referenced by:
    TABLE ""time-frame"" CONSTRAINT "time-group-fk" FOREIGN KEY ("time-group-id") REFERENCES "time-group"("time-group-id")

wcg=> \d wcg."time-frame"
                 Table "wcg.time-frame"
    Column     |            Type             | Modifiers
---------------+-----------------------------+-----------
time-frame-id | bigint                      | not null
time-group-id | integer                     | not null
lower-bound   | timestamp without time zone | not null
upper-bound   | timestamp without time zone | not null
Indexes:
    "time-frame-pk" PRIMARY KEY, btree ("time-group-id")
Check constraints:
    "enforce-bound-values" CHECK ("lower-bound" < "upper-bound")
Foreign-key constraints:
    "time-group-fk" FOREIGN KEY ("time-group-id") REFERENCES "time-group"("time-group-id")
```

Edit 2: Also, I didn't make this clear but, my intent is to have something that provides more granular information that gets updated more frequently than Free-DC. This really only can be had by pulling result data which requires user's validation codes.


----------



## twilyth (Dec 10, 2017)

Aquinus said:


> Lets revisit the idea when I have aggregate stats over time in place and getting calculated. At that point, it should be a relatively light lift and isn't really a whole lot of extra work but, I need to get there first.


That sounds good.  Thank you.

I'm not sure I followed what you were saying so this might be gibberish but would it make things easier if you had 2 databases - one that would just be used for viewing information and maybe storing any information the user might be permitted to insert and one for updates.  Then you could transfer any user information to the the update db once the daily updates are done and copy that to the viewing db.  That should reduce any lag in viewing when the primary db is being updated and could also serve as a backup.  Just a thought.


----------



## Aquinus (Dec 11, 2017)

twilyth said:


> I'm not sure I followed what you were saying so this might be gibberish but would it make things easier if you had 2 databases - one that would just be used for viewing information and maybe storing any information the user might be permitted to insert and one for updates. Then you could transfer any user information to the the update db once the daily updates are done and copy that to the viewing db. That should reduce any lag in viewing when the primary db is being updated and could also serve as a backup. Just a thought.


You can do it with materialized views but, you're recalculating everything every time you want to refresh it. It's a lot of work and you can't do it often when the data set grows a lot. I wanted a solution that is more responsive than that. Right now it's gathering stats every 10 minutes. It does it in about 30 seconds with the 6 members in there now but, it's also running 100% serially and I could probably get it to run in a fraction of the time very easily, it just hasn't been a priority yet.

Also, don't use two databases unless you're pulling two existing ones together. It tends to be painful, even if they're the both kind of database. I've learned this from experience; it introduces more problems than you ever will anticipate.

Edit: I actually think that the API will let me restrict based on the last modified time on the result. I probably could make the API call more selective as well.


----------



## Aquinus (Dec 17, 2017)

No pretty interfaces to show today however, I have made some significant progress towards some not so visual things.

First, I just managed to write a trigger to keep track of the "last modified time" field coming back from the queried results. This is important because now when I hit the API for new data, I can tell it "get me everything that has happened since the latest update I have." This is really nice because now the database isn't being hit with constant updates with data I already had, the API only returns what changed, which means getting the actual data is a lot faster. A side effect is that keeping track of this number takes a little bit of time every time a new result is added but, on the other hand, it vastly reduces the number of DML operations which is worth the (very,) small cost of the trigger. So, what used to take ~30 seconds to process now takes about 1 second and even more importantly, doesn't cause the fans on my machine to whirl up every 10 minutes.  In all seriousness though, this means a lot when it comes to the question: "Will is scale?"

Second, I've built out some database schema for tracking statistics, mainly revolving around splitting up the data by time. I also managed to make a database function that take in a timestamp and approproiately fetches and/or creates all relevent time frames given all applicable time groups (such as daily intervals, hourly intervals, or something arbitrary like dates for a challenge.) This should put me a step away from calculating statistics on the fly based on any time groups specified by an administrator. Like the above, it isn't something pretty that you can drool over but, it's an important bit of progress. 


```
wcg=> select * from "time-group";
time-group-id |  name  |                          description                          |     lower-bound     | upper-bound | gather-statistics? | step-size
---------------+--------+---------------------------------------------------------------+---------------------+-------------+--------------------+-----------
             1 | Daily  | Describes every day that statistics can be gathered against.  | 2017-11-07 00:00:00 |             | t                  | 1 day
             2 | Hourly | Describes every hour that statistics can be gathered against. | 2017-11-07 00:00:00 |             | t                  | 01:00:00
(2 rows)

wcg=> select * from getstatistictimeframes(NOW()::timestamp);
NOTICE:  Found group: 1
NOTICE:  Has Time Frame?: t
NOTICE:  Found group: 2
NOTICE:  Has Time Frame?: f
time-frame-id | time-group-id |     lower-bound     |        upper-bound    
---------------+---------------+---------------------+----------------------------
             1 |             1 | 2017-12-17 00:00:00 | 2017-12-17 23:59:59.999999
             4 |             2 | 2017-12-17 12:00:00 | 2017-12-17 12:59:59.999999
(2 rows)
```

Also, we've collected almost 100,000 results since I started this little project:

```
wcg=> select count(*) from results ;                   
count                   
-------                  
97909                   
(1 row)
```

In reality, it's really not much data (yet.)

```
wcg=> SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS SIZE
FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END DESC -- nulls first
    LIMIT 1
;
name | owner | size
------+-------+-------
wcg  | wcg   | 36 MB
(1 row)
```

Edit: I lied, I've now added a "Last Result Update" column in the "Lifetime Gathered Statistics" section on the front page. This actually takes a substantial amount of time to run (page load is >100ms,) so, I might have to reconsidered how this is done because counting everything on the fly probably isn't a great idea as the data set grows.




Edit 2: I wonder if certain projects paired with certain hardware produce more credit than others... Might be a good statistic to track in the future.

Edit 3: You might not realize it, but this is huge:

```
time-group-id | time-frame-id | device-id |  granted-credit  | result-count |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  applicable-result-ids                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
---------------+---------------+-----------+------------------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
             1 |             1 |   4147721 | 4954.47285948456 |           71 | {1987704461,1987703807,1987456509,1987703080,1987703770,1991568624,1991568662,1991568613,1991568625,1991568661,1991568614,1991568681,1991568660,1989471109,1983508457,1988142374,1987956998,1983519020,1989559342,1983521819,1991805945,1989557147,1991963055,1991963053,1991962899,1991962865,1991962922,1991962872,1991962875,1988355958,1989690329,1988034182,1988385552,1986799147,1988385395,1992057266,1983574236,1989972092,1988420038,1988419651,1988420382,1988106235,1988425691,1989973716,1984043089,1988660640,1988631039,1988755633,1992812859,1988433353,1990027326,1984091717,1987189383,1984118876,1984056908,1992812804,1989007015,1989043873,1988660586,1988773756,1990646114,1984625672,1988551087,1987143111,1988799277,1988950910,1987163545,1992513157,1988799373,1988799255,1992364147}
             1 |            35 |   4147721 | 6647.54506653602 |           83 | {1937295067,1941447435,1941384423,1941407270,1939449825,1941446516,1941446653,1941518102,1941581166,1941654493,1941645089,1943420040,1943450208,1937325722,1943471398,1941784678,1945747467,1945746781,1941744731,1942016500,1945841757,1941853802,1940486667,1941840377,1943770419,1937792230,1945865548,1942016479,1942017220,1941914566,1943904197,1942196352,1942221138,1942228401,1943904264,1941900360,1942196995,1942272841,1942272792,1946357064,1942322564,1942322880,1946357054,1946357069,1941090673,1942681030,1942652887,1944388667,1941081998,1944387270,1938273091,1946403549,1942707889,1946797097,1946797177,1938825070,1942863234,1941132123,1946952190,1944856921,1942915304,1944856096,1943167613,1947199862,1943299810,1945018541,1947273610,1943096361,1947199923,1943299861,1942779953,1942708222,1943166463,1942525131,1942778302,1937820886,1943036678,1946952163,1938927055,1942272803,1942058247,1947199778,1946403355}
             1 |            37 |   4147721 | 6473.92300747193 |           80 | {1940564359,1939997280,1941690661,1941167134,1941941419,1940379864,1944623069,1940659594,1942386731,1940474040,1944622928,1939355065,1940473681,1939862654,1940695147,1940379479,1940060466,1939980959,1942343260,1939768390,1936752827,1940060297,1942385074,1940967064,1940100016,1940137074,1940376831,1939980888,1940621544,1944623190,1941887450,1940968059,1940473644,1940102621,1944622906,1940659931,1942559448,1939623100,1939864045,1944260529,1939869394,1939683126,1938346265,1939297379,1941365882,1939464900,1939623083,1935724530,1939580361,1936752854,1935724535,1939459913,1940806474,1939401680,1941160220,1939768836,1936222050,1942875116,1941322460,1939580478,1939023813,1942875882,1940806489,1941219226,1941158359,1939423818,1943329455,1942988998,1943326457,1941218039,1941260052,1944779567,1944779581,1936221814,1935697064,1940967952,1944779602,1940102407,1944779633,1944779575}
             1 |            42 |   4147721 | 6661.31448208844 |           92 | {1955824136,1962639523,1962976504,1961868506,1960836344,1961868853,1956415372,1956415633,1959195487,1965203365,1965203519,1965203499,1965203161,1965203361,1965203354,1965203171,1965203264,1965260952,1965260951,1965260953,1965260950,1965260944,1962045818,1963216262,1959594512,1957000922,1962109328,1963557588,1963271364,1959326832,1963429760,1959995534,1961462941,1961525749,1954644818,1957587011,1960097647,1960095689,1959479013,1959479084,1963652546,1963652246,1963651004,1963651838,1963651836,1959636254,1955200982,1960347835,1961577845,1963717496,1959718142,1959718253,1963717514,1963717495,1959718006,1959725499,1959724066,1959795150,1959795121,1959794258,1959824104,1959826100,1964030345,1960265507,1960142457,1960283087,1960382164,1960373980,1960324303,1962151658,1960382226,1960142540,1960373906,1960239117,1962219560,1960142389,1960373413,1960141917,1960987110,1960950925,1960732892,1961237328,1964672300,1962712255,1956397277,1960433163,1964758424,1959185562,1960432788,1964758519,1960746872,1959615772}
             1 |            62 |   4147721 | 7269.35582540188 |           85 | {1968623935,1968624067,1968288184,1968289068,1972660978,1963852647,1968479148,1968441899,1968478761,1968818235,1968610743,1968610736,1972955298,1968678107,1968676497,1970235703,1970234919,1968696936,1968870999,1968733752,1967582400,1964426680,1973176410,1973176406,1973176148,1973176079,1968977095,1972955281,1969071327,1969070431,1969071434,1972661027,1969071645,1973267374,1973571282,1973572513,1973571283,1973572254,1973572049,1973571284,1969290769,1969374933,1963534316,1967436151,1972105577,1967969920,1968105612,1967783432,1967531715,1966476871,1966020324,1968021717,1967725095,1972183828,1971992229,1971992257,1971992228,1967725037,1969894333,1966058560,1969681135,1967644838,1967731466,1971992287,1967689253,1963490151,1963440805,1971992318,1966494106,1967436938,1971991995,1971992317,1971992253,1968201666,1967260198,1969980180,1968198186,1963808744,1968249920,1972606467,1972606217,1972606493,1970010163,1971992356,1970175429}
             1 |            64 |   4147721 | 7493.76153094536 |           98 | {1952603574,1957445159,1955386345,1959361718,1958486218,1958544950,1957557379,1957656181,1959679008,1959677800,1957760733,1957761084,1958233604,1957760295,1958231006,1952677806,1952677533,1958014693,1961768639,1959847772,1958581632,1955922698,1953253595,1958669733,1957207221,1957232418,1961017263,1961198918,1957280700,1961198811,1961198932,1957285685,1961198871,1957207265,1959030205,1961198858,1957206994,1961198988,1957280769,1962072331,1958249494,1962072269,1962072076,1962072133,1958436774,1960245244,1958314768,1958954730,1958281999,1962488939,1958282228,1960450370,1953340973,1958873457,1953310630,1959061329,1959478814,1960775103,1962622613,1959177644,1958838971,1963200655,1958655209,1961027682,1953974640,1958707967,1960750828,1953924173,1958839402,1963206072,1963206071,1959061471,1959784646,1962622557,1959478599,1959242900,1962622612,1959176752,1958655588,1963206044,1959060722,1958839323,1957169098,1959479420,1963206028,1963159369,1963271363,1963268343,1963271398,1963266755,1963268039,1963267345,1963266893,1954625670,1957096021,1958544182,1953350353,1953212553}
             1 |           112 |   4147721 | 6820.50369660156 |           75 | {1943368891,1942761911,1939295853,1938528492,1939057836,1939101282,1939473657,1938474286,1938528436,1939102740,1941297766,1936190435,1938605694,1938578890,1934744168,1940250858,1938828149,1935271944,1942918590,1939553244,1939095611,1939095318,1939095697,1939058857,1940604779,1942920990,1939058348,1939159222,1937446557,1937445109,1938052644,1938348837,1938856331,1939109562,1934722287,1938345269,1939519614,1935830420,1938440312,1937503021,1937448700,1934139331,1937444949,1938052344,1941702890,1939184744,1938324951,1937583795,1938439543,1939183789,1937322592,1939725990,1937956478,1937959060,1938023285,1938327256,1937868878,1939784257,1937883695,1942368906,1934057276,1941435846,1941702892,1937798177,1939669671,1941710267,1937324970,1934083520,1934587232,1941720692,1938440538,1937359572,1940210117,1937869355,1937869090}
             1 |           172 |   4147721 |  4730.4405058515 |           60 | {1937148474,1937148362,1935780941,1936465289,1939130786,1937395653,1940865497,1936600185,1938677218,1936465258,1936670005,1940865526,1937391188,1940865494,1940865448,1940769948,1940865517,1940770140,1938272481,1937221410,1936465582,1940865501,1940309936,1940219811,1937389457,1936671641,1936396775,1938677268,1937395477,1940865512,1936791683,1936791700,1936764355,1940865491,1937218577,1936465269,1936612060,1939130667,1940432295,1937047517,1940865506,1936465635,1935710226,1935908823,1940219861,1935908320,1935452335,1935928113,1935373692,1936959758,1940769954,1934189628,1935918986,1936356216,1937440514,1935908720,1940769957,1938734413,1940219864,1935776682}
             1 |           179 |   4147721 |  655.90582765806 |            6 | {1934095127,1934808847,1934035424,1934241815,1933201125,1934096168}
             1 |           201 |   4147721 | 6259.93988646657 |           66 | {1950641339,1947378831,1949791278,1946455458,1950712393,1951875754,1951875889,1949361517,1950376048,1949517480,1950611871,1949550769,1949516827,1950376229,1949447965,1949519489,1944613977,1948508939,1952440717,1952440706,1952440707,1949583577,1952470188,1952470191,1952470192,1952470194,1952470187,1952470193,1952470182,1952470183,1949858522,1949857850,1945150325,1950784532,1949861362,1950787711,1949895228,1949893213,1949892587,1949435563,1950313732,1951207611,1950323965,1950313888,1950278029,1950324265,1950154861,1950470929,1951156197,1950314369,1950471282,1949938181,1950322460,1950221714,1950597759,1950717569,1950717505,1953697787,1950881030,1950880416,1950881910,1951539048,1954353464,1952004436,1951207586,1949032079}
             1 |           209 |   4147721 | 6543.46469184441 |           78 | {1967932386,1965826090,1964541859,1963943308,1963942708,1961835667,1959520337,1964050425,1966181331,1968296757,1968158590,1964670303,1968158566,1962392574,1964080693,1964347685,1959561639,1964411636,1960081881,1964347284,1964412398,1964410219,1964406563,1964409566,1964537044,1964538837,1964537216,1964582107,1964444572,1965105854,1965105675,1966730469,1968819880,1964856164,1964855999,1960583910,1960167725,1964974503,1966892750,1962905743,1968958106,1960607594,1964853998,1965385040,1967103302,1965122279,1967339440,1965350175,1965353635,1965544529,1965544527,1967203857,1967203800,1969361245,1960669567,1965406793,1960679423,1965762506,1963493147,1965762230,1963428291,1963429134,1963426268,1963439417,1963444094,1965558467,1964216343,1963577775,1963578095,1963578414,1963588499,1959164592,1959481063,1963616755,1964285822,1963834889,1963835032,1969361176}
             1 |           212 |   4147721 | 6693.15650825415 |           78 | {1943345684,1943581782,1943368920,1945206897,1943491653,1943547591,1943487156,1943548123,1943487479,1945369914,1943708614,1943709280,1947638136,1947638138,1947638756,1947638261,1947638137,1947679741,1947770579,1947770570,1947770571,1947679969,1947770587,1947770590,1943829093,1939347542,1943883791,1939375650,1944257443,1944149521,1945805827,1942647635,1944000794,1944296569,1944329030,1943248848,1944363850,1944252585,1939918706,1944509709,1944509545,1944551384,1944700816,1944784971,1944785996,1948938553,1944842467,1945064472,1948937793,1948937660,1944843933,1948937510,1944978727,1949132103,1949216474,1949216524,1947638193,1944510092,1944257628,1947770578,1943490419,1945805885,1944061936,1944296768,1949216475,1948839110,1944257117,1944363801,1939948632,1943929197,1944784890,1946333403,1946373321,1947770591,1943345750,1947638135,1944510178,1945914152}
             1 |           234 |   4147721 | 5934.27426605799 |           74 | {1946842514,1945271362,1949216525,1949216527,1949216503,1949216437,1949216446,1949216447,1949216504,1947133356,1945349876,1945540096,1945524418,1947281052,1945623062,1949586564,1949586559,1946259515,1946115259,1949586579,1945613595,1941555309,1946571391,1945834336,1945757334,1945710264,1947677780,1947528042,1946543920,1946531417,1945710465,1946112547,1946112638,1948224163,1946113118,1946013281,1945801707,1946297359,1946515480,1946110354,1946462692,1948244858,1946240625,1949586558,1948216325,1946684468,1946682374,1948285937,1946987705,1946988259,1946989029,1948697615,1947131990,1946844353,1950406989,1950674896,1942521820,1950674875,1946240638,1946987150,1945271352,1949610766,1947240399,1942482475,1945624480,1946109023,1949216526,1949610690,1948697522,1948285574,1945192777,1947280966,1949586560,1946259808}
             1 |           249 |   4147721 | 6585.58040507485 |           81 | {1962976492,1963283151,1961311491,1961313120,1962159521,1962159494,1961410776,1961412464,1957031929,1961444090,1961445712,1961444749,1961483194,1961483089,1961482848,1961518145,1961518258,1961518167,1963708129,1962553463,1961697461,1957446652,1961759454,1966075553,1961911534,1966222629,1966222644,1966222591,1962688966,1957504760,1962688897,1962505943,1962902048,1964631858,1957993471,1966758981,1963205299,1964632505,1966862327,1960235814,1962020181,1962288196,1961924963,1964698345,1962503027,1962290012,1962980476,1963821919,1963207551,1966861941,1967086640,1964208716,1962397940,1958128224,1962021030,1958126694,1966861809,1967086639,1964209308,1967086638,1962503066,1962398061,1958523517,1966222630,1961983680,1967086637,1967186799,1967186771,1963122596,1967349274,1963122552,1967349202,1967349275,1963126467,1967351069,1963121596,1967351080,1967351383,1964173146,1965339644,1957031805}
             1 |           256 |   4147721 | 6825.89710154807 |           91 | {1954761114,1957749709,1960469401,1955471149,1955470591,1955468959,1955160838,1955160555,1955157019,1957330315,1955430883,1957304565,1955333971,1956033263,1957304560,1956033320,1953440989,1950893642,1950893374,1950893161,1955510290,1955510393,1955510360,1955780660,1955766431,1957615977,1951223048,1955911852,1955911735,1956906591,1956659314,1959811784,1956007656,1956338896,1959811707,1951290400,1957862652,1957861872,1954082784,1960469441,1960108877,1951352180,1960108876,1960108557,1956074681,1960108875,1956478510,1956159816,1958347938,1958204608,1956607297,1956570761,1952490381,1956653943,1960469471,1960469470,1956116735,1956159905,1951681240,1956159715,1957943548,1958347842,1956864801,1951728127,1958414670,1956116146,1960108556,1959953554,1960469442,1960492584,1955329897,1960953200,1957797904,1957061423,1956986693,1958649075,1957206986,1957123873,1957056633,1956720435,1954764434,1952072076,1952072260,1956720369,1957797618,1952213524,1958648654,1952183175,1957124047,1956732426,1956742715}
             1 |           265 |   4147721 |  6140.6490472686 |           74 | {1969147117,1969146196,1969145923,1969220901,1969279384,1973690245,1969253508,1969747882,1969749840,1973691212,1969417479,1970329319,1973676347,1969416577,1974065829,1969877896,1969878004,1968155309,1965404017,1969487480,1970414172,1965420944,1970054433,1969739208,1970412966,1970412695,1970060210,1969734091,1965902107,1965902301,1968640238,1974357694,1969752412,1974357608,1974386602,1974461795,1974460872,1974529097,1974529172,1974528931,1974528770,1974528779,1974528769,1974528768,1974911323,1974910596,1974910494,1974946687,1974911395,1974911313,1974911361,1970335300,1974910495,1970232000,1974911386,1970234406,1974910558,1970336316,1974946654,1970235842,1974910597,1970334652,1970234507,1974946631,1969619432,1974946649,1966417343,1974983025,1974983119,1974983285,1974983027,1974983200,1974946634,1970807636}
             1 |           276 |   4147721 | 6752.79860941036 |           84 | {1955695591,1958929346,1954901976,1954901048,1950562475,1954721573,1952720015,1953477542,1955365557,1953547951,1955494492,1954855203,1954855164,1955563193,1955555714,1954855147,1949719988,1957756062,1953573380,1959054179,1953611306,1955663882,1954985228,1952763459,1953611187,1955159962,1954985349,1957854464,1953611323,1957874528,1953964074,1953964738,1955159766,1949771644,1957956007,1957874525,1957875415,1955108461,1954050695,1954215735,1955065593,1950087538,1954142847,1954215504,1954215527,1954214484,1954214945,1954231859,1949395283,1954469455,1949360495,1953153675,1957224079,1957300571,1952953144,1955081253,1952905920,1952945873,1953330378,1953378813,1953330562,1952988338,1955664678,1958378590,1954297342,1954323028,1958381234,1954606856,1954606665,1958500289,1958497691,1958500457,1954743936,1955599146,1954742773,1950492603,1956663066,1950492029,1956662944,1955598877,1956033517,1956760621,1958810777,1953386771}
             1 |           286 |   4147721 | 6713.87105483065 |           88 | {1979160267,1975253027,1975272454,1974279416,1979454799,1979455130,1975701263,1975700562,1975745623,1975746433,1975746105,1979638591,1979638585,1975777494,1972353196,1976080178,1977744337,1979752319,1979752310,1979752313,1979752308,1979752297,1979752295,1979751978,1979751957,1979752045,1979751977,1979751959,1979752296,1976603898,1979770332,1979816168,1980155233,1976523290,1976758305,1977381339,1976512208,1972469248,1980155232,1976510387,1977119252,1978778651,1976577143,1976616309,1976572559,1976462494,1976665907,1976666921,1976758276,1976572613,1976510889,1977339829,1976521939,1976798668,1976572394,1976714953,1972824819,1978591296,1980155227,1977156928,1980155236,1978038890,1980094981,1976616645,1978158724,1975154996,1976666123,1978477549,1980096162,1980712422,1980155226,1975690436,1981196995,1977514016,1973754212,1979023519,1977522335,1979159198,1976356546,1977513820,1981157949,1977514274,1981196873,1977721180,1973810737,1977522419,1981197055,1980580261}
             1 |           289 |   4147721 | 6582.44115671167 |           83 | {1980037073,1979876428,1979768647,1979513500,1979616441,1979616357,1981634967,1983677742,1978071266,1976101537,1980080173,1980091283,1981733096,1980092147,1981785254,1976161605,1980149025,1980134634,1980135809,1980401387,1980515955,1980511583,1980575101,1982099114,1980574830,1982098712,1976246291,1978481160,1980258759,1975525703,1984359385,1984359429,1984359430,1984359409,1984359410,1984359431,1984359408,1984379694,1980663371,1979392929,1980664059,1984917966,1981053916,1981188853,1982622831,1976892931,1980772991,1982762650,1982274716,1981072226,1981050798,1981188859,1981023737,1982763283,1980663823,1980663644,1980663905,1981121824,1981072822,1981072417,1981117248,1981121170,1985481610,1985481313,1981619250,1981660860,1983329868,1977360651,1985326242,1983049328,1981619054,1981576098,1985326262,1979949021,1985481609,1981478701,1982302932,1977360619,1979458094,1981342841,1981576632,1981660875,1977411334}
             1 |           315 |   4147721 | 7135.86229897151 |           88 | {1947624891,1949158321,1949162535,1947535178,1947667622,1947622119,1947718747,1947718797,1947167322,1947236011,1947236080,1947235931,1946910639,1947236016,1947236207,1947006426,1947468384,1947034587,1950758585,1947487348,1942530839,1947250887,1943145310,1947718962,1947978421,1943099682,1947758694,1948863577,1949265635,1949162661,1947461076,1949265675,1948231984,1951297665,1948228088,1948161752,1947871840,1947901906,1949622315,1943543050,1948227703,1948227814,1947842045,1951297876,1948176911,1948161811,1948161482,1948162396,1948197894,1949174546,1943560678,1948423296,1949003861,1952014991,1948593559,1952014938,1944563699,1951530939,1948532827,1951888779,1944546909,1949984417,1948613027,1944107232,1948895039,1948424913,1944147684,1951530954,1949108504,1951530940,1952014971,1949173138,1949004155,1949003982,1948717642,1949172805,1951811016,1951888822,1951531030,1949915228,1952014995,1951531009,1948044434,1949985239,1950220281,1951297408,1950353852,1949109243}
             1 |           349 |   4147721 | 6336.72605783478 |           88 | {1965542412,1967370968,1965512713,1965762536,1965762569,1965615955,1965615977,1961206523,1963493283,1961206140,1963526654,1965812200,1965812904,1965859534,1965859852,1965859575,1967828184,1967883570,1965892007,1965964154,1965964208,1965964101,1966003296,1966109907,1969915765,1966075714,1968177967,1968171885,1966307017,1961654100,1966221652,1966221433,1970463881,1971093541,1966424009,1966791691,1971102006,1966628256,1971092809,1971152753,1966743938,1962273415,1966651844,1961713653,1966746412,1966651620,1966952810,1961753572,1968462267,1966641056,1971092494,1971092601,1968374511,1966641338,1971092524,1970917298,1971092525,1966423776,1970934630,1971093080,1971152745,1970934327,1970934216,1966627850,1966704242,1965013438,1965026965,1971213399,1967126179,1971211399,1967126597,1971212931,1971212883,1967045994,1971224538,1967479757,1971092568,1971212887,1967287226,1962798568,1967173091,1967173093,1962835485,1962880296,1969681136,1971092567,1967916221,1962834060}
             1 |           372 |   4147721 |  7860.5278020166 |           99 | {1970587025,1972997619,1972997459,1972997779,1973641970,1969128929,1972997443,1975131276,1975132909,1971571075,1971527343,1971527238,1975351193,1975351207,1975489882,1975311967,1975756879,1975311036,1971570967,1975309268,1971813424,1975309987,1975756178,1966996623,1975311486,1970510293,1971114311,1971570807,1971568268,1975309265,1971122938,1975756948,1975756168,1975756906,1975756928,1971526941,1971528530,1971305119,1975482917,1975309209,1971604250,1975756233,1975756909,1971663569,1971735383,1972198601,1975870598,1972021151,1975870593,1972020965,1972021188,1972020300,1972020685,1972020696,1972019979,1972020934,1972020655,1972021027,1972021456,1972020541,1972459022,1972082341,1972090546,1972547872,1972019374,1972018942,1972081669,1972082497,1972220374,1972082493,1972082188,1973158967,1976587551,1968537681,1973626023,1974099705,1973625967,1973625894,1969502737,1977267208,1972017580,1972621857,1972846483,1973293596,1972848762,1973291601,1972848295,1972759589,1969058803,1977077702,1977077466,1977077699,1977077703,1977077694,1977077485,1977077548,1977077468,1977077741,1972556277}
             1 |           468 |   4147721 | 6599.51227388243 |           87 | {1951067407,1954862190,1955702899,1951015289,1951116548,1951115445,1951116527,1951116951,1947080885,1952423605,1954840137,1950540502,1951339001,1952450044,1951440629,1951492234,1951491246,1955116045,1953022495,1951525367,1951772291,1951764486,1951789761,1951770531,1951769997,1951768859,1953732022,1953848036,1953524086,1952379059,1953844535,1955693330,1955693249,1952379011,1952063122,1952294925,1955693305,1948929442,1952056475,1955693328,1954275697,1953658948,1955598098,1953790064,1951764222,1955693326,1953480331,1953527062,1952060005,1948061644,1951912036,1955693329,1955693256,1952253160,1951913535,1955693279,1954275804,1955598119,1955693312,1955693280,1955693304,1955693273,1952755145,1954198202,1954393095,1948954272,1954053624,1957224080,1956610525,1956888297,1951778266,1954967514,1952712262,1954279865,1954053591,1948980527,1952584636,1952712785,1949329523,1956888362,1952584582,1954353359,1956723620,1955693251,1951800328,1951770366,1952862252}
             1 |           517 |   4147721 | 255.130226227132 |            3 | {1931232260,1932724490,1932541098}
             1 |           621 |   4147721 | 5878.50515339023 |           80 | {1985802383,1985799916,1986060364,1985719598,1987806048,1985719595,1985802875,1989912749,1985795399,1988075742,1986360901,1986656289,1984708342,1986466960,1989929494,1981996617,1986466239,1986329570,1986855874,1986479894,1989929493,1986855237,1986508470,1989929500,1986279925,1986832260,1988509985,1987977904,1989929503,1986855433,1986516758,1986084027,1986884796,1986685739,1988331600,1986856054,1986414634,1986507633,1986082765,1988134640,1989912742,1989929499,1986780176,1990660659,1990056126,1986552917,1981938978,1984744132,1990230544,1989929498,1987360469,1987410587,1991021691,1987457425,1982452704,1988883978,1988783775,1991021640,1988586443,1990953491,1982452867,1987449550,1987309348,1987310918,1982937593,1991021644,1987290294,1982430370,1987411372,1987089324,1988979467,1987235838,1991021685,1988978863,1987241298,1989929495,1985809395,1986855765,1989929496,1985778384}
             1 |           658 |   4147721 | 7021.82214354383 |          101 | {1973039309,1973039946,1973419189,1973415803,1973414095,1973417269,1973418079,1973420124,1977637516,1973456610,1973456441,1977699808,1977699806,1977699904,1977699923,1977699903,1977699807,1977699811,1977699848,1977699850,1973529360,1973529420,1973530574,1974263702,1973709611,1975390824,1977932217,1973803674,1975774470,1974420446,1973882243,1978111826,1974753388,1970736450,1978270712,1970777019,1974871338,1974134855,1973390583,1974796700,1974327651,1974872826,1970827496,1974479653,1976135024,1976216076,1974637738,1974796643,1970827217,1975051771,1973414036,1973423484,1974757424,1974520910,1976166717,1978270703,1975943720,1978191844,1975954458,1974132528,1978270702,1970799646,1978191718,1976135064,1975180357,1974714924,1971309656,1974796618,1978801464,1978801583,1978801497,1978801585,1978801465,1978801501,1978801498,1978801495,1978801463,1978801499,1976549124,1978801503,1974872501,1974873371,1974873366,1976717683,1975010997,1975059711,1975060385,1973901655,1975234764,1975450113,1979165656,1977060212,1971765326,1975464260,1977106994,1971783248,1975478163,1973920553,1971805619,1977204193,1978801494}
             1 |           665 |   4147721 | 6504.04239423794 |           80 | {1984802709,1984802663,1984801852,1983597481,1983588984,1983739047,1983540601,1982077199,1983738986,1979295838,1985533221,1983799084,1987560997,1987560946,1987560931,1979295676,1983740614,1984289856,1982513943,1984344344,1984343594,1984417908,1988013099,1983049116,1988089830,1983924935,1984343355,1984344510,1979330091,1985994257,1983828986,1985832913,1988013093,1984227818,1984343381,1984530599,1986358971,1984375357,1979933345,1984376944,1984533866,1983068636,1988482298,1988482286,1988482368,1988482299,1985380055,1984795209,1985320729,1984979362,1985381885,1986900076,1980425912,1985056582,1980426098,1985473743,1986633359,1985523321,1985381307,1987203247,1987293331,1983687555,1979827576,1987201146,1983619452,1985522567,1986901707,1985719182,1984797509,1986634051,1985319629,1985523101,1985130687,1985320291,1985402853,1980437042,1985401884,1985064270,1985719540,1984980030}
             1 |           756 |   4147721 | 6690.84818841928 |           83 | {1977704983,1981811714,1978096834,1981897593,1981897582,1979776680,1978407359,1979733346,1974256513,1982011976,1978534404,1978606767,1978711748,1978606928,1978572112,1974709540,1978606988,1978572015,1979018732,1978607008,1978853800,1978572738,1978614541,1978607330,1978572580,1982606936,1982606841,1982606970,1979018513,1982606992,1982606884,1979018284,1982606991,1982770120,1980621052,1982770248,1982770272,1982770171,1982770083,1982770170,1982770197,1982770219,1982770039,1979298041,1982881215,1982881295,1979437487,1979437492,1979435498,1979436988,1979297468,1975632073,1983150173,1979648264,1977477799,1981070765,1977642911,1977646187,1979620923,1978040547,1981598442,1974201366,1981411457,1981605834,1977951679,1977705078,1981598461,1977705204,1977704659,1981598462,1981411225,1977924928,1977704412,1981598424,1977704928,1981598443,1977917948,1978043256,1977501960,1975641900,1979738602,1975642139,1981259922}
             1 |           875 |   4147721 | 5933.39279763428 |           71 | {1982539326,1982603394,1986462447,1982603118,1982601496,1980973813,1982605392,1978362317,1982757630,1982757625,1982655869,1978379662,1982676655,1984746409,1984746822,1982677111,1982689797,1981484268,1982790510,1982790276,1982909153,1978430787,1984844421,1981495434,1983103188,1983103760,1986916622,1984896069,1983149975,1984895416,1983149565,1983149499,1983138299,1984928175,1983226868,1981513916,1981535007,1983219115,1983460445,1982656627,1983461712,1983462910,1983459999,1983137723,1981987208,1981803153,1982123658,1985565090,1983776283,1982154248,1981757481,1982154461,1982005396,1983455762,1981756573,1982154028,1977967319,1982219322,1982219207,1983971026,1982379413,1980924443,1982352189,1982353569,1984173369,1977978580,1982343595,1982383996,1982449350,1982510450,1984300087}
(29 rows)
```


```
wcg=> explain analyze select * from "device-result-statistics" WHERE "time-group-id" = 1 AND "device-id" = 4147721;
                                                                           QUERY PLAN                                                                          
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using "device-result-statistics-pk" on "device-result-statistics"  (cost=0.41..237.42 rows=31 width=166) (actual time=0.021..0.315 rows=29 loops=1)
   Index Cond: (("time-group-id" = 1) AND ("device-id" = 4147721))
Planning time: 0.074 ms
Execution time: 0.340 ms
(4 rows)
```

Edit 4: Checking out for the weekend but, I do have stats getting gathered now as results are inserted/updated. Stats are broken down by device for whatever time groups exist. I threw together a quick daily total on the front page that sums up all the device stats for each time frame for the daily time group but, I'm going to have to do something about how I display information on these pages. Either way, it's progress.


----------



## Aquinus (Jan 1, 2018)

It's been a little while since I gave an update and I did say before that I wanted to have something ready for the new year. Well, it's not quite there but, the progress has been good (minus the last week or so due to the holidays.)

I have, unfortunately, found a bug. Apparently time frames are getting duplicated within a single DML operation. So for example, if 20 records (that have points earned and are verified,) are inserted with the same received time, if the time frame doesn't exist, it will create a new time frame for every result, then use some arbitrary one thereafter.

I have identified how this is occuring though. It mainly has to do with the function for handling this not having visibility into the records created in the same transaction. There are a couple ways to handle this but, I've been torn on which I should do. I have an easy solution (insert one result at a time, instead of bulk inserting them,) but, it doesn't solve the bug, it just works around it. Another would be to prepare these records just prior to inserting/updating them but, it puts overhead on the application as opposed to the database to work around a database problem. The third, which requires more work and a newer version of PostgreSQL (>10.0,) which would allow me to do statement level triggers and know which rows happened in the bulk DML operation. This would let me process all of them at the same time instead of one row a time. This would require me to get a PostgreSQL 10 server running.

After thinking about it, I decided that I want to do this the right way and go with the last option. I've already installed PostgreSQL 10 on "Smite", the 3820 machine running in the attic. This would get me another step to making this project publicly visible as well (include me not starting the project every morning that I start my 3930k machine.) Hopefully over the next several weeks I can start to iron out these issues but, I have constantly been starting the service to make sure results are getting stored. We have quite the bit of information to work with already. Just recently we passed the 150k mark for results.

```
wcg=> select count(*) from results ;
 count  
--------
 154034
(1 row)
```

As always, if you haven't donated your username and validation code, it's more than welcome. Just send it over in a PM and I'll add you to the mix! Suggestions are always welcome as well. This is for me as much as it is for all of you.


----------



## twilyth (Jan 2, 2018)

Don't push yourself. We're not going anywhere.

Thank you for the update.  Was just thinking about this yesterday.  And thanks for all of the work you're doing on it.


----------



## Solaris17 (Jan 2, 2018)

Totally unrelated, I only dabble in DBA as part of my job but I have taken up postgre instead of my normal sqli mysql route and im glad to see it gets so much love by someone who knows far more about this stuff then me. Now I wont doubt teaching myself with it.


----------



## Aquinus (Jan 2, 2018)

Solaris17 said:


> Totally unrelated, I only dabble in DBA as part of my job but I have taken up postgre instead of my normal sqli mysql route and im glad to see it gets so much love by someone who knows far more about this stuff then me. Now I wont doubt teaching myself with it.


PostgreSQL is used all over the place and most people don't even realize it. At my last job everything lived in a PostgreSQL database so, I had to get good at it.


----------



## Arjai (Jan 14, 2018)

By validation code, do you mean the Account Key? @Aquinus

EDIT: Never mind, got it. PM incoming


----------



## Aquinus (Jan 14, 2018)

Arjai said:


> By validation code, do you mean the Account Key? @Aquinus
> 
> EDIT: Never mind, got it. PM incoming


You got it, thank you! An additional thank you to @blindfitter and @mstenholm for donating their validation codes as well. It's over 200,000 results at this point. 

I'm still trying to hammer out the issue with gathering statistics and duplicating time frames. My initial idea wasn't really a viable solution because the same problem occurs using statement triggers after the fact and I'm having to re-evaluate how I'm doing it. Between not having much time and being held up on this bug, I haven't made a whole lot of progress lately. I have another idea but, it's going to take a little bit of time to implement. The wife has work today for almost the entire afternoon and I don't have a whole lot queued up to do so, I might spend a few hours trying to hammer it out. It seems like I need more than one trigger, one that occurs before the update/insert to ensure that time frames already exist (at the statement level,) and another to actually add the results to the proper time frames after the update/insert occurs instead of doing it all at once. We'll see where it goes.


----------



## phill (Jan 14, 2018)

Arjai said:


> By validation code, do you mean the Account Key? @Aquinus
> 
> EDIT: Never mind, got it. PM incoming



Whereabouts can I find this??  I read that you found it but you never said where!!


----------



## Aquinus (Jan 14, 2018)

phill said:


> Whereabouts can I find this??  I read that you found it but you never said where!!


It's hiding earlier in the thread:


Aquinus said:


> It's in the "Profile" section at the WCG website. You can get there directly from here: https://www.worldcommunitygrid.org/ms/viewMyProfile.do


----------



## thebluebumblebee (Apr 18, 2018)

https://www.techpowerup.com/forums/threads/wcg-badges-are-back.133019/post-2060655
Is the information in that post useful?


----------



## Aquinus (Apr 18, 2018)

thebluebumblebee said:


> https://www.techpowerup.com/forums/threads/wcg-badges-are-back.133019/post-2060655
> Is the information in that post useful?


I'm pulling the data right from WCG itself so, I get rather detailed information on every task that runs. I wouldn't really expect another service to have more data than what WCG actively provides through their APIs. In fact, there are other APIs that don't require the validation code but, you only get aggregated data. You don't get information about the individual tasks that have been run and how they turned out though. I wanted something a little more detailed.

```
wcg=# select * from wcg.results where "member-id" = 2 limit 20;
 result-id  | member-id | app-id |  claimed-credit  |     cpu-time      |   elapsed-time    | exit-status |  granted-credit  | device-id |  mod-time  | workunit-id |                      name                       | outcome |    received-time    |   report-deadline   |      sent-time      | server-state | validate-state | file-delete-state 
------------+-----------+--------+------------------+-------------------+-------------------+-------------+------------------+-----------+------------+-------------+-------------------------------------------------+---------+---------------------+---------------------+---------------------+--------------+----------------+-------------------
 1948697615 |         2 |      7 | 29.8301011520574 |  1.18309361111111 |  1.18501409805556 |           0 | 29.8301011520574 |   4147721 | 1511827932 |   382239207 | FAH2_001400_avx17285-1_000006_000019_011_0      |       1 | 2017-11-26 19:12:10 | 2017-11-27 13:04:05 | 2017-11-26 13:04:05 |            5 |              1 |                 2
 1947131990 |         2 |      9 |  115.57861353947 |  3.49610833333333 |  3.49666971333333 |           0 | 115.558663956831 |   4147721 | 1511888826 |   381123514 | MCM1_0138372_0709_0                             |       1 | 2017-11-26 22:22:45 | 2017-12-03 13:04:05 | 2017-11-26 13:04:05 |            5 |              1 |                 2
 1946844353 |         2 |      8 | 52.3375829148568 |  1.19127916666667 |  1.19299537805556 |           0 | 52.3375829148568 |   4147721 | 1511834658 |   380893934 | MIP1_00027672_1014_0                            |       1 | 2017-11-26 21:04:12 | 2017-12-06 13:04:05 | 2017-11-26 13:04:05 |            5 |              1 |                 2
 1950406989 |         2 |      5 | 55.6027842906484 | 0.966578888888889 | 0.967971758888889 |           0 | 47.3213098615202 |   4147721 | 1511830499 |   377129813 | OET1_0005185_x4GV3p_rig_57401_1                 |       1 | 2017-11-26 19:54:50 | 2017-11-30 01:04:05 | 2017-11-26 13:04:05 |            5 |              1 |                 2
 1947167322 |         2 |      9 |  115.83156193935 |  3.49771666666667 |  3.49988376861111 |           0 | 114.228093858699 |   4147721 | 1511892700 |   381153760 | MCM1_0138372_7768_0                             |       1 | 2017-11-27 00:25:45 | 2017-12-03 13:22:43 | 2017-11-26 13:22:43 |            5 |              1 |                 2
 1958486218 |         2 |      9 | 113.851831659972 |            3.2492 |  3.25292790861111 |           0 | 133.269188164032 |   4147721 | 1512346758 |   389049231 | MCM1_0138513_0104_0                             |       1 | 2017-12-02 15:17:11 | 2017-12-09 02:46:58 | 2017-12-02 02:46:58 |            5 |              1 |                 2
 1958544950 |         2 |      9 | 116.235898450657 |  3.32084722222222 |  3.32104448861111 |           0 | 115.312236490598 |   4147721 | 1512350976 |   389096963 | MCM1_0138513_9169_0                             |       1 | 2017-12-02 15:17:12 | 2017-12-09 03:25:46 | 2017-12-02 03:25:46 |            5 |              1 |                 2
 1947236011 |         2 |      9 | 115.081032977187 |  3.47521111111111 |  3.47720632138889 |           0 | 113.579855238304 |   4147721 | 1511963167 |   381191261 | MCM1_0138374_1370_1                             |       1 | 2017-11-27 00:25:45 | 2017-12-03 15:10:29 | 2017-11-26 15:10:29 |            5 |              1 |                 2
 1947236080 |         2 |      9 | 116.767996711817 |  3.52739444444444 |  3.52817841305556 |           0 | 115.464862627591 |   4147721 | 1511863588 |   381191280 | MCM1_0138374_1381_0                             |       1 | 2017-11-27 00:25:45 | 2017-12-03 15:10:29 | 2017-11-26 15:10:29 |            5 |              1 |                 2
 1947235931 |         2 |      9 | 116.118943649506 |  3.50474722222222 |  3.50856708916667 |           0 | 114.768276930156 |   4147721 | 1512022691 |   381191238 | MCM1_0138374_1393_0                             |       1 | 2017-11-27 00:25:45 | 2017-12-03 15:10:29 | 2017-11-26 15:10:29 |            5 |              1 |                 2
 1946910639 |         2 |      8 | 73.8430587175639 |  1.68195055555556 |  1.68332775666667 |           0 | 73.8430587175639 |   4147721 | 1511846750 |   380943481 | MIP1_00027712_1132_0                            |       1 | 2017-11-27 00:25:45 | 2017-12-06 15:20:43 | 2017-11-26 15:20:43 |            5 |              1 |                 2
 1947236016 |         2 |      9 | 116.018281738444 |          3.506775 |  3.50833199138889 |           0 | 115.170708471095 |   4147721 | 1511848483 |   381191246 | MCM1_0138374_1415_0                             |       1 | 2017-11-27 00:50:55 | 2017-12-03 15:10:29 | 2017-11-26 15:10:29 |            5 |              1 |                 2
 1947236207 |         2 |      9 | 116.240204384259 |           3.51115 |  3.51223101694444 |           0 | 116.938012345142 |   4147721 | 1511846749 |   381191322 | MCM1_0138374_1191_1                             |       1 | 2017-11-27 00:25:45 | 2017-12-03 15:10:29 | 2017-11-26 15:10:29 |            5 |              1 |                 2
 1950674896 |         2 |      5 | 43.5911238614777 | 0.787700555555556 |       0.789095175 |           0 | 39.7292781372023 |   4147721 | 1511839368 |   372199973 | OET1_0005176_x4GV3p_rig_64941_1                 |       1 | 2017-11-26 22:22:45 | 2017-11-30 03:10:29 | 2017-11-26 15:10:29 |            5 |              1 |                 2
 1942521820 |         2 |      5 | 35.5443068575337 | 0.642360833333333 | 0.643430096666667 |           0 | 35.5443068575337 |   4147721 | 1511839368 |   377822610 | OET1_0005186_x4GV3p_rig_50366_0                 |       1 | 2017-11-26 22:22:45 | 2017-12-06 15:20:43 | 2017-11-26 15:20:43 |            5 |              1 |                 2
 1950674875 |         2 |      5 | 35.1728439035742 | 0.636010833333333 | 0.636705800555556 |           0 | 32.1058763223576 |   4147721 | 1511839367 |   372200247 | OET1_0005176_x4GV3p_rig_64903_1                 |       1 | 2017-11-26 22:22:45 | 2017-11-30 03:10:29 | 2017-11-26 15:10:29 |            5 |              1 |                 2
 1947006426 |         2 |      8 | 37.3081783988738 | 0.873123611111111 | 0.873691461388889 |           0 | 37.3081783988738 |   4147721 | 1511848261 |   381019165 | MIP1_00027741_0477_0                            |       1 | 2017-11-27 00:50:55 | 2017-12-06 17:02:00 | 2017-11-26 17:02:00 |            5 |              1 |                 2
 1947468384 |         2 |      9 | 116.442905404309 |          3.521625 |  3.52281870472222 |           0 | 115.919721761301 |   4147721 | 1511859061 |   381337732 | MCM1_0138376_4982_0                             |       1 | 2017-11-27 03:50:54 | 2017-12-03 17:02:00 | 2017-11-26 17:02:00 |            5 |              1 |                 2
 1947034587 |         2 |      8 | 50.9230167742068 |  1.17323555555556 |  1.17407489277778 |           0 | 50.9230167742068 |   4147721 | 1511851302 |   381042375 | MIP1_00027745_0135_0                            |       1 | 2017-11-27 01:41:34 | 2017-12-06 17:15:00 | 2017-11-26 17:15:00 |            5 |              1 |                 2
 1950758585 |         2 |     10 | 47.4980647522305 |  1.01460805555556 |  1.01526597194444 |           0 | 47.5720429182576 |   4147721 | 1511851302 |   368162733 | ZIKA_000290493_x4wf8_HCV_NS3pr_Asnprvr_A_0562_1 |       1 | 2017-11-27 01:41:34 | 2017-11-30 05:15:00 | 2017-11-26 17:15:00 |            5 |              1 |                 2
```

With that said, I realize it has been quite some time since I've provided an update. Real life got rather busy for me so, I haven't spent a whole lot of time on working on this. I did a couple of layout changes but, that's about it. Beyond that, it's just been running on the 3820 box in the attic (which is also crunching.) It stopped running for about 5 days while I was on holiday a few months ago so, there is a small gap in the collected data but, I fixed the bug that caused that and it has been running continuously for a couple months now. The uptime on the machine itself is starting to become rather impressive as well, I might add.

```
$ uptime
 18:27:43 up 142 days,  9:53,  7 users,  load average: 8.22, 8.22, 8.14
```
The number of captured results is also getting a bit on the larger side. The front page has become a little sluggish as a result but, that's because I'm crunching lifetime stats when it loads which is something that will have to change.

```
wcg=# select count(*) from wcg.results ;
 count  
--------
 594429
(1 row)
```

I'm at a point where I really need to bite the bullet and handle registration and login, then finish a couple more pages to expose some more data before I decide to open up an alpha version to at least some people here at TPU. I just need to find the time to do it.


----------



## Aquinus (May 25, 2018)

Unfortunately due to the European Union General Data Protection Regulation, WCG no longer lets me collect this information despite having asked all of you for your verification codes. I've contacted WCG requesting an access token to continue to work on this project but, that may only allow you to get your own information and I won't know more until I hear back from IBM. It's possible that the GDPR just killed this project.


----------



## Norton (May 25, 2018)

@mstenholm shared a link at the WCG forum discussing this:

https://www.worldcommunitygrid.org/forums/wcg/viewthread_thread,40823_offset,10

Will likely kill off all of the stats programs


----------



## Boatvan (May 25, 2018)

Norton said:


> Will likely kill off all of the stats programs


This would make me a sad panda...


----------



## phill (May 25, 2018)

Well that sucks...  No more pie!!


----------



## HammerON (May 25, 2018)

Thanks for taking on this task @Aquinus !  I wasn't aware that you were trying to do this.  I really hope this doesn't end your project or others such as Free-DC and BOINCstats...


----------



## Aquinus (May 25, 2018)

Well, I heard from IBM. Not only do I have to sign a *legally binding document that says I'm complying with all applicable GDPR rules*, I have to wait for IBM to *finish preparing this document*. As it stands right now, I'm unable to continue and once it's ready I'll be forced into a legal agreement should I continue to work on this project, even for members who aren't EU citizens, which personally rubs me the wrong way. I'm going to have to think long and hard before committing to something like this as I feel that the burden isn't necessarily worth the effort being put on to what I consider a hobby project.



> Hello Jon,
> 
> Thank you for taking the time to contact us.  As you've noticed, access to the APIs is now restricted to people who obtain a token. To get the token, you must first sign our Data Processing Agreement to ensure that you're willing to comply with certain requirements about how the API data must be processed. We are still finalizing the text of this Agreement and will email you as soon as it's ready.  We have just implemented the release which allows members to access their own API stats: https://www.worldcommunitygrid.org/forums/wcg/viewpostinthread?post=582183
> 
> ...


----------



## HammerON (May 25, 2018)

Understand whatever you choose to do in this instance.


----------



## thebluebumblebee (May 25, 2018)

Does the EU need a Trump thump?


----------



## Aquinus (May 25, 2018)

thebluebumblebee said:


> Does the EU need a Trump thump?


I'm more annoyed that I'm being forced into a legal agreement even should I choose to serve only non-EU citizens. I'm more than happy to protect people's information but, I don't like being essentially coerced into it, even outside of the EU which is beyond the purview of the EU council.


----------



## Norton (May 26, 2018)

Bok over at FreeDC posted in his forum on this issue:



> No WCG stats for now
> World Community Grid have switched off anonymous access to both the xml stats used for overall stats as well as access to the API's I use to get badge data and subproject data.
> 
> I've requested access to the former which *should* be granted via userid/pw at some point, but I'm unsure whether there will be access to the badge and subproject data again.​


https://www.free-dc.org/showthread.php?56848-No-WCG-stats-for-now

WCG also added an option (Opt-in) to display your data- see link in the article below (near the bottom):
https://www.worldcommunitygrid.org/about_us/viewNewsArticle.do?articleId=561


----------



## Arjai (May 26, 2018)

Aquinus said:


> Unfortunately due to the European Union General Data Protection Regulation, WCG no longer lets me collect this information despite having asked all of you for your verification codes. I've contacted WCG requesting an access token to continue to work on this project but, that may only allow you to get your own information and I won't know more until I hear back from IBM. It's possible that the GDPR just killed this project.


See the post here, https://www.techpowerup.com/forums/threads/have-you-got-pie-today.93832/post-3846848

Essentially you ask for access and then sign a contract to abide by the new policy and you get a token to use that will give you access to those who have not blocked their info. Or to accounts that have been deleted.


----------



## Aquinus (May 26, 2018)

They switched off authorized access with verification codes as well. I can't even pull my own info using my own verification code.


Arjai said:


> See the post here, https://www.techpowerup.com/forums/threads/have-you-got-pie-today.93832/post-3846848
> 
> Essentially you ask for access and then sign a contract to abide by the new policy and you get a token to use that will give you access to those who have not blocked their info. Or to accounts that have been deleted.


It's a little more than that. If a user deletes their info while I'm building this service, I will be legally obligated to delete all of this user's associated info. It's IBM literally absolving themselves of any wrongdoing  should anyone try to sue under the guise of GDPR. It puts the legal obligation on me. With that said, I already asked for the token and they told me that the paperwork isn't ready yet so, I have to wait here patiently anyways should I choose to sign it.


----------



## Aquinus (May 31, 2018)

For what it's worth:


> Hello again Jon,
> 
> Correct.  IBM is adopting the GDPR policies globally.  You may find more information about the changes here:  https://www.worldcommunitygrid.org/about_us/viewNewsArticle.do?articleId=561
> 
> ...


I'm not quite sure how I feel about that.


----------



## XZero450 (May 31, 2018)

@Aquinus, IBM is doing what every organization that I have been a part of because it's easier and cheaper to build for the most secure and put everything in that basket rather than to keep things isolated and managed separately. In addition to that, we have a trend in the world right now when it comes to privacy in that many other countries may soon adopt similar laws and IBM might as well be pro-active.

I would recommend that if you do continue with this project that you do so as part of an LLC or similar and put the onus on an entity that you control, rather than your person. The price for data is about to go up, and so will the cost of liability.

What I would have preferred to see IBM do is to keep machine stats separate from people/users and left everything open. Thus, you could trend machines, and the owners of said machines would know which ones were theirs, and then it would be entirely up to the owners to enable trending/sharing of their statistics. Having not read the new legal article, I may be off base with possibilities.


----------



## Norton (Jun 11, 2018)

@Aquinus 

Do you have any experience with web page/screen scraping? If so, it may be able to salvage this project by grabbing the data that way.

I'm currently grabbing our numbers from a WCG page daily and dumping the data into a spreadsheet. This page:
https://www.worldcommunitygrid.org/...Id=S8TLJ6TFV1&pageNum=1&numRecordsPerPage=250

My method is crude but it works, there's likely many ways to do it better 

Thoughts?


----------

