Speed up the performance of this endpoint

[dadiorchen]

The API like this:https://prod-k8s.treetracker.org/treetracker/captures?tree_associated=false&limit=1&offset=0&captured_at_start_date=2022-08-01&captured_at_end_date=2022-08-01&&matchting_tree_distance=6&matchting_tree_time_range=30

It takes about 8s to finish, and if we wider the time range, it would be much worse, so we need to find out how to speed up it.

[dadiorchen]

basically, the goal is to find a way to speed up this SQL

select count(*) from "capture" where (not "capture"."status" = 'deleted' and "tree_id" is null 
    and "id" in (
        select 
          distinct(tc.id)
        from capture tc 
        JOIN tree tt ON
          ST_DWithin(
            tc.estimated_geographic_location,
            tt.estimated_geographic_location,
          6)
          AND tc.captured_at > tt.created_at + INTERVAL '30 DAYS' 
          AND tc.captured_at >= '2022-08-01 00:00:00'
          AND tc.captured_at <= '2022-08-01 23:59:59'
      ) 
      and "capture"."captured_at" >= '2022-08-01 00:00:00' and "capture"."captured_at" <= '2022-08-01 23:59:59')

[andrewsu31098]

I'll look into this one.

[andrewsu31098]

Hi @dadiorchen. Setting up the database in "db_migrate" is not working because "database.json" is missing. I think I'll need a doctl dev token.

[dadiorchen]

Are you in our Slack community? If not please leave your email so I can invite you?

[yi-xuan-97]

I would like to take on this task