public inbox for [email protected]
help / color / mirror / Atom feedReindexing
4+ messages / 3 participants
[nested] [flat]
* Reindexing
@ 2025-04-25 05:16 Wasim Devale <[email protected]>
0 siblings, 2 replies; 4+ messages in thread
From: Wasim Devale @ 2025-04-25 05:16 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>; pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Reindexing
@ 2025-04-25 05:57 Laurenz Albe <[email protected]>
parent: Wasim Devale <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2025-04-25 05:57 UTC (permalink / raw)
To: Wasim Devale <[email protected]>; Pgsql-admin <[email protected]>; pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* When indexing is needed for a temp table
@ 2025-04-25 16:43 Bo Guo <[email protected]>
parent: Wasim Devale <[email protected]>
1 sibling, 1 reply; 4+ messages in thread
From: Bo Guo @ 2025-04-25 16:43 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>; pgsql-admin
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 *
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: When indexing is needed for a temp table
@ 2025-04-25 22:41 Laurenz Albe <[email protected]>
parent: Bo Guo <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2025-04-25 22:41 UTC (permalink / raw)
To: Bo Guo <[email protected]>; Pgsql-admin <[email protected]>; pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-04-25 22:41 UTC | newest]
Thread overview: 4+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-04-25 05:16 Reindexing Wasim Devale <[email protected]>
2025-04-25 05:57 ` Laurenz Albe <[email protected]>
2025-04-25 16:43 ` When indexing is needed for a temp table Bo Guo <[email protected]>
2025-04-25 22:41 ` Re: When indexing is needed for a temp table Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox