Re: extract epoch and index scanning

[Randall Skelton <skelton@brutus.uwaterloo.ca>]

This seems to be peculuar to our operational database which is version 
7.2.x.  Version 7.4 gives the index scan.

Cheers,
Randall

On 8 Apr 2004, at 14:28, Randall Skelton wrote:

> Can someone explain why the latter query uses an index scan while the 
> former uses a sequence scan?  I have tried all sorts of casting in the 
> first case but I cannot get the index scan.
>
> Cheers,
> Randall
>
> ==
> data=> EXPLAIN ANALYZE SELECT acqtimestamp FROM aux_datarecord where 
> acqtimestamp between (extract(epoch from timestamp '2004-02-21 
> 22:39:57+00') - 50716800)::double precision and (extract(epoch from 
> timestamp '2004-02-21 22:43:52+00'::timestamp) - 50716800.0)::double 
> precision;
> NOTICE:
> QUERY PLAN:
>
> Seq Scan on aux_datarecord  (cost=100000000.00..100548707.00 
> rows=477476 width=8) (actual time=114670.09..114670.09 rows=0 loops=1)
> Total runtime: 114670.23 msec
>
> EXPLAIN
>
> data=> EXPLAIN ANALYZE SELECT acqtimestamp FROM aux_datarecord where 
> acqtimestamp between '1026686397'::double precision and 
> '1026686632'::double precision;
> NOTICE:
> QUERY PLAN:
>
> Index Scan using aux_datarecord_acqtimestamp on aux_datarecord  
> (cost=0.00..3.54 rows=1 width=8) (actual time=0.66..0.66 rows=0 
> loops=1)
> Total runtime: 0.83 msec
>
> EXPLAIN
> ==
>
>
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)