| From: | arno <arno(at)renevier(dot)net> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | index scan and functions |
| Date: | 2010-07-19 15:55:48 |
| Message-ID: | 20100719155548.GA15921@localhost |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Sam Mason | 2010-07-19 16:09:02 | Re: index scan and functions |
| Previous Message | Nicolas Payart | 2010-07-19 15:03:29 | Re: Can not change log_min_duration_statement parameter on PG 8.2.4 |