Query optimisations using exists

[hylkevds]

There is a large potential for optimisation in the SQL queries that FROST generates when doing filters along one-to-many relations (like Thing -> Datastreams -> Observations)

The current query that FROST generates takes 75 seconds:

 explain analyze select
	distinct "e1"."PROPERTIES",
	"e1"."ID",
	"e1"."NAME",
	"e1"."DESCRIPTION"
from
	"THINGS" as "e1"
join "DATASTREAMS" as "e2" on
	"e2"."THING_ID" = "e1"."ID"
join "OBS_PROPERTIES" as "e3" on
	"e3"."ID" = "e2"."OBS_PROPERTY_ID"
join "OBSERVATIONS" as "e4" on
	"e2"."ID" = "e4"."DATASTREAM_ID"
where
	(1 = 1
	and "e3"."ID" = 1340
	and "e4"."RESULT_NUMBER" < 10
	and "e4"."RESULT_QUALITY" like ('%' || replace(replace(replace('http://id.eaufrance.fr/nsa/446#2', '!', '!!'), '%', '!%'), '_', '!_') || '%') escape '!'
	and "e4"."RESULT_TIME" < timestamp with time zone '2016-01-14 00:00:00+00:00')
limit 101;

By rewriting this to use exists instead of join & distinct the result can be had in 0.58 seconds:

explain analyze select
	"e1"."PROPERTIES",
	"e1"."ID",
	"e1"."NAME",
	"e1"."DESCRIPTION"
from
	"THINGS" as "e1"
where
	(1 = 1
	and exists(
	select
		1
	from
		"DATASTREAMS" as "e2"
	join "OBS_PROPERTIES" as "e3" on
		"e3"."ID" = "e2"."OBS_PROPERTY_ID"
	where
		"e2"."THING_ID" = "e1"."ID"
		and "e3"."ID" = 1340
		and exists(
		select
			1
		from
			"OBSERVATIONS" as "e4"
		where
			"e2"."ID" = "e4"."DATASTREAM_ID"
			and "e4"."RESULT_NUMBER" < 10
			and "e4"."RESULT_QUALITY" like ('%' || replace(replace(replace('http://id.eaufrance.fr/nsa/446#2', '!', '!!'), '%', '!%'), '_', '!_') || '%') escape '!'
			and "e4"."PHENOMENON_TIME_START" < timestamp with time zone '2016-01-14 00:00:00+00:00' ) ) )
limit 101