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