gistfile1.sql

set enable_seqscan = on;
--set random_page_cost = 3.0;
set search_path to 'aatams', 'public';

--explain
with rxr_filtered_dets as (
select valid_detection.id as id
from valid_detection
join receiver_deployment on valid_detection.receiver_deployment_id = receiver_deployment.id
join installation_station on receiver_deployment.station_id = installation_station.id
join installation on installation_station.installation_id = installation.id
join project rxr_project on installation.project_id = rxr_project.id

where

rxr_project.name = 'AATAMS Heron Island'
),

txr_filtered_dets as (
select valid_detection.id as id from valid_detection
left join sensor on valid_detection.transmitter_id = sensor.transmitter_id
join device tag on sensor.tag_id = tag.id
join surgery on tag.id = surgery.tag_id
join animal_release on surgery.release_id = animal_release.id
join project txr_project on animal_release.project_id = txr_project.id
join animal on animal_release.animal_id = animal.id
join species on animal.species_id = species.id

where spcode = '37018038'  -- 37010003
),

det_filtered_dets as (
select valid_detection.id as id from valid_detection
--where transmitter_id = 'A69-9002-15920'
where timestamp between '2011-03-20' and '2012-04-01'
--and receiver_name = 'VR2W-106883'
),

filtered_dets as (
    select * from rxr_filtered_dets
    intersect
    select * from txr_filtered_dets
    intersect
    select * from det_filtered_dets
)

--select count(*) from valid_detection
--join filtered_dets on valid_detection.id = filtered_dets.id;

select * from valid_detection
join filtered_dets on valid_detection.id = filtered_dets.id
ORDER BY timestamp, receiver_name, valid_detection.transmitter_id
LIMIT 20