Postgres Indexing: When Does BRIN Win?(crunchydata.com)

https://www.crunchydata.com/blog/postgres-indexing-when-does-brin-win

6 コメント

Oh nice, I have just the tables for that! Billions of well-compacted rows with a big ol' timestamp on them. Btree indices are almost as big as the table itself...

Strum3554 年前

Note that if you delete entries in non sequential order (aka not at the start-only or the end-only) and continue inserting, BRIN index becomes much less effective. Its very much predicated upon the concept of append-only, as postgres filling deleted rows with new rows throws off the physical layout part of how BRIN works

aspyct4 年前

I don't delete anything in those tables, however I do one update on 9/10 of the rows, at least.

I seem to remember there was a way to reorder rows in a table, maybe that could be useful.

pramsey4 年前

You can check if your data remain correlated after your updates. It's possible they do: SELECT correlation FROM pg_stats WHERE tablename = 'mytable' AND attname = 'mytimestamp';

aspyct4 年前

Oh nice! I had no idea, I'll try it, thanks :)

geordee4 年前

This is similar to Zone Maps in Netezza. https://www.ibm.com/docs/en/psfa/7.2.1?topic=statistics-zone...

Incidentally, Netezza was built on Postgres engine, years back.