Pavel Stehule <pavel.stehule@gmail.com> writes:
> -- I was surprised, so following query can use index
> postgres=# explain select a from test2 where a at time zone
> 'America/Santiago' >= now() at time zone 'America/Santiago' ;
> QUERY
> PLAN
> ══════════════════════════════════════════════════════════════════════════════════════════════
> Index Only Scan using test2_a_idx on test2 (cost=0.13..12.18 rows=1
> width=8)
> Filter: (timezone('America/Santiago'::text, a) >=
> timezone('America/Santiago'::text, now()))
> (2 rows)
This plan isn't actually "using" the index in any meaningful way; it's
applying the where condition as a filter. It happens to be sane to use
the index as a dumb data source, because it can be an index-only scan, and
that might (if you're lucky and don't hit too many recheckable rows) be
cheaper than a seqscan. But we don't consider plain indexscans as worth
the trouble to consider in such cases, because a full-table plain
indexscan can never beat a seqscan, either in the planner's cost model or
in reality.
> why, the index isn't used in this case?
> postgres=# explain select a,b from test2 where a at time zone
> 'America/Santiago' >= now() at time zone 'America/Santiago' ;
Can't be an index-only scan because of the use of b, so there's no
possible way that this can be better than a seqscan.
regards, tom lane
