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

问题描述

我有一个具有一个布尔列的表.

I have a table that has one boolean column.

productid integer
isactive boolean

当我执行查询时

SELECT productid   
    FROM 
    product  
    WHERE ispublish
    LIMIT 15 OFFSET  0

之后，我为ispublish列创建了索引:

After that, I created an index for the ispublish column:

CREATE INDEX idx_product_ispublish ON product USING btree (ispublish)

然后重新执行

SELECT productid   
       FROM 
       product  
       WHERE ispublish
       LIMIT 15 OFFSET  0

结果:

=>没有区别

我已经尝试过以下方法，但是结果是相同的:

I've been tried the following, 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(如果perpertage较高)要便宜必须检索表中的

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. 在数据仓库中，可以通过位图索引扫描将其与其他索引组合.

1. in data warehouse scenarios, where it can be combined with other indexes via a bitmap index scan.

，如果表中只有一小部分的值是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;

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