{"action":"created","issue":{"url":"https://api.github.com/repos/decred/dcrdata/issues/427","repository_url":"https://api.github.com/repos/decred/dcrdata","labels_url":"https://api.github.com/repos/decred/dcrdata/issues/427/labels{/name}","comments_url":"https://api.github.com/repos/decred/dcrdata/issues/427/comments","events_url":"https://api.github.com/repos/decred/dcrdata/issues/427/events","html_url":"https://github.com/decred/dcrdata/pull/427","id":314280998,"number":427,"title":"Implement the new proposed Addresses table format","user":{"login":"dmigwi","id":22055953,"avatar_url":"https://avatars1.githubusercontent.com/u/22055953?v=4","gravatar_id":"","url":"https://api.github.com/users/dmigwi","html_url":"https://github.com/dmigwi","followers_url":"https://api.github.com/users/dmigwi/followers","following_url":"https://api.github.com/users/dmigwi/following{/other_user}","gists_url":"https://api.github.com/users/dmigwi/gists{/gist_id}","starred_url":"https://api.github.com/users/dmigwi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dmigwi/subscriptions","organizations_url":"https://api.github.com/users/dmigwi/orgs","repos_url":"https://api.github.com/users/dmigwi/repos","events_url":"https://api.github.com/users/dmigwi/events{/privacy}","received_events_url":"https://api.github.com/users/dmigwi/received_events","type":"User","site_admin":false},"labels":[{"id":795603859,"url":"https://api.github.com/repos/decred/dcrdata/labels/needs%20review","name":"needs review","color":"85d2db","default":false},{"id":693838481,"url":"https://api.github.com/repos/decred/dcrdata/labels/needs%20testing","name":"needs testing","color":"c2e0c6","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":{"url":"https://api.github.com/repos/decred/dcrdata/milestones/13","html_url":"https://github.com/decred/dcrdata/milestone/13","labels_url":"https://api.github.com/repos/decred/dcrdata/milestones/13/labels","id":2724319,"number":13,"title":"tbd","description":"uncertain future","creator":{"login":"chappjc","id":9373513,"avatar_url":"https://avatars1.githubusercontent.com/u/9373513?v=4","gravatar_id":"","url":"https://api.github.com/users/chappjc","html_url":"https://github.com/chappjc","followers_url":"https://api.github.com/users/chappjc/followers","following_url":"https://api.github.com/users/chappjc/following{/other_user}","gists_url":"https://api.github.com/users/chappjc/gists{/gist_id}","starred_url":"https://api.github.com/users/chappjc/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/chappjc/subscriptions","organizations_url":"https://api.github.com/users/chappjc/orgs","repos_url":"https://api.github.com/users/chappjc/repos","events_url":"https://api.github.com/users/chappjc/events{/privacy}","received_events_url":"https://api.github.com/users/chappjc/received_events","type":"User","site_admin":false},"open_issues":2,"closed_issues":3,"state":"open","created_at":"2017-08-26T01:45:40Z","updated_at":"2018-04-17T21:54:29Z","due_on":null,"closed_at":null},"comments":26,"created_at":"2018-04-14T00:02:55Z","updated_at":"2018-05-02T23:40:22Z","closed_at":null,"author_association":"NONE","pull_request":{"url":"https://api.github.com/repos/decred/dcrdata/pulls/427","html_url":"https://github.com/decred/dcrdata/pull/427","diff_url":"https://github.com/decred/dcrdata/pull/427.diff","patch_url":"https://github.com/decred/dcrdata/pull/427.patch"},"body":"**New Addresses Table Schema Proposal**\r\n\r\n<table>\r\n  <tr>\r\n    <td></td>\r\n    <td>Columns</td>\r\n    <td>Data Type</td>\r\n    <td>Description</td>\r\n  </tr>\r\n  <tr>\r\n    <td>1.</td>\r\n    <td>id</td>\r\n    <td>SERIAL8 PRIMARY KEY</td>\r\n    <td>This is the primary key of the addresses table</td>\r\n  </tr>\r\n  <tr>\r\n    <td>2.</td>\r\n    <td>address</td>\r\n    <td>TEXT</td>\r\n    <td>Defines the actual address used to reference the actual transactions made.</td>\r\n  </tr>\r\n  <tr>\r\n    <td>3.</td>\r\n    <td>tx_hash</td>\r\n    <td>TEXT</td>\r\n    <td>Defines the funding/spending transaction hash as listed in the transactions table</td>\r\n  </tr>\r\n  <tr>\r\n    <td>4.</td>\r\n    <td>matching_tx_hash<br><del>in_out_row_id</del></td>\r\n    <td> TEXT<br><del>INT8</del></td>\r\n    <td>Defines a value that is is bi-directional i.e.\r\nFor a spending tx the value stored here should be the row id of the funding tx that credited it.\r\nFor a funding tx the value stored here should be the tx output that spends the amount credited by the funding tx.</td>\r\n  </tr>\r\n  <tr>\r\n    <td>5.</td>\r\n    <td>value</td>\r\n    <td>INT</td>\r\n    <td>Defines the actual amount that was either credited or debited by a given transaction input or output.</td>\r\n  </tr>\r\n  <tr>\r\n    <td>6.</td>\r\n    <td>block_time</td>\r\n    <td>INT8</td>\r\n    <td>- Define the time when the actual block was discovered.\r\n- Listed here in help order the transaction in a meaningful without using sql joins\r\n- Will reduce the number of queries made in order to display all the data that should appear on the `/address/{transaction}` route.  (History Page)</td>\r\n  </tr>\r\n  <tr>\r\n    <td>7.</td>\r\n    <td>is_funding</td>\r\n    <td>BOOLEAN\r\n</td>\r\n    <td>- Helps differentiate between funding and spending transactions.</td>\r\n  </tr>\r\n  <tr>\r\n    <td>***8.</td>\r\n    <td>tx_vin_vout_index <br> (Should be dropped)</td>\r\n    <td>INT8</td>\r\n    <td>Contains a value that references the value as listed in tx_index or prev_tx_index rows for vins table or just tx_index row for the vouts table.\r\n</td>\r\n  </tr>\r\n  <tr>\r\n    <td>***9.</td>\r\n    <td>tx_vin_vout_row_id <br> (Should be dropped)</td>\r\n    <td>INT8</td>\r\n    <td>Contains a value that references the value as listed in id row for the vins table or the vouts table. </td>\r\n  </tr>\r\n</table>\r\n\r\n**Additional Notes:**\r\n\r\nUnless persuaded otherwise, I am convinced that the **tx_vin_vout_index** and **tx_vin_vout_row_id**  (Columns with ***) are not that necessary. Tx_vin_vout_index is column merged from the formerly **spending_tx_vin_index** and **funding_tx_vout_index** columns. Tx_vin_vout_row_id is also a column merged from the formerly **vout_row_id** and **vin_row_id**.\r\n\r\nConsider the following points:\r\n\r\n* Vin_row_id and Vout_row_id need not to be referenced in the new table as either spending_tx_hash or funding_tx_hash that will now appear in the tx_hash can be used to fetch either of the details from the vins table or the vouts table.\r\n\r\n* The specific tx_hash also be used to fetch the tx_index and prev_tx_index values  from vins table or just tx_hash from vouts table that match what exactly is  to be stored in this table.\r\n\r\n**Conclusion.**\r\n\r\nUnless fetching the values to be stored in both **tx_vin_vout_index** and **tx_vin_vout_row_id** from either vins or vouts table might result to slow queries, we should do away with them in the addresses table as they store unnecessary data.\r\n\r\nWe should index column **block_time** so as to improve the queries performance. Most queries will either search, group or even order the queries output by the field. For unindexed search by field,  linear search is used but for indexed field binary search is used. I.e.\r\n\r\nBig O notation for Unindexed  = N \r\nfor 1 million records with each record taking 1ms to visit then the whole query should take:\r\n**Time taken : **1,000,000 x 1ms = 1,000,000ms\r\n\r\n Big O notation for Indexed  = Log 2N  \r\n**Time taken :** (Log 2 1,000,000) x 1ms = 19.931569 x 1ms = 19.931569ms    Appox. 20 ms\r\n\r\n**Caveat**\r\n\r\n* An extra set of data is added to table for every indexed column thus increasing the space consumed by the table.\r\n* Because two write operations are made one to the index (heap postgres and binary Trees for mysql) and the other to the table every write operation is slower\r\n\r\n**Appendix/Reference**\r\n\r\n* What is Column indexing: [https://stackoverflow.com/questions/1108/how-does-database-indexing-work](https://stackoverflow.com/questions/1108/how-does-database-indexing-work)\r\n\r\n**HOW TO TEST block_time INDEXING**\r\n\r\nAccess the command line version of postgres via `psql`  in your machine and run the following commands:\r\n```sql\r\nCREATE TABLE indexed_addr (id SERIAL8 PRIMARY KEY, address TEXT, tx_hash TEXT, in_out_row_id INT8, value INT8, block_time INT8 NOT NULL, is_funding BOOLEAN);\r\n\r\nCREATE INDEX block_time_index ON indexed_addr (block_time);\r\n\r\nCREATE TABLE unindexed_addr (id SERIAL8 PRIMARY KEY, address TEXT, tx_hash TEXT, in_out_row_id INT8, value INT8, block_time INT8 NOT NULL, is_funding BOOLEAN);\r\n```\r\n\r\nrun `\\timing` to activate query timing in postgres for that session.\r\n\r\nGet the sample data (I can't paste my zipped file with 2 million records here as its larger than 10 MB)\r\nAlternatively generate the sample data from the exist testnet data in the postgres by running this:\r\n\r\n```sql\r\n\\copy (select a.id, a.address, a.funding_tx_hash, t.id, a.value, t.block_time, case a.value when 0 then false else true end from addresses a, transactions t where a.funding_tx_hash = t.tx_hash limit 2000000) TO ~/sample_data.csv (format CSV, delimiter \",\");\r\n```\r\n\r\nLoad the sample data into indexed_addr and unindexed_addr tables\r\n```sql\r\n\\copy indexed_addr from ~/sample_data.csv (format CSV, delimiter \",\")\r\n\r\n\\copy unindexed_addr from ~/sample_data.csv (format CSV, delimiter \",\")\r\n```\r\n\r\nTest the index by running the following queries\r\n```sql\r\nselect count(*) from indexed_addr where block_time = 1512437355; => took 0.531 ms\r\n\r\nselect count(*) from unindexed_addr where block_time = 1512437355; => took 352.423 ms\r\n```\r\n<img width=\"401\" alt=\"screen shot 2018-04-14 at 03 17 40\" src=\"https://user-images.githubusercontent.com/22055953/38762217-8b8d689a-3f92-11e8-9b0a-6c6c05a53845.png\">\r\n\r\n\r\nRemember to drop the test tables after running tests on them.\r\n```sql\r\ndrop table indexed_addr, unindexed_addr;\r\n```\r\n"},"comment":{"url":"https://api.github.com/repos/decred/dcrdata/issues/comments/386153898","html_url":"https://github.com/decred/dcrdata/pull/427#issuecomment-386153898","issue_url":"https://api.github.com/repos/decred/dcrdata/issues/427","id":386153898,"user":{"login":"dmigwi","id":22055953,"avatar_url":"https://avatars1.githubusercontent.com/u/22055953?v=4","gravatar_id":"","url":"https://api.github.com/users/dmigwi","html_url":"https://github.com/dmigwi","followers_url":"https://api.github.com/users/dmigwi/followers","following_url":"https://api.github.com/users/dmigwi/following{/other_user}","gists_url":"https://api.github.com/users/dmigwi/gists{/gist_id}","starred_url":"https://api.github.com/users/dmigwi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dmigwi/subscriptions","organizations_url":"https://api.github.com/users/dmigwi/orgs","repos_url":"https://api.github.com/users/dmigwi/repos","events_url":"https://api.github.com/users/dmigwi/events{/privacy}","received_events_url":"https://api.github.com/users/dmigwi/received_events","type":"User","site_admin":false},"created_at":"2018-05-02T23:40:22Z","updated_at":"2018-05-02T23:40:22Z","author_association":"NONE","body":"@chappjc You plan has worked again, we have managed to reduce time taken to `Updating from vins 0 to 249999...` (using testnet) from 14 minutes to 6 minutes. More than 50% improvement.\r\n\r\n```\r\n2018-05-03 02:26:36.840 [INF] PSQL: Updating from vins 0 to 49999...\r\n2018-05-03 02:27:49.853 [INF] PSQL: Updating from vins 50000 to 99999...\r\n2018-05-03 02:29:00.044 [INF] PSQL: Updating from vins 100000 to 149999...\r\n2018-05-03 02:30:14.893 [INF] PSQL: Updating from vins 150000 to 199999...\r\n2018-05-03 02:31:32.159 [INF] PSQL: Updating from vins 200000 to 249999...\r\n2018-05-03 02:32:49.232 [INF] PSQL: Updating from vins 250000 to 299999...\r\n```\r\nThe latest update drops indexes on `block_time` and `matching_tx_hash` that existed before but maintains an index on `tx_hash` as it improves the speed of the query the fetches the repeated fields between an input and an output i.e. address and value of the tx output."}}
{"actor":{"display_login":"dmigwi"}}