Postgres Indexing: When Does BRIN Win?

Post by krnaveen14 » Wed, Jul 13, 2022, 11:19 PM UTC

Post by geordee » Thu, Jul 14, 2022, 7:58 AM UTC

Post by aspyct » Thu, Jul 14, 2022, 11:35 AM UTC

Strum355 wrote: post #4

aspyct wrote: post #2

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...

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

Post by aspyct » Fri, Jul 15, 2022, 8:04 AM UTC

pramsey wrote: post #6

aspyct wrote: post #5

Earlier quoted context omitted.

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.

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';