CODEWITHSUNDEEP
sqlpostgresqltimescaledb
jheddings
jheddings

Reputation: 27573

How can I speed up this SQL query for finding previous entries "on this day"?

I'm trying to speed up this PostgreSQL query to find previous entries "on this day" in past years from a table. I currently have the query below:

select * from sample
where date_part('month', "timestamp") = date_part('month', now())
and date_part('day', "timestamp") = date_part('day', now())
order by "timestamp" desc;

This seems to get the intended result, but it is running much slower than desired. Is there a better approach for comparing the current month & day?

Also, would there be any changes to do a similar search for "this hour" over the past years? Similar to the following:

select * from sample
where date_part('month', "timestamp") = date_part('month', now())
and date_part('day', "timestamp") = date_part('day', now())
and date_part('hour', "timestamp") = date_part('hour', now())
order by "timestamp" desc;

The data is time-series in nature, using TimescaleDB as the database. Here is the current definition:

CREATE TABLE public.sample (
    "timestamp" timestamptz NOT NULL DEFAULT now(),
    entity varchar(256) NOT NULL,
    quantity numeric NULL
);
CREATE INDEX sample_entity_time_idx ON public.sample (entity, "timestamp" DESC);
CREATE INDEX sample_time_idx ON public.sample ("timestamp" DESC);

Upvotes: 3

Views: 119

Answers (1)

The Impaler
The Impaler

Reputation: 48800

If you need the same day in all previous years that I would guess that the query would return 1/365th of the rows; that's a 0.27% selectivity. Great.

With that selectivity an index can speed up the query significantly. Now, since you are selecting non-consecutive rows you'll need a functional index. I would try:

create index ix1 on sample ((date_part('doy', "timestamp")));

Then, you can modify your query to:

select * 
from sample
where date_part('doy', "timestamp") = date_part('doy', now())
order by "timestamp" desc;

For the current hour in the past years you would have an even better selectivity of around 1/365/24; that is 0.01%. Awesome.

create index ix2 on sample (
  (date_part('doy', "timestamp")), 
  (date_part('hour', "timestamp"))
);

Then, the new query could look like:

select * 
from sample
where date_part('doy', "timestamp") = date_part('doy', now()) 
  and date_part('hour', "timestamp") = date_part('hour', now()) 
order by "timestamp" desc;

Please post the execution plans of these queries with the indexes created. I'm curious to see how well the perform.

Upvotes: 2

Related Questions