0

I have a table in Postgres 9.6:

CREATE TABLE public.lml_data
(
    timestamp_from timestamp without time zone,
    timestamp_to timestamp without time zone NOT NULL,
    statcode character varying(16) COLLATE pg_catalog."default" NOT NULL,
    component character varying(16) COLLATE pg_catalog."default" NOT NULL,
    meetwaarde real,
    last_updated timestamp with time zone,
    CONSTRAINT pkey_lml_data PRIMARY KEY (timestamp_to, statcode, component)
)

It has an index:

CREATE INDEX lml_data_idx
    ON public.lml_data USING btree
    (timestamp_to DESC NULLS LAST, statcode COLLATE pg_catalog."default" ASC NULLS LAST, component COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

It has some 29000000 records. We always do a SELECT ... WHERE timestamp_to .. AND statcode= ... AND component=...

It returns approx 370 records. These are hourly values over two weeks (24x14). But it takes some 10 seconds to do this. Is this normal? I am a nitwit on indices and analyzing.

O, and the timestamp_to is on the fly converted to epoch by this: SELECT extract(epoch from a.timestamp_to AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Amsterdam')*1000 as tijd ... WHERE ...

Hope I am clear. Where can I speed up this?

The full SELECT is:

SELECT statcode, meetwaarde, extract(epoch from timestamp_to AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Amsterdam')*1000 as tijd 
FROM lml_data 
WHERE (statcode = 'NL10937' OR statcode = 'NL10934') 
  AND component='PM2.5' 
  AND date_trunc('second', timestamp_to AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Amsterdam') >= '2021-06-27 00:00:00' 
  AND date_trunc('second', timestamp_to AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Amsterdam') < '2021-07-04 23:59:59' 
ORDER BY tijd ASC;

And the output of EXPLAIN (analyze, buffers format text):

QUERY PLAN
    Sort  (cost=1410368.93..1410369.05 rows=46 width=21) (actual time=4992.986..4993.029 rows=384 loops=1)
    
          Sort Key: ((date_part('epoch'::text, timezone('Europe/Amsterdam'::text, timezone('UTC'::text, timestamp_to))) * '1000'::double precision))
          Sort Method: quicksort  Memory: 55kB
          Buffers: shared hit=12019 read=317705
          ->  Seq Scan on lml_data  (cost=0.00..1410367.66 rows=46 width=21) (actual time=1098.748..4992.564 rows=384 loops=1)
                Filter: (((component)::text = 'PM2.5'::text) AND (((statcode)::text = 'NL10937'::text) OR ((statcode)::text = 'NL10934'::text)) AND (date_trunc('second'::text, timezone('Europe/Amsterdam'::text, timezone('UTC'::text, timestamp_to))) >= '2021-06-27 00:00:00'::timestamp without time zone) AND (date_trunc('second'::text, timezone('Europe/Amsterdam'::text, timezone('UTC'::text, timestamp_to))) < '2021-07-04 23:59:59'::timestamp without time zone))
                Rows Removed by Filter: 28751951
                Buffers: shared hit=12019 read=317705
        Planning time: 33.623 ms
        Execution time: 4993.508 ms

Thanks in advance.

0

1 Answer 1

0

I assume you are storing timestamps in UTC, correct? You may want to try to convert your parameters instead of calling extract for every row. Example:

ts1 = f('2021-06-27 00:00:00') -- f transforms your argument from local timezone to utc
ts2 = f('2021-07-04 23:59:59') 

WHERE (statcode = 'NL10937' OR statcode = 'NL10934') 
  AND component='PM2.5' 
  AND timestamp_to >= ts1   
  AND timestamp_to < ts2
ORDER BY tijd ASC;

As of now your query is not utilizing any index but does a seq scan on the table. If your table contains 29000000 rows it will take some time to evaluate extract(...) twice for each row.

If you change the query as suggested above, the query may be able to utilize the index which will be much more efficient for a couple of hundred rows.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.