Unconventional PostgreSQL Optimizations
hakibenita.com
Unconventional PostgreSQL Optimizations
1–10 of 72 posts
Re: Unconventional PostgreSQL Optimizations
#2Remarkably fresh content.
It's interesting how both virtual columns and hash indexes work, but feel like they're bolted on, vs being made part of the whole ecosystem so that they work seamlessly.
Re: Unconventional PostgreSQL Optimizations
#3I think a stored generated column allows you to create an index on it directly. Isn't it better approach?
Re: Unconventional PostgreSQL Optimizations
#4I think a stored generated column allows you to create an index on it directly. Isn't it better approach?
I assume it would increase the storage usage, which they say they are trying to avoid in that example.
Re: Unconventional PostgreSQL Optimizations
#5I think a stored generated column allows you to create an index on it directly. Isn't it better approach?
The article explains why they want to avoid this option:
> Starting at version 14, PostgreSQL supports generated columns - these are columns that are automatically populated with an expression when we insert the row. Sounds exactly like what we need but there is a caveat - the result of the expression is materialized - this means additional storage, which is what we were trying to save in the first place!
Re: Unconventional PostgreSQL Optimizations
#6I think a stored generated column allows you to create an index on it directly. Isn't it better approach?
The article explains why they want to avoid this option: > Starting at version 14, PostgreSQL supports generated columns - these are columns that are automatically populated with an expression when we insert the row. Sounds exactly like what we need but there is a caveat - the result of the expression is materialized - this means additional storage, which is what we were trying to save in the first place!
Thanks, missed that part. I would still be interested in knowing how much additional storage that adds, if the OP is interested in updating the article.
Re: Unconventional PostgreSQL Optimizations
#7Great article, shows a lot of interesting PostgreSQL features. I have used PostgreSQL and MySQL for decades, and this article showed me that I have barely scratched the surface of what is possible.
Re: Unconventional PostgreSQL Optimizations
#8I think a stored generated column allows you to create an index on it directly. Isn't it better approach?
>I think a stored generated column allows you to create an index on it directly. Isn't it better approach?
Is it also possible to create index (maybe partial index) on expressions?
Re: Unconventional PostgreSQL Optimizations
#9>Currently, constraint exclusion is enabled by default only for cases that are often used to implement table partitioning via inheritance trees. Turning it on for all tables imposes extra planning overhead that is quite noticeable on simple queries, and most often will yield no benefit for simple queries.
PG's lack of plan caching strikes again, this sort of thing is not a concern in other DB's that reuse query plans.
Re: Unconventional PostgreSQL Optimizations
#10some points from this article that I didn't know before.