I have a huge table like following:

CREATE TABLE public.huge_table (
    sampl_day date NOT NULL,
    tick_time timestamp(6) with time zone NOT NULL,
    crit_feat integer NOT NULL,
    --- other fields
    CONSTRAINT pkey_huge_table PRIMARY KEY (tick_time)
CREATE INDEX idx_huge_table_day_time
ON public.huge_table USING brin (sampl_day, tick_time);

This table stores much many samples(rows) of a time series, one row for one sample. Most time, the field "crit_feat" has a very low value, for example 1 or 2. At some abnormal time point, it would go very high, for example 100.

It's very easy to find out these special rows in a single query. Let's call them as "special time points".

And now I need to view rows those happened in 5s before/after each of above special time points in a query result set, to find out what happened before/after a special event.

If I only need to find out a single one such special time point and rows in 5s before/after it, it is relatively easier.

I can do like this:

WITH spec_time as (
    SELECT tick_time tp
    FROM huge_table
    ORDER BY crit_feat DESC LIMIT 1)

FROM huge_table
WHERE tick_time BETWEEN (SELECT tp FROM spec_time) - INTERVAL '5s'
                    AND (SELECT tp FROM spec_time) + INTERVAL '5s'
ORDER BY tick_time;

But I need to do so with every special points and UNION all of them!

The number of all speical points is about 20k at least, and the events that should be selected out are NOT ONLY whose "crit_feat" field has the largest value, BUT ALSO include ones with a value greater than a specified threshold, for example: the median value of "crit_feat" field within entire data set.

I guess that I can get it with Temporary-Table and Cursor in a Storage Procedure/Function, but I'm expecting a simpler method!

Sorry about my ugly English, I hope that I have expressed correctly things what I want to say.



1 Answer 1


Pretty sure if I understand you correctly, you can accomplish your goal with just a self join like so:

SELECT Results.* -- Don't actually use *, it's an anti-pattern. Instead, list out only the fields you need.
FROM huge_table AS Threshold
INNER JOIN huge_table AS Results
    ON Threshold.tick_time 
            Results.tick_time - INTERVAL '5s'
            AND Results.tick_time + INTERVAL '5s'
WHERE Threshold.crit_feat >= 100 -- Input your threshold value here

You'll likely want an index minimally on (tick_time, crit_feat).

Also, the above may return the same row from the Results instance of the table multiple times, if it matches on multiple rows from the Threshold instance of the table. If that's so, and you want the unique list of rows, you can just add the DISTINCT keyword after the SELECT keyword.

  • Your solution is perfect! Thanks!
    – Leon
    Apr 26 at 17:06
  • And would you please to explain me : Why should I make an index on (tick_time, crit_feat), instead of (crit_feat)? In my mind, it's enough to filter the data set on the alias Threshold. I have tested with both of two respectively. Of course, (tick_time, crit_feat) can be used with, and (crit_feat) can NOT. I'm just wondering why.
    – Leon
    Apr 26 at 17:13
  • @Leon No problem, glad to be of help! I'm guessing that (tick_time, crit_feat) is a good pick because it covers both use cases in the query: 1. the predicates used by Threshold which is both fields - one in the INNER JOIN and the other in the WHERE clause, and 2. the predicate used for Results which is just the tick_time field. An index on only crit_feat does not cover the INNER JOIN predicate using the tick_time field.
    – J.D.
    Apr 26 at 17:44
  • But when we are filtering Threshold, only field crit_feat will be checked, so an index only on it is nessary? As to Results, there is already an index(primary key) on it, enough to join it with Threshold. I know I'm wrong, but I don't know why. haha!
    – Leon
    Apr 27 at 0:34
  • 1
    Got it! I appreciate !!!
    – Leon
    Apr 27 at 8:23

Your Answer

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

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