index scan and functions

Поиск
Список
Период
Сортировка
От arno
Тема index scan and functions
Дата
Msg-id 20100719155548.GA15921@localhost
обсуждение исходный текст
Ответы Re: index scan and functions  (Sam Mason <sam@samason.me.uk>)
Список pgsql-general
Hi,
In a table, I've some geoip informations with indexes to two colums

 \d geoip
         Table « public.geoip »
 Colonne  |     Type     | Modificateurs
----------+--------------+---------------
 begin_ip | bigint       |
 end_ip   | bigint       |
 country  | character(2) |
Index :
    "geoip_begin_idx" btree (begin_ip)
    "geoip_end_idx" btree (end_ip)

when I try to select stuffs form this table, request is fast:



syj=> explain select * from geoip where  begin_ip <= 2130706433 and end_ip >= 2130706433;
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Index Scan using geoip_end_idx on geoip  (cost=0.00..1448.46 rows=26967 width=19)
   Index Cond: (end_ip >= 2130706433)
   Filter: (begin_ip <= 2130706433)
(3 lignes)


But when using a custom function to compute my where parameter, request is slow:

syj=> explain select * from geoip where  begin_ip <= inet_to_bigint('127.0.0.1') and end_ip >=
inet_to_bigint('127.0.0.1');
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Seq Scan on geoip  (cost=0.00..67654.95 rows=14418 width=19)
   Filter: ((begin_ip <= inet_to_bigint('127.0.0.1'::inet)) AND (end_ip >= inet_to_bigint('127.0.0.1'::inet)))
(2 lignes)

inet_to_bigint is a function that transform an inet address its integer representation.


Is there a way, either to put function return value in a variable, or to tell
postgres to still use a sequential scan ?

thanks

Вложения

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

Предыдущее
От: Nicolas Payart
Дата:
Сообщение: Re: Can not change log_min_duration_statement parameter on PG 8.2.4
Следующее
От: Sam Mason
Дата:
Сообщение: Re: index scan and functions