Thread: Why does postgres not take into account my index on a bigint column ?

IMHO this is a bug in PostgreSQL....  Ints of any type are not supposed
to be quoted, and whether-or-not they are shouldn't affect the planning
and execution of the query...

Anyone from -hackers read this?  Or should I repost it there?

Pierre-Andre Michel wrote:

> Hi,
>
>
>
> I noticed that postgres does not take into account index on BIGINT
> column when the value specified in the WHERE condition is not quoted.
>
> My problem is that I use third part programs that automatically
> generate many select queries where numeric values are not surrounded
> with quotes.
>
>
>
> So does anybody know a way to force postgres to consider using indices
> on bigint columns even when the value in a where cond is not
> surrounded by quotes ?
>
>
>
> Thanks
>
>
>
> ----
>
> For instance, in the following 'header' table I have a bigint column
> 'objectId'. Here is what I get when running the explain command:
>
>
>
> idns_dev=# explain select * from header where objectid = '47866';
>
>
>
> NOTICE:  QUERY PLAN:
> Index Scan using header_pkey on header  (cost=0.00..3.92 rows=1 width=552)
> EXPLAIN
>
>
>
> idns_dev=# explain select * from header where objectid = 47866;
>
>
>
> NOTICE:  QUERY PLAN:
> Seq Scan on header  (cost=0.00..2246.50 rows=1 width=552)
> EXPLAIN
>
> ------
>
>
>
>
>
>
>
> ______________________
> Pierre-André Michel
>
>
>
> SmartGene SA
> PSE Bâtiment C
> EPFL, Ecublens
> CH-1015 Lausanne
>
>
>
> tél. prof.: (+4121) 693 85 84
> mobile:    (+4178) 681 53 03
>

On Mon, Apr 15, 2002 at 08:16:38AM -0700, Michael Loftis wrote:
> IMHO this is a bug in PostgreSQL....  Ints of any type are not supposed
> to be quoted, and whether-or-not they are shouldn't affect the planning
> and execution of the query...

This is a well-known problem with bigint.  The problem is really in
the handling of the datatype: for some reason (the details were in a
recent thread about the issue), the values get interpreted as int4
and there is no automatic cast to int8.

When you quote the value, it gets treated as type unknown, and then
gets cast correctly.  There's been quite a bit of discussion, ISTM,
about how to fix it, but no consensus.

> Pierre-Andre Michel wrote:
>
> > Hi,
> >
> >
> >
> > I noticed that postgres does not take into account index on BIGINT
> > column when the value specified in the WHERE condition is not quoted.

> > So does anybody know a way to force postgres to consider using indices
> > on bigint columns even when the value in a where cond is not
> > surrounded by quotes ?

The alternative to quotes is to use an explicit cast.

A

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

Michael Loftis <mloftis@wgops.com> writes:
> Anyone from -hackers read this?  Or should I repost it there?

Don't bother, we've heard it before ;-)

You may care to consult the pghackers archives for previous discussions
about this problem and why it's not trivial to fix.  Eventually someone
will come up with a proposal that has a tolerable amount of collateral
damage ...

            regards, tom lane

Tom Lane wrote:

>Michael Loftis <mloftis@wgops.com> writes:
>
>>Anyone from -hackers read this?  Or should I repost it there?
>>
>
>Don't bother, we've heard it before ;-)
>
Yah, Andrew Sul;livan just clued me in.  Wasn't connecting dots until
just now.

>You may care to consult the pghackers archives for previous discussions
>about this problem and why it's not trivial to fix.  Eventually someone
>will come up with a proposal that has a tolerable amount of collateral
>damage ...
>
Well... I'm sure I can come up with a solution, but just for a frame of
reference when Windows and my BIOS started acting up and mis-configuring
IRQs I just labotomized the BIOS....  Collateral schmollateral.  Nuke em
till they glow and let God sort 'em out.  Hehehe :)

Seriously though this is something that could be a biiiiig problem for
me so I intend to look into it, re-read the threads I can find in
pghackers and maybe I can think of something.  Sometimes all that is
needed is a fresh set of eyes.

>
>
>            regards, tom lane
>

Ahhhh OK I know.  I wasn't putting 2+2 together.  Time for more coffee.
 That whole discussion about unknown types on -hackers makes a boat load
more sense now.  This is a problem for me and the way an app I'm
currently writing will work (or rather will be) so I'll have to look
into it much more deeply thats for sure.



Andrew Sullivan wrote:

>On Mon, Apr 15, 2002 at 08:16:38AM -0700, Michael Loftis wrote:
>
>>IMHO this is a bug in PostgreSQL....  Ints of any type are not supposed
>>to be quoted, and whether-or-not they are shouldn't affect the planning
>>and execution of the query...
>>
>
>This is a well-known problem with bigint.  The problem is really in
>the handling of the datatype: for some reason (the details were in a
>recent thread about the issue), the values get interpreted as int4
>and there is no automatic cast to int8.
>
>When you quote the value, it gets treated as type unknown, and then
>gets cast correctly.  There's been quite a bit of discussion, ISTM,
>about how to fix it, but no consensus.
>
>>Pierre-Andre Michel wrote:
>>
>>>Hi,
>>>
>>>
>>>
>>>I noticed that postgres does not take into account index on BIGINT
>>>column when the value specified in the WHERE condition is not quoted.
>>>
>
>>>So does anybody know a way to force postgres to consider using indices
>>>on bigint columns even when the value in a where cond is not
>>>surrounded by quotes ?
>>>
>
>The alternative to quotes is to use an explicit cast.
>
>A
>