Re: Index not used when using expression

Dingyuan Wang <gumblex(at)aosc(dot)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