Force index usage on bigint in PL/PGSQL

Поиск
Список
Период
Сортировка
От Federico Pedemonte
Тема Force index usage on bigint in PL/PGSQL
Дата
Msg-id 20030116151704.GB3362@protciv14.cmirl.arpal.it
обсуждение исходный текст
Ответы Re: Force index usage on bigint in PL/PGSQL  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Bruno Wolff III
Дата:
Сообщение: Re: three timestamps/table, use of 'now' in table creation statement
Следующее
От: Geert Bevin
Дата:
Сообщение: Unsolveable query?