Hi,
I have a table named "gps", with an indexed column "packettime", which
has unix timestamps.
The following query:
select * from gps where packettime < extract(epoch from '2017-05-01
08:00+08'::timestamp with time zone)
explains to:
Seq Scan on gps (cost=0.00..43411860.64 rows=384325803 width=120)
Filter: ((packettime)::double precision < date_part('epoch'::text,
'2017-05-01 08:00:00+08'::timestamp with time zone))
While this query:
select * from gps where packettime < 1493596800
explains to:
Index Scan using idx_gps_packettime on gps (cost=0.58..2661058.92
rows=8912880 width=120)
Index Cond: (packettime < 1493596800)
The above behaviour is the same whether the query is long or short.
PostgreSQL doesn't seem like to pre-compute the function value.
So how can I make it pre-compute the timestamp and use the index, or I
have to manually calculate the timestamp? Is this an intended behaviour?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
