extract epoch and index scanning

Started by Randall Skeltonabout 22 years ago2 messagesgeneral
Jump to latest
#1Randall Skelton
skelton@brutus.uwaterloo.ca

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
==

#2Randall Skelton
skelton@brutus.uwaterloo.ca
In reply to: Randall Skelton (#1)
Re: extract epoch and index scanning

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:

Show quoted text

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