Re: [GENERAL] index scan and functions

[arno]

Le mardi 20 juillet 2010, à 10:11:21 +0200, Harald a écrit : 
 In article 20100719162547.ga17...@localhost,
 arno a...@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.


signature.asc
Description: Digital signature

[GENERAL] index scan and functions

[arno]

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


signature.asc
Description: Digital signature

Re: [GENERAL] index scan and functions

[arno]

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.


signature.asc
Description: Digital signature