pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (15+ messages)

Message-ID: <4119.1087757106@sss.pgh.pa.us>
Permalink:  ../4119.1087757106@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/4119.1087757106@sss.pgh.pa.us

 · 

reply

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