我有一个相当大的表，需要获取"column_a <> column_b位置"的行，我需要什么样的索引？

Question

我有一个相当大的表(〜100m行(，并且该表有两个boolean列。我们称它们为a和b。我想在 a is not equal to b中获取所有行：

SELECT *
FROM   table
WHERE  a <> b

我需要两个索引，一个在a上，一个在b上为此，或者在(a, b)上的复合索引也可以在这里工作吗？

我正在使用PostgreSQL 9.6，并将很快升级到10.1。

Answer 1

几乎没有索引可以帮助此查询。如果您碰巧知道A通常等于B，那么您可以在表达式上具有索引。但是，通常不建议使用布尔值索引。而且，这些值必须在大多数情况下是平等的 - 考虑90％的时间或99％的时间。

Answer 2

如果使用a<>b的记录相对较少，则可以使用条件索引：

CREATE INDEX ON thetable (id) WHERE a<>b;

实际的索引场id并不是那么重要，并且可能会阴影现有的无条件(PK(索引。如果a和b是无效的(对布尔人来说毫无意义(，则可以将a is distinct from b用作条件。

---

更新：

---

-- i tmp.sql
CREATE TABLE thetable
        ( id serial NOT NULL PRIMARY KEY
        , data text
        , a boolean NOT NULL
        , b boolean NOT NULL
        );
INSERT INTO thetable(a,b, data)
SELECT True, True, 'data_' || gs::integer
FROM generate_series(1,1000000) gs 
        ;
UPDATE thetable SET a = False WHERE id % 37 = 0 ;
UPDATE thetable SET b = False WHERE id % 47 = 0 ;
SELECT version();
DROP INDEX zzzzzz ;
CREATE INDEX zzzzzz ON thetable((a<>b)) WHERE a<>b;
VACUUM ANALYZE thetable;
EXPLAIN ANALYZE
-- SELECT COUNT(*)
SELECT id
FROM thetable
WHERE a <> b
        ;

---

结果：

---

psql:tmp.sql:2: NOTICE:  drop cascades to table tmp.thetable
DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
INSERT 0 1000000
UPDATE 27027
UPDATE 21276
SET
                                           version                                            
----------------------------------------------------------------------------------------------
 PostgreSQL 9.3.5 on i686-pc-linux-gnu, compiled by gcc (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 32-bit
(1 row)
ERROR:  index "zzzzzz" does not exist
CREATE INDEX
VACUUM
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Index Scan using zzzzzz on thetable  (cost=0.29..9741.19 rows=995000 width=4) (actual time=0.057..193.087 rows=47153 loops=1)
   Index Cond: ((a <> b) = true)
 Total runtime: 259.891 ms
(3 rows)

---

因此，看来您必须在索引表达中与WHERE中的条件完全相同。(并且它也可能与查询条件匹配(