Index bloat reduced in PostgreSQL v14 • Hacker News Board

Re: Index bloat reduced in PostgreSQL v14

#91

Post by pgaddict » Sun, Sep 12, 2021, 12:21 AM UTC

pas wrote: post #43

jandrewrogers wrote: post #37

Earlier quoted context omitted.

PostgreSQL is robust across a wide range of applications but it does have some architectural sharp edges that can cause serious operational problems in practice if you run into them. Most of these only show up at scale. Only a few do not have any viable workaround in practice. The worst one, in my experience, is that the statistics collector is architecturally broken for some large tables, which can cause the query p…

Even if a full rework or overriding would be hard to implement, saving/loading statistics, or disabling the collector (after it already managed to do a decent run) couldn't be done? Or the nature/representation of the stats don't allow for this? (So they need constant updating, even if badly? But if stats only influence query plans then after good plans are found for the most common queries they could be persisted, r…

I think import/export of stats is pretty doable. Not a tiny amount of work, because of how many stats there may be (regular, extended), but I don't see any obvious major challenges ...

Similarly for disabling autoanalyze. We kinda already allow that for autovacuum (ALTER TABLE ... SET ...), so adding autoanalyze_enabled seems possible too. Or you could just set the threshold/scale_factor very high ...

Re: Index bloat reduced in PostgreSQL v14

#92

Post by gmokki » Sun, Sep 12, 2021, 12:21 PM UTC

Dylan16807 wrote: post #77

jashmatthews wrote: post #61

Earlier quoted context omitted.

Because the contents of the table is changing the statistics are becoming out of date.

That doesn't answer the question at all. The old statistics said to use the index. If it's still using old statistics, why does the behavior change?

Because the user is using values that are no longer covered by the statistics. For example incrementing timestamp or id column. If the stats are from yesterday and they say nothing about the frequency of todays timestamps the query will have to take a pessimistic view of the world. It might be that the data has radically changed since the last stats run, or not. Need to analyze the table to know and make optimal choices.

Re: Index bloat reduced in PostgreSQL v14

#93

Post by williamdclt » Tue, Sep 14, 2021, 10:34 AM UTC

pgaddict wrote: post #88

williamdclt wrote: post #60

Earlier quoted context omitted.

That's really my number 1 gripe with PG. I'm not even too bothered by the opaqueness of the query planner (although I'd love better visibility into it). But the fact that the query plan can change any second is insane: you can't lock it, and you can't force another one as a short-term fix. There's no option that I know of. If you reach an impossible-to-anticipate threshold and the query plan changes, your whole syste…

Well, what exactly would you expect for better visibility into the planner? I mean, you have the source code, and I'm not sure how to visualize the extreme number of combinations considered by the planner. Any examples of databases doing interesting things? As for the "locking" of plans, I personally have rather serious doubts about that. Yes, I've heard it suggested as a viable solution, but knowing how vastly diffe…

> what exactly would you expect for better visibility into the planner

Dunno. At the moment I need a fairly deep understanding of how the planner works (eg how it uses statistics or indexes) to optimise queries, I'd love to be able to _see_ that rather than guess. Not saying it's easy, I'm just wishing

> As for the "locking" of plans, I personally have rather serious doubts about that. Yes, I've heard it suggested as a viable solution, but knowing how vastly different plans may be "right" for the same query with just slightly different parameters ...

What's the problem with vastly different plans being "right" for the same query? All I am (and many other people are) asking for is a way to ensure PG doesn't bring down my entire system because it decided to change the query plan it uses without 1. any sort of warning 2. any way to revert it. It doesn't feel like it's asking for too much! Maybe locking plans is a good solution, maybe it's not, I'd just like _something_ that lets me sleep at night