From: Mark Harrison <mh@pixar.com>
To: pgsql-performance@postgresql.org
Subject: coercing int to bigint for indexing purposes
Date: Wed, 11 Feb 2004 14:38:41 -0800
Message-ID: <402AAEF1.2050703@pixar.com> (raw)
Is there a way to automatically coerce an int into a bigint for
indexing purposes?
We have a table with a bigint column that is an index.
For mi, there's no problem, since I now know to say
select * from foo where id = 123::bigint
but our casual users still say
select * from foo where id = 123
causing a sequential scan because the type of 123 is not
a bigint.
As you can see, there's nearly 4 orders of magnitude difference
in time, and we anticipate this will only get worse as our
tables increase in size:
LOG: duration: 0.861 ms statement: select * from big where id = 123123123123123;
LOG: duration: 6376.917 ms statement: select * from big where id = 123;
One thing I have considered is starting our id sequence at 5000000000
so that "real" queries will always be bigint-sized, but this seems
to me a bit of a hack.
Many TIA,
Mark
--
Mark Harrison
Pixar Animation Studios
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: mh@pixar.com
Subject: Re: coercing int to bigint for indexing purposes
In-Reply-To: <402AAEF1.2050703@pixar.com>
* 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