postgresql - 第1列的索引= x AND第2列<= y ORDER BY BY第3列DESC

我在谷歌上搜索了15个不同级别的索引（包括interval_end, ticker, interval_start DESC）和ASC/DESC，最终它使用的唯一索引是idx_onemin_intervalstart。我相信是那个接线员害死了我。
我已经阅读了所有关于索引的PostgreSQL手册，但仍然感到困惑这不是一个例子。
查询

explain analyze
SELECT open
FROM  onemin_interval
WHERE ticker = 'QQQ'
  AND interval_end <= 1326810600000
ORDER BY interval_start DESC
LIMIT 19960;

表架构
表<=

   Column            |     Type      | Modifiers
---------------------+---------------+-----------
 interval_start      | numeric(13,0) |
 interval_end        | numeric(13,0) |
 open                | numeric(10,2) |
 close               | numeric(10,2) |
 high                | numeric(10,2) |
 low                 | numeric(10,2) |
 volume_for_interval | bigint        |
 ticker              | character(10) |
 humantimeopen       | character(23) |
 humantimeclose      | character(23) |
 adlval              | bigint        |

索引

"idx_onemin_intervalend" btree (interval_end)
"idx_onemin_intervalend_intervalstart" btree (interval_end, interval_start)
"idx_onemin_intervalstart" btree (interval_start DESC)
"idx_onemin_ticker" btree (ticker)
"idx_onemin_ticker_intervalend" btree (ticker, interval_end)

查询计划

Limit  (cost=0.00..10295.29 rows=19960 width=20) (actual time=581.856..1731.352 rows=19960 loops=1)
->  Index Scan Backward using idx_onemin_intervalstart on onemin_interval  (cost=0.00..36843.32 rows=71430 width=20) (actual time=581.842..1621.713 rows=19960 loops=1)
     Filter: ((interval_end <= 1326810600000::numeric) AND (ticker = 'QQQ'::bpchar))
Total runtime: 1791.594 ms
(4 rows)

评论后的新内容
我添加了大量用于测试的索引，并运行public.onemin_interval。查询与以前几乎相同：

explain analyze
SELECT open
FROM  onemin_interval
WHERE ticker = 'QQQ'
  AND interval_end <= 1327698068642
ORDER BY interval_start DESC
LIMIT 19960;

查询计划

Limit  (cost=0.00..5849.68 rows=19960 width=16) (actual time=0.088..394.596 rows=19960 loops=1)
  ->  Index Scan using test11 on onemin_interval  (cost=0.00..21748.74 rows=74210 width=16) (actual time=0.079..298.848 rows=19960 loops=1)
     Filter: ((interval_end <= 1327698068642::bigint) AND (ticker = 'QQQ'::text))
Total runtime: 1442.898 ms
(4 rows)

架构
表ANALYZE onemin_interval

   Column           |     Type      | Modifiers
--------------------+---------------+-----------
interval_start      | bigint        |
interval_end        | bigint        |
open                | numeric       |
close               | numeric       |
high                | numeric       |
low                 | numeric       |
volume_for_interval | bigint        |
ticker              | text          |
humantimeopen       | character(23) |
humantimeclose      | character(23) |
adlval              | bigint        |

索引

"idx_onemin_intervalend" btree (interval_end)
"idx_onemin_intervalend_intervalstart" btree (interval_end, interval_start)
"idx_onemin_intervalstart" btree (interval_start)
"idx_onemin_ticker" btree (ticker)
"idx_onemin_ticker_intervalend" btree (ticker, interval_end)
"test1" btree (interval_end DESC)
"test10" btree (ticker, interval_end DESC, interval_start DESC)
"test11" btree (interval_start DESC)
"test12" btree (interval_start DESC, interval_end DESC, ticker)
"test13" btree (interval_start DESC, ticker, interval_end DESC)
"test14" btree (ticker, interval_start DESC, interval_end DESC)
"test15" btree (interval_end DESC, interval_start DESC, ticker)
"test3" btree (interval_end)
"test4" btree (interval_end DESC, ticker)
"test5" btree (interval_end, ticker)
"test6" btree (ticker, interval_end DESC)
"test7" btree (ticker, interval_end)
"test8" btree (interval_end, ticker)
"test9" btree (interval_end DESC, ticker, interval_start DESC)

最佳答案

数据类型、大小和性能
首先，类型character(n)几乎总是一个错误的选择。如果你没有很好的理由并且知道你在做什么，那么改用text类型。如果实际上需要强制执行最大长度，则使用约束。列的示例：

ALTER TABLE onemin_interval
ADD CONSTRAINT onemin_interval_ticker_len CHECK (length(ticker) < 11);

很明显你有“QQQ”这样的值，所以10的长度似乎是任意的。这将减少表的大小（可能会显著地）并加快。。一切。
另外，考虑使用ticker而不是bigint。反正你不会存储小数，这可能是更好的设计。numeric(13,0)需要13个字节（5+4x2），bigint需要8个字节，在各个方面的操作都更快。
最后：numeric(13,0)列是否也可以是普通的bigint？（如果-2147483648到+2147483647的范围在表的生命周期内足够大。）
核心问题
为什么不使用indexinteger？
我会尝试几种方法：
显式转换
如果无法将数据类型更改为idx_onemin_ticker_intervalend，请使用显式转换来测试查询：

WHERE ticker = 'QQQ'::character(10)

旧版本的PostgreSQL可能无法对三个字符的未添加字符串（text）使用character(10)的索引。不过，在我的测试中，PostgreSQL 8.4和9.1都使用了该索引。
多列索引中的列顺序
尝试反转多列索引中列的顺序。

idx_onemin_ticker_intervalend btree (interval_end DESC, ticker)

Order of the columns in a multi-column index is relevant。不过，在您的例子中，如果一切都是有序的，那么应该是不相关的，因为您在两个列上都包含了条件。我仍然会测试这个。
规划师统计
查询规划器似乎期望条件character(10)不是很有选择性，而事实上，它们看起来很有选择性（结果4行）。尝试提高两列的default_statistics_target，如下所示：

ALTER TABLE onemin_ticker ALTER COLUMN interval_end SET STATISTICS 1000;
ALTER TABLE onemin_ticker ALTER COLUMN ticker SET STATISTICS 1000;
ANALYZE onemin_ticker;

甚至更多，最多10000行（您确实有420k行）。默认值为100。这通常只对不均匀的数据编辑有帮助，但是对于大型表，它应该很容易得到回报。
一般性能优化
对performance optimization in the PostgreSQL Wiki的一般建议始终适用。
最后，请注意，这种查询永远不会很快变亮。排序70k行需要时间-并且引擎必须对所有70k行进行排序，然后才能应用'QQQ'。
正如我从您的更新中看到的，查询计划器使用indexticker = 'QQQ' AND interval_end <= 1326810600000-这表明排序是查询中最昂贵的操作。
当您在排序之前根据多个条件进行选择时，我看不出索引如何进一步帮助您进行排序。不能对数据进行预排序和预选择-除非LIMIT 19960子句稳定，否则可以创建如下部分索引：

CREATE INDEX onemin_interval_interval_start_part_idx
  ON    onemin_interval (interval_start DESC)
  WHERE ticker = 'QQQ'::character(10)
  AND   interval_end <= 1326810600000

... 从而减轻脂肪的执行。但我想你没那么幸运吧？
如果其中一个标准是稳定的，那么它已经有了很大的帮助，所以你可以在这个标准上有一个部分索引。