我有2张桌子：
account_transaction：
+-------------------------------+--------------------------+------------------------+
| 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 | |
+------------+--------------------------+-----------+
我想获取上一个报告期的所有交易。这是两个产生相同结果的查询，但是一个查询进行seq扫描，另一个查询可以使用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字段的上下边界（这不是最糟糕的情况）。世界）。而且我想我总是可以将其编写为函数或视图。但是，我对PostgreSQL没弄清楚它可以使用索引感到有些惊讶。我在这里想念什么吗？无论如何，要使第一个查询使用索引？
我正在使用PostgreSQL 9.3
操作员<@需要使用GIN或GiST索引才能使用。不适用于普通的B树索引。
手册中的详细信息在这里。
相关答案：
对于您的用例，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))
否则，您需要更详尽的条件。相关答案：
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句