Index not used when using expression

From: Dingyuan Wang <gumblex(at)aosc(dot)io>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Index not used when using expression
Date: 2017-11-09 16:27:59
Message-ID: 42dbce76-9986-f9a0-e29d-12013e1b8b8e@aosc.io
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2017-11-09 16:33:54 Re: Index not used when using expression
Previous Message rammohan ganapavarapu 2017-11-09 16:22:37 Re: Can master and slave on different PG versions?