设置“NOT NULL”在postgresql的列上增加性能？ [英] Does setting "NOT NULL" on a column in postgresql increase performance?
问题描述
永远是一个很好的理想，如果你可以避免它，因为使用的语义是如此凌乱;请参阅什么是NULL的处理？很好的讨论如何让你陷入麻烦。
在PostgreSQL的版本中高达8.2，软件不知道如何在最常见的类型索引（b-tree）上进行比较，包括在其中查找NULL值。在索引类型文档的相关位置，您可以看到描述为但请注意IS NULL不等于=并且不可索引。这样做的有效缺点是，如果您指定需要包含NULL值的查询，那么计划程序可能无法使用该情况的明显索引来满足它。作为一个简单的例子，如果你有一个可以使用索引加速的ORDER BY语句，但是您的查询也需要返回NULL值，那么优化器不能使用该索引，因为结果将丢失任何NULL数据，而因此是不完整和无用的。优化器知道这一点，而是对表进行无索引的扫描，这可能非常昂贵。
PostgreSQL 在8.3中改进了这一点，索引列上的IS NULL条件可以与B树索引一起使用。因此，您可以通过尝试使用NULL值对索引进行索引而被刻录的情况已被减少。但是，由于NULL语义仍然非常痛苦，您可能会遇到这样一种情况，即使8.3策划者也不会按照您的预期执行任何操作，您仍然应该尽可能使用NOT NULL，以降低运行到严重优化查询的机会。
I know this is a good idea in MySQL. If I recall correctly, in MySQL it allows indexes to work more efficiently.
It's always a good ideal to keep columns from being NULL if you can avoid it, because the semantics of using are so messy; see What is the deal with NULLs? for good a discussion of how those can get you into trouble.
In versions of PostgreSQL up to 8.2, the software didn't know how to do comparisons on the most common type index (the b-tree) in a way that would include finding NULL values in them. In the relevant bit of documentation on index types, you can see that described as "but note that IS NULL is not equivalent to = and is not indexable". The effective downside to this is that if you specify a query that requires including NULL values, the planner might not be able to satisfy it using the obvious index for that case. As a simple example, if you have an ORDER BY statement that could be accelerated with an index, but your query needs to return NULL values too, the optimizer can't use that index because the result will be missing any NULL data--and therefore be incomplete and useless. The optimizer knows this, and instead will do an unindexed scan of the table instead, which can be very expensive.
PostgreSQL improved this in 8.3, "an IS NULL condition on an index column can be used with a B-tree index". So the situations where you can be burned by trying to index something with NULL values have been reduced. But since NULL semantics are still really painful and you might run into a situation where even the 8.3 planner doesn't do what you expect because of them, you should still use NOT NULL whenever possible to lower your chances of running into a badly optimized query.
这篇关于设置“NOT NULL”在postgresql的列上增加性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
