Horton

Re: WIP: BRIN multi-range indexes

Tomas Vondra <tomas.vondra@2ndquadrant.com>

View thread View on postgr.es
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: John Naylor <john.naylor@2ndquadrant.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2020-09-13T16:40:43Z
Lists: pgsql-hackers

Attachments

Hi,

while running some benchmarks to see if the first two patches cause any
regressions, I found a bug in 0002 which reworks the NULL handling. The
code failed to eliminate ranges early using the IS NULL scan keys,
resulting in expensive recheck. The attached version fixes that.

I also noticed that some of the queries seem to be slightly slower, most
likely due to bringetbitmap having to split the scan keys per attribute,
which also requires some allocations etc. The regression is fairly small
might be just noise (less than 2-3% in most cases), but it seems just
allocating everything in a single chunk eliminates most of it - this is
what the new 0003 patch does.

OTOH the rework also helps in other cases - I've measured ~2-3% speedups
for cases where moving the IS NULL handling to bringetbitmap eliminates
calls to the consistent function (e.g. IS NULL queries on columns with
no NULL values).

These results seems very dependent on the hardware (especially CPU),
though, and the differences are pretty small in general (1-2%).

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Commits

  1. BRIN minmax-multi indexes

  2. BRIN bloom indexes

  3. Support the old signature of BRIN consistent function

  4. Remove unnecessary pg_amproc BRIN minmax entries

  5. Optimize allocations in bringetbitmap

  6. Move IS [NOT] NULL handling from BRIN support functions

  7. Pass all scan keys to BRIN consistent function at once

  8. Properly detoast data in brin_form_tuple