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

Started by Pierre-Andre Michelabout 24 years ago6 messagesgeneral

Pierre-Andre Michel

pamichel@geneva-link.ch

about 24 years ago

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

mloftis@wgops.com

almost 24 years ago

In reply to: Pierre-Andre Michel (#1)

Re: 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:

Show quoted text

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

Andrew Sullivan

andrew@libertyrms.info

almost 24 years ago

In reply to: Michael Loftis (#2)

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

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

tgl@sss.pgh.pa.us

almost 24 years ago

In reply to: Michael Loftis (#2)

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

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

mloftis@wgops.com

almost 24 years ago

In reply to: Pierre-Andre Michel (#1)

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

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:

Show quoted text

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

mloftis@wgops.com

almost 24 years ago

In reply to: Pierre-Andre Michel (#1)

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

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.

Show quoted text

regards, tom lane