From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Stan Bielski <bielski(at)ece(dot)cmu(dot)edu> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Indexing question |
Date: | 2004-06-20 18:45:06 |
Message-ID: | 4119.1087757106@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Stan Bielski <bielski(at)ece(dot)cmu(dot)edu> writes:
> Table "public.allflow_tv_mydoom"
> Column | Type | Modifiers
> ------------+---------+-----------
> tv_s | bigint |
^^^^^^
> Indexes: allflow_tv_mydoom_x btree (tv_s)
> standb=# explain select * from allflow_tv_mydoom where tv_s < 1074200099
> and tv_s > 107506499;
> [ gives seqscan ]
This is a FAQ :-(. Unadorned integer constants are taken to be int4
not int8 (unless they are too large for int4), and cross-data-type
comparisons are not indexable in existing releases. So you have to
explicitly cast the comparison values to int8:
explain select * from allflow_tv_mydoom where tv_s < 1074200099::bigint
and tv_s > 107506499::bigint;
(or use the standard CAST syntax if you prefer).
7.5 will have a fix for this ancient annoyance.
BTW, is there a reason to be using tv_s+tv_us and not just a single
timestamptz column?
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Rod Taylor | 2004-06-20 20:02:17 | Re: pg_fetch_array |
Previous Message | Janio Rosa da Silva | 2004-06-20 16:39:38 | Hi! |