db=> explain analyze SELECT rep_id, rmonth, grs_sales_tot AS grs, net_sales_tot AS net, cost_tot AS cost
from sales_report
WHERE rdate > '2022-01-01' and rdate < '2023-01-01' ;QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
-------
Bitmap Heap Scan on sales_report (cost=119.73..1220.82 rows=8140 width=31) (actual time=0.971..6.869 rows=8032 loops=1)
Recheck Cond: ((rdate > '2022-01-01'::date) AND (rdate < '2023-01-01'::date))
Heap Blocks: exact=941
-> Bitmap Index Scan on sales_report_rdate_idx (cost=0.00..117.69 rows=8140 width=0) (actual time=0.804..0.805 rows=8032 lo
ops=1)
Index Cond: ((rdate > '2022-01-01'::date) AND (rdate < '2023-01-01'::date))
Planning Time: 0.124 ms
Execution Time: 7.430 ms
(7 rows)explain analyze SELECT rep_id, rmonth, grs_sales_tot AS grs, net_sales_tot AS net, cost_tot AS cost
from sales_report
WHERE rdate <@ daterange('2022-01-01', '2023-01-01') ;QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Seq Scan on sales_report (cost=0.00..1577.85 rows=240 width=31) (actual time=0.021..12.524 rows=8032 loops=1)
Filter: (rdate <@ '[2022-01-01,2023-01-01)'::daterange)
Rows Removed by Filter: 39876
Planning Time: 0.106 ms
Execution Time: 12.983 ms
(5 rows)如何强制daterange使用索引？
select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
1条答案
按热度按时间qhhrdooz1#
你不能以任何有用的方式。
如果不重写查询，它将无法正常工作。如果你要重写查询，你最好用你已经知道的方法重写。
您可以创建一个索引
using gist (daterange(rdate,rdate,'[]'))，然后通过生成查询where daterange(rdate,rdate,'[]') <@ daterange('2022-01-01', '2023-01-01')来使用它但是，由于这仍然需要重写查询，并且比原始查询稍慢，因此可能没有太大意义。（此外，它与原始查询不完全相同，因为它包括精确的下边界，而原始查询排除了它。