Dingyuan Wang <gumblex@aosc.io> writes:
> 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))
The reason that's not working for you is that the query is not testing
packettime, it's testing packettime::float8, because date_part() returns
float8. You could cast the result of date_part() to bigint, or whatever
type the packettime column actually is, so that the comparison is to
the unadorned variable.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general