How to correctly use a BRIN index in PostgreSQL?
0
votes
2
answers
103
views
My table is like this:
CREATE TABLE IF NOT EXISTS public.ticks_a_2507
(
tick_time timestamp(6) with time zone NOT NULL,
tick_nano smallint NOT NULL,
trade_day date NOT NULL,
-- other columns...
CONSTRAINT prk_ticks_a_2507 PRIMARY KEY (tick_time, tick_nano)
);
CREATE INDEX IF NOT EXISTS idx_ticks_a_2507_td
ON public.ticks_a_2507 USING brin(trade_day);
----------------------------------
Rows those were inserted in a same day, always carry same value for column "trade_day".
Henc I guess that a BRIN can be used as the type of index "idx_ticks_a_2507_td", so as to save my disk space.
But when I issue a query on this column, it looks like that the index is **NOT** used by PG13.
explain select min(trade_day), max(trade_day) from ticks_a_2507;
> Finalize Aggregate (cost=18870.72..18870.73 rows=1 width=8)
>
> -> Gather (cost=18870.39..18870.70 rows=3 width=8)
>
> Workers Planned: 3
>
> -> Partial Aggregate (cost=17870.39..17870.40 rows=1 width=8)
>
> -> Parallel Seq Scan on ticks_a_2507 (cost=0.00..16672.93 rows=239493 width=4)
And the queries are much slower than ones with a BTree index on the same column.
I have hundreds of tables with this structure, and everyday every table will be inserted thousands rows with same "trade_day" value(the date of insertion op).
If I use **
BTree
**, a lot amount of disk space would be wasted, even exceeding the data space.
Am I using **Brin
** in a wrong way? Or this is NOT the stage to use it?
Asked by Leon
(411 rep)
Jan 17, 2025, 09:53 AM
Last activity: Jan 19, 2025, 08:02 AM
Last activity: Jan 19, 2025, 08:02 AM