From: Tom Lane <tgl@sss.pgh.pa.us>
To: Wayne <lists-pgsql@useunix.net>
cc: pgsql-sql@lists.postgresql.org
Subject: Re: min()/max() with BRIN indexes
In-reply-to: <20200229165024.GD20190@ln-1.useunix.net>
References: <20200229165024.GD20190@ln-1.useunix.net>
Comments: In-reply-to Wayne <lists-pgsql@useunix.net>
	message dated "Sat, 29 Feb 2020 16:50:24 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <16369.1583005035.1@sss.pgh.pa.us>
Date: Sat, 29 Feb 2020 14:37:15 -0500
Message-ID: <16370.1583005035@sss.pgh.pa.us>
Precedence: bulk

Wayne <lists-pgsql@useunix.net> writes:
> I have rather large tables that use a time stamp as an index. New entries
> are continuously added to the table with the current time. If I convert
> from BTREE to BRIN indexes and select records with specific date ranges
> the BRIN is used and performance is acceptable. However I often want to
> get the latest time stamp using the max() function. I didn't expect that
> this would result in a sequential scan of the table and skip the BRIN
> index.

> Is this expected behavior?

Yeah.  In principle a BRIN index could be used to accelerate finding min
or max, but there's no actual support for that at the moment ... and in
any case, it'd still be substantially slower than the equivalent with
a btree index, which can locate the extremal values immediately.

For this particular case, you might be able to fake it with something like

	select max(ts) from mytab where ts > 'some cutoff'

if you can estimate some not-too-far-before-current-time cutoff
that you are sure you'll find some records after.

			regards, tom lane