Bloom Filter Indexes in PostgreSQL (opens in new tab)

(postgresql.org)

32 pointskaashmonee1y ago5 comments

5 comments

Slightly off-topic but what tools do folks use for optimizing/analyzing/debugging indexes on Postgres.

You can't go wrong with https://pgmustard.com. It's helped me so many times. All you need is the output of EXPLAIN (ANALYZE, VERBOSE, BUFFERS, FORMAT JSON).

hijinks1y ago

pghero and/or https://github.com/supabase/index_advisor

If you want to pay for a saas then pganalyze is like $400 a month for 4 dbs is a pretty good pricing model

ezekg1y ago

> A sequential scan over this large table takes a long time:

> ...

> Execution Time: 16.988 ms

I feel like I have a different definition of a "long time", and apparently all my queries suck lol. Also, of course the btree index on (i1, i2, i3, i4, i5, i6) won't be used when querying on only i1 and i5. Felt a bit weird to read that as an "unexpected" outcome in the pg docs, but I guess the writer is just trying to make a contrast with the bloom index on the same column set.

esafak1y ago

j / k navigate · click thread line to collapse

5 comments

whinvik1y ago

Slightly off-topic but what tools do folks use for optimizing/analyzing/debugging indexes on Postgres.

ezekg1y ago

You can't go wrong with https://pgmustard.com. It's helped me so many times. All you need is the output of EXPLAIN (ANALYZE, VERBOSE, BUFFERS, FORMAT JSON).

hijinks1y ago

pghero and/or https://github.com/supabase/index_advisor

If you want to pay for a saas then pganalyze is like $400 a month for 4 dbs is a pretty good pricing model

ezekg1y ago

> A sequential scan over this large table takes a long time:

> ...

> Execution Time: 16.988 ms

esafak1y ago

j / k navigate · click thread line to collapse