From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stan Bielski <bielski@ece.cmu.edu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Indexing question
Date: Sun, 20 Jun 2004 14:45:06 -0400
Message-ID: <4119.1087757106@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.3.96L.1040615172733.5306P-100000@elysium.pdl.cmu.edu>
References: <Pine.LNX.3.96L.1040615172733.5306P-100000@elysium.pdl.cmu.edu>
Stan Bielski <bielski@ece.cmu.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, bielski@ece.cmu.edu
Subject: Re: Indexing question
In-Reply-To: <4119.1087757106@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox