Thread: [GENERAL] Index not used when using expression

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

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