我在表预订中有到达时间戳类型的列(我正在使用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年),则优化器仍然可以选择全表扫描.因人而异.解释是你的朋友
