Sample Header Ad - 728x90

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