agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

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
Date: Sat, 29 Feb 2020 14:37:15 -0500
Message-ID: <16370.1583005035@sss.pgh.pa.us> (raw)
In-Reply-To: <20200229165024.GD20190@ln-1.useunix.net>
References: <20200229165024.GD20190@ln-1.useunix.net>

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

view thread (4+ messages)  latest in thread

Message-ID: <16370.1583005035@sss.pgh.pa.us>
Permalink:  ../16370.1583005035@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/16370.1583005035@sss.pgh.pa.us

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, lists-pgsql@useunix.net, pgsql-sql@lists.postgresql.org
  Subject: Re: min()/max() with BRIN indexes
  In-Reply-To: <16370.1583005035@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox