在PostgreSQL中索引空值 [英] Indexing Null Values in PostgreSQL

问题描述

我有一个查询格式：

select m.id from mytable m
left outer join othertable o on o.m_id = m.id
    and o.col1 is not null and o.col2 is not null and o.col3 is not null
where o.id is null

查询返回几百条记录，尽管表有数百万行，并且要花很多时间才能运行（大约一个小时）

The query returns a few hundred records, although the tables have millions of rows, and it takes forever to run (around an hour).

当我使用以下方法检查索引统计信息时：

When I check my index statistics using:

select * from pg_stat_all_indexes
where schemaname <> 'pg_catalog' and (indexrelname like 'othertable_%' or indexrelname like 'mytable_%')

只使用了othertable.m_id的索引，而根本没有使用col1..3的索引。为什么会这样？

I see that only the index for othertable.m_id is being used, and that the indexes for col1..3 are not being used at all. Why is this?

我读过很少 将放在PG传统上无法索引NULL值的位置。但是，我已经读到，自PG 8.3起，这种情况应该发生了变化？我目前在Ubuntu 10.04上使用PostgreSQL 8.4。我是否需要专门创建部分或功能索引以加快IS NOT NULL查询的速度，还是已经在索引NULL而只是误解了这个问题？

I've read in a few places that PG has traditionally not been able to index NULL values. However, I've read this has supposedly changed since PG 8.3? I'm currently using PostgreSQL 8.4 on Ubuntu 10.04. Do I need to make a "partial" or "functional" index specifically to speed up IS NOT NULL queries, or is it already indexing NULLs and I'm just misunderstanding the problem?

推荐答案

您可以尝试使用部分索引：

You could try a partial index:

CREATE INDEX idx_partial ON othertable (m_id)
WHERE (col1 is not null and col2 is not null and col3 is not null);

从文档中： http://www.postgresql.org/docs/current/interactive/indexes-partial.html

这篇关于在PostgreSQL中索引空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！