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?
min()andmax()really the one you need? If not, please showEXPLAIN (ANALYZE, BUFFERS)for a more realistic query. Note that a BRIN index will likely become useless if you ever delete or update rows in the table.