Time filter optimisation

[ndevilleBE]

Dear,
I'm using the dockerized version 2.0.4 of the Sensorthings server with a PostgreSQL 14 database.
I'm trying to optimize the time of filtering options and I came to a weird behaviour.

It seems that when the filter 'overlaps' is combined with other options (e.g. orderby), it slows the answer, while it should be faster as the amount of data to process is reduced.
For instance:

https://sensors.naturalsciences.be/sta/v1.1/Datastreams(5)/Observations?$orderby=result%20asc
-> response time = 476ms with "@iot.count": 247207

https://sensors.naturalsciences.be/sta/v1.1/Datastreams(5)/Observations?$filter=overlaps(phenomenonTime,%202020-09-01T00:00:00Z/2021-01-01T00:00:00Z)&$orderby=result%20asc
-> response time = 2585 ms with "@iot.count": 1761

I need to optimize at least two requests:

1. filter by time (e.g. select 1 year of data)
   1.1) filter by result
   1.2) filter by bounding box

The current indexes for the Observation table I have are:

• index on datastream ID, phenomenonTime asc
• index on result asc
• index on Feature ID

Any suggestions on the possible optimization steps I could implement?

Many thanks for your help

[hylkevds]

Some information about this is on the Performance tuning page.
The short version:

• set the option persistence.slowQueryThreshold to get the exact queries that are slow
• Use explain analyze to see what PostgreSQL is doing exactly for those queries

Also check when the last time was that the tables were analyzed, since that is often the cause for PostgreSQL not using the indexes one expects it to use. You can check the vacuum and analyse state using this query:

SELECT schemaname as schema,relname,n_live_tup,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze,vacuum_count as vc, autovacuum_count as avc 
  FROM pg_stat_user_tables where schemaname='public'
  ORDER BY n_live_tup DESC;

[ndevilleBE]

Dear Hylke,
useful and clear, as always!
It happens that PosgreSQL is using both phenomenon_time_start AND phenomenon_time_end to filter data.
I added an index on phenomenon_time_end.

Also I noticed that when I asked to order by 'result', the url is translated in PostgreSQL to:
order by "e0"."RESULT_NUMBER" asc, "e0"."RESULT_BOOLEAN" asc, "e0"."RESULT_STRING" asc, "e0"."RESULT_JSON" asc, "e0"."RESULT_TYPE" asc, "e0"."ID" asc

I changed my index on result_number to included all the results mentioned in the query.

I noticed also that using the TOP function can have a strong impact on the response time.
Without it, the SQL query return the 101 first row->

explain analyze
select "e0"."RESULT_BOOLEAN", "e0"."RESULT_QUALITY", "e0"."PHENOMENON_TIME_START", "e0"."PARAMETERS", "e0"."DATASTREAM_ID", "e0"."RESULT_STRING", "e0"."RESULT_TYPE", "e0"."VALID_TIME_END", "e0"."PHENOMENON_TIME_END", "e0"."FEATURE_ID", "e0"."ID", "e0"."RESULT_JSON", "e0"."RESULT_TIME", "e0"."RESULT_NUMBER", "e0"."VALID_TIME_START", "e0"."MULTI_DATASTREAM_ID" from "OBSERVATIONS" as "e0" join "DATASTREAMS" as "e1" on "e1"."ID" = "e0"."DATASTREAM_ID" where (true and "e1"."ID" = 5 and (not (("e0"."PHENOMENON_TIME_START" >= timestamp '2021-01-01 00:00:00.0' or timestamp '2020-09-01 00:00:00.0' >= "e0"."PHENOMENON_TIME_END")) or "e0"."PHENOMENON_TIME_START" = timestamp '2020-09-01 00:00:00.0')) order by "e0"."RESULT_NUMBER" asc, "e0"."RESULT_BOOLEAN" asc, "e0"."RESULT_STRING" asc, "e0"."RESULT_JSON" asc, "e0"."RESULT_TYPE" asc, "e0"."ID" asc offset 0 rows fetch next 101 rows only

Limit (cost=1187.65..20054.33 rows=101 width=309) (actual time=2380.337..2444.567 rows=101 loops=1)

With top = 10000 ->

explain analyze
select "e0"."RESULT_BOOLEAN", "e0"."RESULT_QUALITY", "e0"."PHENOMENON_TIME_START", "e0"."PARAMETERS", "e0"."DATASTREAM_ID", "e0"."RESULT_STRING", "e0"."RESULT_TYPE", "e0"."VALID_TIME_END", "e0"."PHENOMENON_TIME_END", "e0"."FEATURE_ID", "e0"."ID", "e0"."RESULT_JSON", "e0"."RESULT_TIME", "e0"."RESULT_NUMBER", "e0"."VALID_TIME_START", "e0"."MULTI_DATASTREAM_ID" from "OBSERVATIONS" as "e0" join "DATASTREAMS" as "e1" on "e1"."ID" = "e0"."DATASTREAM_ID" where (true and "e1"."ID" = 5 and (not (("e0"."PHENOMENON_TIME_START" >= timestamp '2021-01-01 00:00:00.0' or timestamp '2020-07-01 00:00:00.0' >= "e0"."PHENOMENON_TIME_END")) or "e0"."PHENOMENON_TIME_START" = timestamp '2020-07-01 00:00:00.0')) order by "e0"."RESULT_NUMBER" asc, "e0"."RESULT_BOOLEAN" asc, "e0"."RESULT_STRING" asc, "e0"."RESULT_JSON" asc, "e0"."RESULT_TYPE" asc, "e0"."ID" asc offset 0 rows fetch next 10001 rows only

Limit (cost=30800.12..30814.59 rows=5786 width=309) (actual time=38.025..38.227 rows=1761 loops=1)

The response time is divided by 62... It makes absolutely no sense to me. Any idea?

Kind regards

[ndevilleBE]

Well well I think got it.
For a small request (e.g. 100 rows), PostgreSQL planner makes use of an Index scan on the Observations table.
For larger request (in my case above 158 rows), the planner switches to Bitmap scanning, which is way faster.

Changing the defaultTop parameter to 200 improved the response time from 2585 ms to 78 ms.