sql – 从Postgres中选择时间戳在一定范围内的记录

我知道我可以这样做：

select * FROM reservations WHERE extract(year from arrival) = 2012;

但我已经进行了分析,看起来像需要序列扫描.
有更好的选择吗？

附： 1嗯两种方式似乎都要求seq.扫描.但是,通过wildplasser的结果会更快地产生结果 – 为什么？

cmm=# EXPLAIN ANALYZE select * FROM reservations WHERE extract(year from arrival) = 2010;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Seq Scan on vrreservations  (cost=0.00..165.78 rows=14 width=4960) (actual time=0.213..4.509 rows=49 loops=1)
   Filter: (date_part('year'::text,arrival) = 2010::double precision)
 Total runtime: 5.615 ms
(3 rows)

cmm=# EXPLAIN ANALYZE SELECT * from reservations WHERE arrival > '2010-01-01 00:00:00' AND arrival < '2011-01-01 00:00:00';
                                                                  QUERY PLAN                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on reservations  (cost=0.00..165.78 rows=51 width=4960) (actual time=0.126..2.491 rows=49 loops=1)
   Filter: ((arrival > '2010-01-01 00:00:00'::timestamp without time zone) AND (arrival < '2011-01-01 00:00:00'::timestamp without time zone))
 Total runtime: 3.144 ms
(3 rows)

** P.S. 2 – 在创建索引到达列之后,第二种方式得到更快 – 因为它看起来像查询使用索引. Mkey – 我想我会跟这个一样. **

QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on reservations  (cost=4.77..101.27 rows=51 width=4960) (actual time=0.359..0.791 rows=49 loops=1)
   Recheck Cond: ((arrival > '2010-01-01 00:00:00'::timestamp without time zone) AND (arrival < '2011-01-01 00:00:00'::timestamp without time zone))
   ->  Bitmap Index Scan on arrival_idx  (cost=0.00..4.76 rows=51 width=0) (actual time=0.177..0.177 rows=49 loops=1)
         Index Cond: ((arrival > '2010-01-01 00:00:00'::timestamp without time zone) AND (arrival < '2011-01-01 00:00:00'::timestamp without time zone))
 Total runtime: 1.265 ms

解决方法

SELECT * 
FROM reservations 
WHERE arrival >= '2012-01-01'
AND arrival < '2013-01-01'
   ;

BTW如果值的分布表示索引扫描不是值得的(例如,如果所有值都在2012年),则优化器仍然可以选择全表扫描.因人而异.解释是你的朋友

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!