The answer is to create a generated column as follows (all of the code below is available on the fiddle here):
I had an original answer (shown at end of answer), but I've revised it to use a Generated Column
(aka "Computed" or "Virtual" column) instead of an Expression Index
(aka "Functional Index").
This has the advantages of:
There's one disadvantage in that it uses more space, but I've found that this is not normally a critical issue (never seen it myself). Unfortunately, PostgreSQL does not yet have virtual generated columns - see link.
Your table definition should be as follows:
CREATE TABLE t
(
ticktime TIMESTAMPTZ,
broktime TIMESTAMPTZ,
trade_day DATE,
--
-- other fields
--
abs_b_minus_t INTERVAL GENERATED ALWAYS AS (GREATEST(broktime, ticktime) - LEAST(broktime, ticktime)) STORED
);
Then create an index on abs_b_minus_t
:
CREATE INDEX t_ix ON t
USING BRIN (trade_day, abs_b_minus_t );
Populate:
INSERT INTO t VALUES
('2022-02-14 14:43:55'::TIMESTAMPTZ, '2022-02-14 12:43:55'::TIMESTAMPTZ, '2022-02-14'::DATE),
('2022-03-14 14:43:55'::TIMESTAMPTZ, '2022-02-14 12:43:55'::TIMESTAMPTZ, '2022-03-14'::DATE),
('2022-02-14 14:43:55'::TIMESTAMPTZ, '2022-05-14 12:43:55'::TIMESTAMPTZ, '2022-02-14'::DATE);
Then we run:
SELECT
ticktime - broktime AS t_minus_b,
abs_b_minus_t
FROM t;
Result:
t_minus_b abs_b_minus_t
02:00:00 02:00:00
28 days 02:00:00 28 days 02:00:00
-88 days -21:00:00 88 days 21:00:00
So, we see that it's working - we are obtaining absolute values of the difference between broktime
and tradtime
.
Now, we can check index usage - we run SET enable_seqscan = OFF;
and then:
EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT
broktime - ticktime
FROM t
WHERE abs_b_minus_t < INTERVAL '30 DAYS';
Result:
QUERY PLAN
Bitmap Heap Scan on public.t (cost=12.14..39.07 rows=423 width=16) (actual time=0.022..0.025 rows=2 loops=1)
Output: (broktime - ticktime)
Recheck Cond: (t.abs_b_minus_t < '30 days'::interval)
Rows Removed by Index Recheck: 1
Heap Blocks: lossy=1
Buffers: shared hit=3
-> Bitmap Index Scan on t_ix (cost=0.00..12.03 rows=1270 width=0) (actual time=0.017..0.017 rows=10 loops=1)
Index Cond: (t.abs_b_minus_t < '30 days'::interval)
Buffers: shared hit=2
Planning:
Buffers: shared hit=1
Planning Time: 0.042 ms
Execution Time: 0.052 ms
So, we are using t_ix
with the BRIN index on our generated field.
Original Answer:
CREATE TABLE t
(
ticktime TIMESTAMPTZ,
broktime TIMESTAMPTZ,
trade_day DATE
--
-- other fields
--
);
Now, we create our functional index as follows:
CREATE INDEX t_ix ON t
USING BRIN (trade_day, (GREATEST(broktime, ticktime) - LEAST(broktime, ticktime)));
Populate the table:
INSERT INTO t VALUES
('2022-02-14 14:43:55'::TIMESTAMPTZ, '2022-02-14 12:43:55'::TIMESTAMPTZ, '2022-02-14'::DATE),
('2022-03-14 14:43:55'::TIMESTAMPTZ, '2022-02-14 12:43:55'::TIMESTAMPTZ, '2022-03-14'::DATE),
('2022-02-14 14:43:55'::TIMESTAMPTZ, '2022-05-14 12:43:55'::TIMESTAMPTZ, '2022-02-14'::DATE);
Now we test:
SELECT
ticktime - broktime AS t_minus_b,
GREATEST(broktime, ticktime) - LEAST(broktime, ticktime) AS abs_b_minus_t
FROM t;
Result:
t_minus_b abs_b_minus_t
02:00:00 02:00:00
28 days 02:00:00 28 days 02:00:00
-88 days -21:00:00 88 days 21:00:00
So, we have the values and their absolutes.
SELECT
broktime - ticktime
FROM t
WHERE GREATEST(broktime, ticktime) - LEAST(broktime, ticktime) < INTERVAL '30 DAYS';
Result:
?column?
-02:00:00
-28 days -02:00:00
To check index usage, we disable seqscans:
Then, we run:
EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT
broktime - ticktime
FROM t
WHERE GREATEST(broktime, ticktime) - LEAST(broktime, ticktime) < INTERVAL '30 DAYS';
Result:
QUERY PLAN
Bitmap Heap Scan on public.t (cost=12.17..57.59 rows=567 width=16) (actual time=0.041..0.044 rows=2 loops=1)
Output: (broktime - ticktime)
Recheck Cond: ((GREATEST(t.broktime, t.ticktime) - LEAST(t.broktime, t.ticktime)) < '30 days'::interval)
Rows Removed by Index Recheck: 1
Heap Blocks: lossy=1
Buffers: shared hit=3
-> Bitmap Index Scan on t_ix (cost=0.00..12.03 rows=1700 width=0) (actual time=0.027..0.027 rows=10 loops=1)
Index Cond: ((GREATEST(t.broktime, t.ticktime) - LEAST(t.broktime, t.ticktime)) < '30 days'::interval)
Buffers: shared hit=2
Planning:
Buffers: shared hit=1
Planning Time: 0.044 ms
Execution Time: 0.096 ms
So, we see that t_ix
is used with the relatively efficient Bitmap