sql - tstzrange 查询包含的性能不佳

Question

我有 2 个表:

账户交易:

+-------------------------------+--------------------------+------------------------+
|            Column             |           Type           |       Modifiers        |
+-------------------------------+--------------------------+------------------------+
| id                            | integer                  | not null               |
| account_id                    | bigint                   | not null               |
| created                       | timestamp with time zone | not null default now() |
| transaction_type              | text                     | not null               |
| amount                        | numeric(5,2)             | not null               |
| external_reference_id         | character varying(60)    |                        |
+-------------------------------+--------------------------+------------------------+

索引:

"idx_account_transaction_created" btree (created)

报告周期:

+------------+--------------------------+-----------+
|   Column   |           Type           | Modifiers |
+------------+--------------------------+-----------+
| month      | text                     |           |
| created    | timestamp with time zone |           |
| date_range | tstzrange                |           |
+------------+--------------------------+-----------+

我想获取上一个报告期间的所有交易。下面是产生相同结果的两个查询，但一个执行序列扫描，另一个可以使用 idx_account_transaction_created 索引。

explain select count(*) from account_transaction where created <@ (select date_range from reporting_period order by created desc limit 1);
+----------------------------------------------------------------------------------------+
|                                       QUERY PLAN                                       |
+----------------------------------------------------------------------------------------+
| Aggregate  (cost=4214.81..4214.82 rows=1 width=0)                                      |
|   InitPlan 1 (returns $0)                                                              |
|     ->  Limit  (cost=13.20..13.20 rows=1 width=40)                                     |
|           ->  Sort  (cost=13.20..13.60 rows=800 width=40)                              |
|                 Sort Key: reporting_period.created                                     |
|                 ->  Seq Scan on reporting_period  (cost=0.00..12.40 rows=800 width=40) |
|   ->  Seq Scan on account_transaction  (cost=0.00..4200.81 rows=1602 width=0)          |
|         Filter: (created <@ $0)                                                        |
+----------------------------------------------------------------------------------------+
(8 rows)

explain select count(*) from account_transaction where created >= '2014-06-01' and created <= '2014-06-30 23:59:59.999999';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                            QUERY PLAN                                                                            |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Aggregate  (cost=2640.54..2640.54 rows=1 width=0)                                                                                                                |
|   ->  Index Only Scan using idx_account_transaction_created on account_transaction  (cost=0.08..2605.77 rows=69535 width=0)                                      |
|         Index Cond: ((created >= '2014-06-01 00:00:00+00'::timestamp with time zone) AND (created <= '2014-06-30 23:59:59.999999+00'::timestamp with time zone)) |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
(3 rows)

我更喜欢第一个查询，因为它看起来更容易阅读和理解，而且只有一次往返。第二个效率更高，因为它使用创建字段上的索引，但这意味着应用程序将需要出去并获取最后一个报告周期并获取 date_range 字段的下限和上限(这不是最糟糕的事情世界)。而且我想我总是可以把它写成一个函数或一个 View 。但是，我有点惊讶 PostgreSQL 没有弄清楚它可以使用索引。我在这里缺少什么吗？无论如何让第一个查询使用索引？

我正在使用 PostgreSQL 9.3

Answer 1

运营商<@需要 GIN 或 GiST 索引才能使用。不适用于普通 B 树索引。
Details in the manual here.
相关回答:

• Fast table indexing for range lookup
• Best way to join on a range?
• Perform this hours of operation query in PostgreSQL

备选

对于您的用例，B 树索引可能更有效。这应该允许 Postgres 使用它:

SELECT count(*) AS ct
FROM  (
   SELECT lower(date_range) AS ts_from, upper(date_range) AS ts_to
   FROM   reporting_period
   ORDER  BY created DESC
   LIMIT  1
   ) r
JOIN   account_transaction a ON a.created >= r.ts_from
                            AND a.created <  r.ts_to
;

假设你所有的tstzrange值具有包括下限和不包括上限(建议的默认值)。为了强制执行，我建议 CHECK表中的约束 reporting_period :

CHECK (lower_inc(date_range) AND NOT upper_inc(date_range))

否则您需要更详细的条件。相关回答:

• Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL