| 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: | Whole Thread | Raw Message | 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? |