pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Julien Rouhaud <rjuju123@gmail.com>
Cc: Andrey Klochkov <diggerk@gmail.com>
Cc: Francisco Olarte <folarte@peoplecall.com>
Cc: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Do BRIN indexes support MIN/MAX?
Date: Thu, 30 Mar 2023 00:18:28 -0400
Message-ID: <4136181.1680149908@sss.pgh.pa.us> (raw)
In-Reply-To: <CAOBaU_Y8Hs78kihL+zNEKAj5CkypwNnZO1e5tArnyUSkUa5_Pg@mail.gmail.com>
References: <CAFcR=R5jYvttbc5H0GUd7evczLsQecMUVFw-wv_QP0=aOTJixA@mail.gmail.com>
	<CAB=Je-GsYgM0Qd9AmsJ3ZU=qrPcOc_3XGUfhcwprMEzJfSnQwA@mail.gmail.com>
	<CA+bJJbzc-GXq8huFVL_yqfdmsg57hFH3ZmBLSjfW2qA+vHYCRw@mail.gmail.com>
	<CAFcR=R4EF=9ZiGNt8+KOvrbSYAhb1zxjxHnU14edxKLG6k9Gfw@mail.gmail.com>
	<CAOBaU_Y8Hs78kihL+zNEKAj5CkypwNnZO1e5tArnyUSkUa5_Pg@mail.gmail.com>

Julien Rouhaud <rjuju123@gmail.com> writes:
> brin indexes don't work the way you would hope for.  the stored min/max
> values per range guarantees that all values in the underlying relation
> pages are contained in that range, but it doesn't mean that those min/max
> values are still present in the table, so you can't deduce in which range
> the current min or max value is from there.

Yeah.  You could for example (when looking for a MAX) skip scanning
block ranges whose indexed MAX is less than the indexed MIN of some
other block range.  But unless the column you are interested in is
pretty well correlated with physical storage order, that seems
unlikely to buy much.

[ ... thinks ... ]  Actually, I'm not sure that even that optimization
works without hackery.  It requires that you know that the "other block
range" is not empty according to your snapshot.  I guess you could work
by scanning block ranges in descending indexed-MIN order, and skipping
ranges only once you have a candidate MAX in hand.  But that sounds
complicated and very prone to nonsequential table access, so I'm not
sure how much of a win it is.  If you need fast min/max access, you
probably need to bite the bullet and maintain a btree index.

			regards, tom lane





view thread (11+ messages)  latest in thread

Message-ID: <4136181.1680149908@sss.pgh.pa.us>
Permalink:  ../4136181.1680149908@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/4136181.1680149908@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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: tgl@sss.pgh.pa.us, rjuju123@gmail.com, diggerk@gmail.com, folarte@peoplecall.com, sitnikov.vladimir@gmail.com
  Subject: Re: Do BRIN indexes support MIN/MAX?
  In-Reply-To: <4136181.1680149908@sss.pgh.pa.us>
  References: <CAFcR=R5jYvttbc5H0GUd7evczLsQecMUVFw-wv_QP0=aOTJixA@mail.gmail.com> <CAB=Je-GsYgM0Qd9AmsJ3ZU=qrPcOc_3XGUfhcwprMEzJfSnQwA@mail.gmail.com> <CA+bJJbzc-GXq8huFVL_yqfdmsg57hFH3ZmBLSjfW2qA+vHYCRw@mail.gmail.com> <CAFcR=R4EF=9ZiGNt8+KOvrbSYAhb1zxjxHnU14edxKLG6k9Gfw@mail.gmail.com> <CAOBaU_Y8Hs78kihL+zNEKAj5CkypwNnZO1e5tArnyUSkUa5_Pg@mail.gmail.com> <4136181.1680149908@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox