Currently the query: https://prod-k8s.treetracker.org/api/admin/api/trees?filter={%22where%22:{%22approved%22:false,%22active%22:true,%22organizationId%22:194,%22stakeholderUUID%22:%2235e1c708-267b-4dd9-84be-868a954b0807%22},%22order%22:[%22id%20desc%22],%22limit%22:24,%22skip%22:240,%22fields%22:{%22uuid%22:true,%22imageUrl%22:true,%22lat%22:true,%22lon%22:true,%22id%22:true,%22timeCreated%22:true,%22timeUpdated%22:true,%22active%22:true,%22approved%22:true,%22planterId%22:true,%22deviceIdentifier%22:true,%22planterIdentifier%22:true,%22speciesId%22:true,%22tokenId%22:true,%22morphology%22:true,%22age%22:true,%22captureApprovalTag%22:true,%22rejectionReason%22:true,%22note%22:true}}
is slow, the explanation and execution of the SQL:
treetracker=> explain select * from trees
treetracker-> where
treetracker-> (
treetracker(> planting_organization_id in (select entity_id from getEntityRelationshipChildren(194))
treetracker(> )
treetracker-> order by id desc
treetracker-> limit 96
treetracker-> offset 1000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Limit (cost=331855.11..363711.94 rows=96 width=917)
-> Nested Loop (cost=13.18..101083046.49 rows=304612 width=917)
Join Filter: (trees.planting_organization_id = getentityrelationshipchildren.entity_id)
-> Index Scan Backward using trees_id_pkey on trees (cost=0.43..732516.99 rows=5882438 width=917)
-> HashAggregate (cost=12.75..14.75 rows=200 width=4)
Group Key: getentityrelationshipchildren.entity_id
-> Function Scan on getentityrelationshipchildren (cost=0.25..10.25 rows=1000 width=4)
(7 rows)
Time: 2.154 ms
treetracker=> select * from trees
treetracker-> where
treetracker-> (
treetracker(> planting_organization_id in (select entity_id from getEntityRelationshipChildren(194))
treetracker(> )
treetracker-> order by id desc
treetracker-> limit 96
treetracker-> offset 1000;
treetracker=>
treetracker=>
treetracker=> select * from trees
treetracker-> where
treetracker-> (
treetracker(> planting_organization_id in (select entity_id from getEntityRelationshipChildren(194))
treetracker(> )
treetracker-> order by id desc
treetracker-> limit 2
treetracker-> offset 1000;
id | time_created | time_updated | missing | priority | cause_of_death_id | planter_id | primary_location_
id | settings_id | override_settings_id | dead | photo_id |
image_url
| certificate_id | estimated_geometric_location | lat | lon
| gps_accuracy | active | planter_photo_url | planter_identifier | device_id | sequence | note | verified |
uuid | approved | status | cluster_regions_assigned | species_id | planting_organization_id | payment_id
| contract_id | token_issued | morphology | age | species | capture_approval_tag | rejection_reason | matching_hash | de
vice_identifier | images | domain_specific_data | image_url_backup | token_id | name | earnings_i
d | session_id
--------+---------------------+---------------------+---------+----------+-------------------+------------+------------------
---+-------------+----------------------+------+----------+------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------
-----------------+----------------+----------------------------------------------------+--------------------+----------------
----+--------------+--------+-------------------+--------------------+-----------+----------+------+----------+--------------
------------------------+----------+---------+--------------------------+------------+--------------------------+------------
+-------------+--------------+------------+----------+---------+----------------------+------------------+---------------+---
----------------+--------+----------------------+------------------+--------------------------------------+------+-----------
--+--------------------------------------
472791 | 2020-11-27 14:28:33 | 2020-11-27 14:28:33 | f | f | | 3272 |
| | | 0 | | https://treetracker-production-images.s3.eu-central-1.amazonaws.c
om/2020.11.29.12.35.24_18.28512164298445_-73.56352591887116_b37c4484-c427-4ba4-a38e-a810e9c71b38_IMG_20201127_092832_29320605
89995952190.jpg | | 0101000020E6100000000004CF106452C0000064BBFD483240 | 18.28512164298445 | -73.56352591887
116 | 9 | t | | 46964146 | | | | f | 1e185606-1279
-4383-8816-c39f8d6d8d07 | t | planted | t | 331 | 194 |
| | f | seedling | new_tree | | fruit | | | bd
b06f0ac235df94 | {} | {} | | 1729897b-5e53-4fd4-9742-19b55c792f7e | |
| cd2e2d3f-2f6a-5abd-b2ae-2fd6cf3a101b
472790 | 2020-11-27 14:28:05 | 2020-11-27 14:28:05 | f | f | | 3272 |
| | | 0 | | https://treetracker-production-images.s3.eu-central-1.amazonaws.c
om/2020.11.29.12.35.21_18.285042098723352_-73.56351166963577_d03aff17-54df-436f-b321-a6132227708b_IMG_20201127_092804_4411297
485046996398.jpg | | 0101000020E610000000004093106452C00000DC84F8483240 | 18.285042098723352 | -73.56351166963
577 | 6 | t | | 46964146 | | | | f | 9afafad7-a382
-4fa7-bb2d-8f3a4deb4105 | t | planted | t | 331 | 194 |
| | f | seedling | new_tree | | fruit | | | bd
b06f0ac235df94 | {} | {} | | 2f415bb2-8f11-4ac5-83f0-ee0b77fbfc76 | |
| cd2e2d3f-2f6a-5abd-b2ae-2fd6cf3a101b
(2 rows)
Time: 170853.592 ms (02:50.854)
Seems the function is a reason to trigger id scan, in the web map case, the SQL below is in a better result, maybe we can use this to speed up the admin panel:
treetracker=> WITH RECURSIVE organization_children AS (
treetracker(> SELECT entity.id, entity_relationship.parent_id, 1 as depth, entity_relationship.type, entity_relati
onship.role
treetracker(> FROM entity
treetracker(> LEFT JOIN entity_relationship ON entity_relationship.child_id = entity.id
treetracker(> WHERE entity.id = 194
treetracker(> UNION
treetracker(> SELECT next_child.id, entity_relationship.parent_id, depth + 1, entity_relationship.type, entity_rel
ationship.role
treetracker(> FROM entity next_child
treetracker(> JOIN entity_relationship ON entity_relationship.child_id = next_child.id
treetracker(> JOIN organization_children c ON entity_relationship.parent_id = c.id
treetracker(> )
treetracker-> ,org_tree_id AS (
treetracker(> SELECT id FROM (
treetracker(> SELECT trees.id as id from trees
treetracker(> WHERE
treetracker(> planter_id IN (
treetracker(> SELECT id FROM planter
treetracker(> JOIN (
treetracker(> SELECT id AS entity_id FROM organization_children LIMIT 20
treetracker(> ) org ON planter.organization_id = org.entity_id
treetracker(> )
treetracker(> UNION
treetracker(> select id from trees where planting_organization_id = (
treetracker(> 194
treetracker(> )
treetracker(> ) ids
treetracker(>
treetracker(> )
treetracker-> select * from trees
treetracker-> where
treetracker-> (
treetracker(> id in (select id from org_tree_id)
treetracker(> )
treetracker-> order by id desc
treetracker-> limit 2
treetracker-> offset 1000;
id | time_created | time_updated | missing | priority | cause_of_death_id | planter_id | primary_location
_id | settings_id | override_settings_id | dead | photo_id |
image_url
| certificate_id | estimated_geometric_location | lat | lon
| gps_accuracy | active | p
lanter_photo_url | planter_iden
tifier | device_id | sequence | note | verified | uuid | approved | status | cluster_regions
_assigned | species_id | planting_organization_id | payment_id | contract_id | token_issued | morphology | age | species | ca
pture_approval_tag | rejection_reason | matching_hash | device_identifier | images | domain_specific_data | image_url_backup
| token_id | name | earnings_id | session_id
---------+---------------------+---------------------+---------+----------+-------------------+------------+-----------------
----+-------------+----------------------+------+----------+-----------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------
-----------------+----------------+----------------------------------------------------+--------------------+----------------
----+--------------+--------+------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------+-------------
-------+-----------+----------+------+----------+--------------------------------------+----------+---------+----------------
----------+------------+--------------------------+------------+-------------+--------------+------------+-----+---------+---
-------------------+------------------+---------------+-------------------+--------+----------------------+------------------
+----------+------+-------------+------------
3673267 | 2022-11-23 12:10:08 | 2022-11-23 12:10:08 | f | f | | 5865 |
| | | 0 | | https://treetracker-production-images.s3.eu-central-1.amazonaws.
com/2022.11.23.11.36.42_18.295119434595108_-73.5591351930052_9344a485-d119-46d3-9912-3fdc38942e45_IMG_20221123_071006_5287675
375067452884.jpg | | 0101000020E61000000000FADEC86352C0000080F28C4B3240 | 18.295119434595108 | -73.5591351930
052 | | t | https://treetracker-production-images.s3.eu-central-1.amazonaws.com/2022.05.15.08.33.25_18.2946
49268500507_-73.55373761616647_9b26ff3c-b2b0-418a-87ba-93b9620c06e4_IMG_20220512_075859_334598811520482040.jpg | 44882359
| | | | f | b22facc9-09d9-4c64-ab68-229981dbd8c1 | f | planted | t
| | | | | f | | | |
| | | cf1616c5c72ce3c9 | | |
| | | |
3673266 | 2022-11-23 12:09:56 | 2022-11-23 12:09:56 | f | f | | 5865 |
| | | 0 | | https://treetracker-production-images.s3.eu-central-1.amazonaws.
com/2022.11.23.11.36.40_18.29509348422289_-73.55913235992193_838c8544-4ee7-4211-a8aa-9e376a9df216_IMG_20221123_070952_2436247
732041458036.jpg | | 0101000020E6100000000018D3C86352C00000203F8B4B3240 | 18.29509348422289 | -73.55913235992
193 | | t | https://treetracker-production-images.s3.eu-central-1.amazonaws.com/2022.05.15.08.33.25_18.2946
49268500507_-73.55373761616647_9b26ff3c-b2b0-418a-87ba-93b9620c06e4_IMG_20220512_075859_334598811520482040.jpg | 44882359
| | | | f | b6a33ce1-4a78-4b03-9957-6480114fe934 | f | planted | t
| | | | | f | | | |
| | | cf1616c5c72ce3c9 | | |
| | | |
(2 rows)
Time: 65904.394 ms (01:05.904)
Currently the query:
https://prod-k8s.treetracker.org/api/admin/api/trees?filter={%22where%22:{%22approved%22:false,%22active%22:true,%22organizationId%22:194,%22stakeholderUUID%22:%2235e1c708-267b-4dd9-84be-868a954b0807%22},%22order%22:[%22id%20desc%22],%22limit%22:24,%22skip%22:240,%22fields%22:{%22uuid%22:true,%22imageUrl%22:true,%22lat%22:true,%22lon%22:true,%22id%22:true,%22timeCreated%22:true,%22timeUpdated%22:true,%22active%22:true,%22approved%22:true,%22planterId%22:true,%22deviceIdentifier%22:true,%22planterIdentifier%22:true,%22speciesId%22:true,%22tokenId%22:true,%22morphology%22:true,%22age%22:true,%22captureApprovalTag%22:true,%22rejectionReason%22:true,%22note%22:true}}is slow, the explanation and execution of the SQL:Seems the function is a reason to trigger id scan, in the web map case, the SQL below is in a better result, maybe we can use this to speed up the admin panel: