Thread: index scan and functions

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

On Mon, Jul 19, 2010 at 05:55:48PM +0200, arno wrote:
> But when using a custom function to compute my where parameter

> 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 ?

I'd guess your function is labeled as "VOLATILE".  This is saying that
the function has "side effects", but from the function's name I'd guess
that it doesn't and the only purpose of calling the function is to get
its return value.  I'd expect that labeling it as STABLE would cause PG
to do what you're expecting.  More details here:

  http://www.postgresql.org/docs/current/static/xfunc-volatility.html

--
  Sam  http://samason.me.uk/

Le lundi 19 juillet 2010, à 17:09:02 +0100, Sam a écrit :
> On Mon, Jul 19, 2010 at 05:55:48PM +0200, arno wrote:
> > But when using a custom function to compute my where parameter
>
> > 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 ?
>
> I'd guess your function is labeled as "VOLATILE".  This is saying that
> the function has "side effects", but from the function's name I'd guess
> that it doesn't and the only purpose of calling the function is to get
> its return value.  I'd expect that labeling it as STABLE would cause PG
> to do what you're expecting.  More details here:
>
>   http://www.postgresql.org/docs/current/static/xfunc-volatility.html

Thanks, that's exactly what I was looking for.

In article <20100719162547.GA17618@localhost>,
arno <arno@renevier.net> writes:

> Thanks, that's exactly what I was looking for.

No, I'd say you're looking for the ip4r package which provides
an indexable IP address range type.

Le mardi 20 juillet 2010, à 10:11:21 +0200, Harald a écrit :
> In article <20100719162547.GA17618@localhost>,
> arno <arno@renevier.net> writes:
>
> > Thanks, that's exactly what I was looking for.
>
> No, I'd say you're looking for the ip4r package which provides
> an indexable IP address range type.

Thanks, I'll look into it.

In article <20100719162547.GA17618@localhost>,
arno <arno@renevier.net> writes:

> Thanks, that's exactly what I was looking for.

No, I'd say you're looking for the ip4r package which provides
an indexable IP address range type.