Draft: (3) hivemind REST API

#60 (closed) block_explorer_ui#593 (closed)

Filtering by transacting_account_id (headblock) (related to block_explorer_ui#593 (closed) )

---------------------------HAFAH VERSION, EXCLUDE (INCOMING OPERATIONS)------------------------------

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('arcange', 'arcange', 'exclude')
--Execution Time: 166.603 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('abit', 'abit', 'exclude')
--Execution Time: 500.454 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('ocd-witness', 'ocd-witness', 'exclude')
--Execution Time: 120.706 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('good-karma', 'good-karma', 'exclude')
--Execution Time: 190.379 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('roelandp', 'roelandp', 'exclude')
--Execution Time: 118.723 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('quochuy', 'quochuy', 'exclude')
--Execution Time: 64.885 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('blocktrades', 'blocktrades', 'exclude')
--Execution Time: 59.236 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('actifit', 'actifit', 'exclude')
--Execution Time: 39.650 ms

-----------------------------------------------------------------------------------------
--slow when corner case happens - needs optimizations

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('deathwing', 'deathwing', 'exclude')
--Execution Time: 2665.695 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('neoxian', 'neoxian', 'exclude')
--Execution Time: 1349.172 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('gtg', 'gtg', 'exclude')
--Execution Time: 641.571 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('threespeak', 'threespeak', 'exclude')
--Execution Time: 355.844 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('pharesim', 'pharesim', 'exclude')
--Execution Time: 644.228 ms

---------------------------INCOMING TRANFER/COMMNENT-------------------------------------
--it works much better when filtered by specific operation type - the corner case has less chance to happen
EXPLAIN ANALYZE --incomming comments
SELECT * FROM hafah_endpoints.get_ops_by_account('gtg', 'gtg', 'exclude', '1')
--Execution Time: 238.400 ms

EXPLAIN ANALYZE --incomming transfers
SELECT * FROM hafah_endpoints.get_ops_by_account('gtg', 'gtg', 'exclude', '2')
--Execution Time: 453.929 ms

EXPLAIN ANALYZE --incomming comments
SELECT * FROM hafah_endpoints.get_ops_by_account('blocktrades', 'blocktrades', 'exclude', '1')
--Execution Time: 340.420 ms

EXPLAIN ANALYZE --incomming transfers
SELECT * FROM hafah_endpoints.get_ops_by_account('blocktrades', 'blocktrades', 'exclude', '2')
--Execution Time: 97.488 ms


---------------------------HAFAH VERSION, EXCLUDE (FILTER OUT SPECIFIC ACCOUNT FROM RESULT)------------------------------
EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('pharesim', 'arcange', 'exclude')
--Execution Time: 60.021 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('arcange', 'abit', 'exclude')
--Execution Time: 40.605 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('abit', 'blocktrades', 'exclude')
--Execution Time: 99.132 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('ocd-witness', 'good-karma', 'exclude')
--Execution Time: 49.807 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('good-karma', 'threespeak', 'exclude')
--Execution Time: 30.179 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('threespeak', 'actifit', 'exclude')
--Execution Time: 57.380 ms

---------------------------HAFAH VERSION, INCLUDE (OUTGOING OPERATIONS)------------------------------
--It may be faster after change to transacting_account_id (transacting_account_id NULL for virtual ops)
--current version must filter out these rows by filtering out virtual ops
EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('pharesim', 'pharesim', 'include')
--Execution Time: 365.138 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('arcange', 'arcange', 'include')
--Execution Time: 542.904 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('abit', 'abit', 'include')
--Execution Time: 247.547 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('ocd-witness', 'ocd-witness', 'include')
--Execution Time: 229.736 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('good-karma', 'good-karma', 'include')
--Execution Time: 563.477 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('threespeak', 'threespeak', 'include')
--Execution Time: 277.567 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('actifit', 'actifit', 'include')
--Execution Time: 455.008 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('neoxian', 'neoxian', 'include')
--Execution Time: 182.097 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('gtg', 'gtg', 'include')
--Execution Time: 218.541 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('blocktrades', 'blocktrades', 'include')
--Execution Time: 552.457 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('deathwing', 'deathwing', 'include')
--Execution Time: 620.666 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('roelandp', 'roelandp', 'include')
--Execution Time: 510.078 ms

EXPLAIN ANALYZE
SELECT * FROM hafah_endpoints.get_ops_by_account('quochuy', 'quochuy', 'include')
--Execution Time: 669.751 ms

---------------------------OUTGOING TRANFER/COMMNENT-------------------------------------

EXPLAIN ANALYZE --outgoing comments
SELECT * FROM hafah_endpoints.get_ops_by_account('gtg', 'gtg', 'include', '1')
--Execution Time: 31.042 ms

EXPLAIN ANALYZE --outgoing transfers
SELECT * FROM hafah_endpoints.get_ops_by_account('gtg', 'gtg', 'include', '2')
--Execution Time: 84.891 ms

EXPLAIN ANALYZE --outgoing comments
SELECT * FROM hafah_endpoints.get_ops_by_account('blocktrades', 'blocktrades', 'include', '1')
--Execution Time: 24.505 ms

EXPLAIN ANALYZE --outgoing transfers
SELECT * FROM hafah_endpoints.get_ops_by_account('blocktrades', 'blocktrades', 'include', '2')
--Execution Time: 229.790 ms