Postgres SQL：为布尔列创建索引 [英] Postgres SQL : Create index for boolean column

问题描述

我的表有1个布尔列。

I have table that has 1 boolean columns.

productid integer
isactive (boolean)

执行查询时

SELECT productid   
    FROM 
    product  
    WHERE ispublish
    LIMIT 15 OFFSET  0

之后，我为 ispublish 列

CREATE INDEX idx_product_ispublish ON product USING btree (ispublish)

re execute
SELECT productid   
       FROM 
       product  
       WHERE ispublish
       LIMIT 15 OFFSET  0
result 

=>没有区别
我已经尝试过
但是结果是一样的

=> No difference I've been tried But the results are the same

CREATE INDEX idx_product_ispublish ON product USING btree (ispublish)

CREATE INDEX idx_product_ispublish ON product USING btree (ispublish)

CREATE INDEX idx_product_ispublish ON product (ispublish) WHERE ispublish is TRUE

谁可以向我解释？

推荐答案

PostgreSQL仅在认为这样会更便宜时才使用索引。
boolean 列上的索引只能使用两个可能的值，几乎不会使用它，因为顺序读取整个表比索引更便宜如果必须检索表的较高性能，请在索引和表上使用随机I / O。

PostgreSQL will use an index only if it thinks it will be cheaper that way. An index on a boolean column, which can only take two possible values, will almost never be used, because it is cheaper to sequentially read the whole table than to use random I/O on the index and the table if a high percantage of the table has to be retrieved.

boolean 列仅有用

1. 在数据仓库方案中，可以通过位图索引扫描。

如果表的一小部分具有值 TRUE （或 FALSE ）。在这种情况下，最好创建一个部分索引，例如

if only a small fraction of the table has the value TRUE (or FALSE for that matter). In this case it is best to create a partial index like

CREATE INDEX ON mytab((1)) WHERE boolcolumn;

这篇关于Postgres SQL：为布尔列创建索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！