Postgres 9.x中有多少成本检查约束？ [英] How much cost check constraints in Postgres 9.x?
问题描述
我想知道是否有一些基准可以比较在60列的表上插入一些检查约束的成本，而在20列上我想在6个行上插入NotEmpty和NotNull约束。
我的情况是我的桌子上有空值和空值（在我的情况下，始终表示无数据）。
我想仅用一个统一该数据值。
这就是为什么我想在列上插入NotEmpty约束的原因，因为当我读取null值时，它不像空值（并且尊重）那样重（字节大小）他的真实意思）。
但是从另一方面来看，NotNull约束比检查约束更深入，也许它的性能更好...
当我进行这种更改时，我被问到了一个问题：
-INSERT和UPDATE操作中Postgres 9.x中有多少成本检查约束？
因为如果花费很多，最好同时使用这两个值并在使用合并函数进行检索时出现这种情况...
您的经历如何？还是基准？
有人试图避免 NULL 值，声称
我不是其中之一。 NULL 值只适用于没有数据的列。对于磁盘空间和性能（主要影响是较小的表和索引），它们无疑是存储空列的最便宜方法：
在PostgreSQL中不使用NULL的情况下，在PostgreSQL中仍使用NULL位图标题？
是否设置 NOT NULL？在postgresql中的列上以提高性能？
可空列会在PostgreSQL中占用额外的空间吗？
一旦您了解 的性质， NULL 值，没有理由避免使用它们。 Postgres提供了多种函数来处理NULL。 colaesce（ ） ， nullif（） ， concat（）， concat_ws（） ，...
通常来说，就性能而言， NOT NULL约束要胜过 CHECK约束并且都以对数命中率击败了触发器。但是，即使是简单的触发器也很便宜。 NOT NULL 约束的成本几乎为零。而且，所有这些都只影响写操作，但在大多数应用程序中，读操作占主导。
因此，对性能最重要的影响（不包括次优索引和查询）是表和索引的大小，或更重要的是，每个数据页面的元组数。在大多数情况下，较大的元组会导致性能降低。满足查询所必须读取的数据页数相应增加。可用的缓存内存早已饱和。
我尚未准备好基准测试，但是无论如何，最好还是针对您的特定环境进行测试。这些只是简单的经验法则。现实要复杂得多。
I'd like to know if there are some benchmark to compare how much cost insert some check constraints on a table of 60 columns where on 20 i'd like to insert a constraints of NotEmpty and on 6 rows NotNull.
My case is that i have on my table Empty values and Null values (that in my case means always "no data").
I'd like to unify that data values with just one.
That's why I'm thinking to insert NotEmpty constraints on columns, because as i have read null value are not heavy (in byte size) as like empty values (and respect his real meaning).
But from other side NotNull Constraint are more deep level than check constraint, and maybe it performs better...
When i'm doing that changing, I was raised this question: - How much cost check constraints in Postgres 9.x in INSERT and UPDATE operation?
Because if costs to much maybe it's better to have this situation with both values and when i'll retrieve it using a coalesce function...
What's about yours experiences or benchmarks?
Some people try to avoid NULL values, claiming the logic would be confusing.
I am not one of them. NULL values are just fine for columns with no data. They are certainly the cheapest way to store "empty" columns - for disk space as well as performance (the main effect being smaller tables and indices):
Does not using NULL in PostgreSQL still use a NULL bitmap in the header?
Does setting "NOT NULL" on a column in postgresql increase performance?
Do nullable columns occupy additional space in PostgreSQL?
Once you understand the nature of NULL values, there is no reason to avoid them. Postgres offers a variety of functions to deal with NULLs. colaesce(), nullif(), concat(), concat_ws(), ...
Generally, as far as performance is concerned, a NOT NULL constraint beats a CHECK constraint and both beat triggers by a log shot. But even simple triggers are cheap. The cost of a NOT NULL constraint is next to nothing. Also, all of these only affect write operations, but in most applications read operations dominate.
The most relevant impact on performance (sub-optimal indices and queries aside) therefore is the size of tables and indices or, more importantly, the number of tuples per data page. Bigger tuples lead to slower performance for most use cases. The number of data pages that have to be read to satisfy a query increases accordingly. Available cache memory is saturated earlier.
I don't have a benchmark ready, but it's best to test for your particular environment anyway. These are just simple rules of thumb. Reality is a lot more complex.
这篇关于Postgres 9.x中有多少成本检查约束？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
