Re: [GENERAL] Index not used when using expression

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