Force index usage on bigint in PL/PGSQL

Started by Federico Pedemonteabout 23 years ago2 messagesgeneral
Jump to latest
#1Federico Pedemonte
fepede@inwind.it

Hi all,

Reading the pg archives i found out the reason why i have bad db
performance. It's becase of the quotes around the bigint issue.

I have a large (23M records) table (called data) on which i've create an
index on the field 'ora' that is a bigint. Explain told me that the
following query doesn't use this index

select codice, ora, tem from data where ora > 200210230000;

while this one does

select codice, ora, tem from data where ora > '200210230000';

The problem is that i'd like to "force" my plpgsql functions to use
indexes.

Is there a way I can rewrite the following plpgsql query to use the
quote-trick ?

result record;
orain alias for $1; -- bigint
orafin alias for $2; -- bigint

for result in
select rai, tem -- rai and tem are fields of the table
from data
where (codice LIKE cod_staz and
ora > orain and
ora <= orafin ) loop

By the way, has this thing been fixed in the newest versions of postgres
? I'm using the "debianized" version 7.2.1

Thanks in advance !

Bye, Federico.

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Federico Pedemonte (#1)
Re: Force index usage on bigint in PL/PGSQL

Federico Pedemonte <fepede@inwind.it> writes:

The problem is that i'd like to "force" my plpgsql functions to use
indexes.
Is there a way I can rewrite the following plpgsql query to use the
quote-trick ?

result record;
orain alias for $1; -- bigint
orafin alias for $2; -- bigint

If $1 and $2 are in fact declared as bigint, then you don't have a
problem.

regards, tom lane