Force index usage on bigint in PL/PGSQL

From: Federico Pedemonte <fepede(at)inwind(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: Force index usage on bigint in PL/PGSQL
Date: 2003-01-16 15:17:04
Message-ID: 20030116151704.GB3362@protciv14.cmirl.arpal.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Geert Bevin 2003-01-16 15:22:55 Unsolveable query?
Previous Message Bruno Wolff III 2003-01-16 14:44:23 Re: three timestamps/table, use of 'now' in table creation statement