query very slow
Hi Uwe, on of our users informed us that the following query took her very long (after 2 hours she stopped the process).
WITH source_ids AS
(
SELECT source_id::integer
FROM source
WHERE source.NAME = 'prgB515')
SELECT signal.timestamp,
value,
parameter.unit,
parameter.NAME,
source.NAME,
source.serial,
source_type.NAME,
site.NAME
FROM signal
INNER JOIN site
ON signal.site_id = site.site_id
INNER JOIN parameter
ON signal.parameter_id = parameter.parameter_id
INNER JOIN source
ON signal.source_id = source.source_id
INNER JOIN source_type
ON source.source_type_id = source_type.source_type_id
WHERE signal.source_id = ANY(array
(
select source_id::integer
FROM source_ids))
AND '2021-08-31 00:00:00'::timestamp <= signal.timestamp
AND signal.timestamp <= '2021-09-02 00:00:00'::timestamp
GROUP BY signal.timestamp,
signal.value,
signal.signal_id,
parameter.NAME,
parameter.unit,
source_type.NAME,
source.NAME,
source.serial,
site.NAME
ORDER BY signal.timestamp ASC;
Could you look into this?
Thanks and best, Carina
Edited by schmittu