From: | Randall Skelton <skelton(at)brutus(dot)uwaterloo(dot)ca> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | extract epoch and index scanning |
Date: | 2004-04-08 18:28:37 |
Message-ID: | 8D1826E8-898A-11D8-BEF7-000393C92230@brutus.uwaterloo.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
==
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-04-08 18:38:22 | Re: ERROR: invalid memory alloc request size 0 |
Previous Message | Clark Endrizzi | 2004-04-08 18:07:09 | To_char statement problems |