Postgres Pro
Downloads
Home   >   mailing lists

Thread: Reindexing

Reindexing

From
Wasim Devale
Date:
Hi everyone 

I want to automate the reindexing and planning to do it concurrently twice a week.

Can someone highlight at what percentage of index bloat can decide the frequency of reindexing?

Thanks,
Wasim

Re: Reindexing

From
Laurenz Albe
Date:
On Fri, 2025-04-25 at 10:46 +0530, Wasim Devale wrote:
> I want to automate the reindexing and planning to do it concurrently twice a week.

That's probably unnecessary.

> Can someone highlight at what percentage of index bloat can decide the frequency of reindexing?

It is perfectly normal for a B-tree index to have an "avg_leaf_density" of 30 or so,
as determined with pgstatindex().  Consider reindexing only for indexes that are
doing worse *and that keep deteriorating*.

Yours,
Laurenz Albe

When indexing is needed for a temp table

From
Bo Guo
Date:
Hello, everyone!

When creating a temporary table for additional sql operations in a stored function, should I worry about building indexes on the temp table to increase performance when the temp table's row count exceeds a certain threshold?  If so, is there a rule of thumb for the row count threshold?  

Thanks

Bo 

Re: When indexing is needed for a temp table

From
Laurenz Albe
Date:
On Fri, 2025-04-25 at 09:43 -0700, Bo Guo wrote:
> When creating a temporary table for additional sql operations in a stored function,
> should I worry about building indexes on the temp table to increase performance when
> the temp table's row count exceeds a certain threshold?  If so, is there a rule of
> thumb for the row count threshold?  

Well, if a table is small, you don't need an index.  It is hard to say what is small.
For one, it depends on the size of the rows.

In general, I would say that it never pays to build an index on a temporary table
unless you are using that index at least twice.

Yours,
Laurenz Albe