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?
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? |