Forums PostgreSQL.fr

ilrico

Membre

multi index et between

Bonjour, ce problème doit être fréquent mais je n'ai pas trouvé de solution sur google.

J'ai une table avec start_ip bigint, end_ip bigint, loc_id integer.
Je cherche à trouver loc_id lorsque mon ip est comprise entre start_ip et end_ip.

anb=> explain (select loc_id from ipblocks where 3649595313 between start_ip and end_ip);
                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on ipblocks  (cost=0.00..38340.18 rows=22560 width=4)
   Filter: ((3649595313::bigint >= start_ip) AND (3649595313::bigint <= end_ip))
(2 rows)

La création d'un index multiple sur start_ip, end_ip n'apporte aucune amélioration.

anb=> explain (select loc_id from ipblocks where 3649595313 between start_ip and end_ip);
                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on ipblocks  (cost=0.00..38340.18 rows=20974 width=4)
   Filter: ((3649595313::bigint >= start_ip) AND (3649595313::bigint <= end_ip))
(2 rows)

D'après ce que j'ai lu sur le web c'est normal.
Cela semble venir de la méthode btree par default pour un index multiple, si j'ai bien compris...
(cf ce lien http://archives.postgresql.org/pgsql-ge … g01072.php )

J'ai également tenté de créer un index uniquement sur la première colonne (start_ip), mais à nouveau il y a strictement aucune amélioration.

La seule manière que j'ai trouvé, c'est d'ajouter "limit 1" car je sais que je n'attend qu'un resultat (il n'y a pas de chevauchement normalement entre les blocs d'ip).
(d'après ce texte sur l'analyse du problème similaire sous Oracle : http://www.orafaq.com/node/1798 )

Cela a réduit significativement le temps de requête :

anb=> explain (select loc_id from ipblocks where 3649595313 between start_ip and end_ip limit 1);
                                      QUERY PLAN
---------------------------------------------------------------------------------------
Limit  (cost=0.00..1.91 rows=1 width=4)
   ->  Seq Scan on ipblocks  (cost=0.00..38340.18 rows=20053 width=4)
         Filter: ((3649595313::bigint >= start_ip) AND (3649595313::bigint <= end_ip))

Avez-vous une idée (plus clean que 'limit 1') pour réduire le temps de requête ?

Merci d'avance !

rjuju

Administrateur

Re : multi index et between

Bonjour.
Quelle version de postgresql utilisez-vous ?

Un index sur start_ip devrait être utilisé s'il est présent.

Un index multiple sur start_ip,end_ip devrait également être utilisé. Si ce n'est pas le cas, cela vient sans doute d'un problème autre, statistiques de la table ou configuration postgresql par exemple.

---

Julien.
https://rjuju.github.io/

gleu

Administrateur

Re : multi index et between

Pour qu'un index soit utilisé, il faut que l'ensemble de lignes à récupérer dans cet index soit très faible. Donc une première question est de savoir combien de lignes se trouvent dans la table ipblocks. Une autre question est de connaître les statistiques de PostgreSQL sur les deux colonnes recherchées, ce qui peut se connaître avec la requête :

select * from pg_stats where tablename='ipblocks' and attname in ('start_ip', 'end_ip');

Enfin, dernier point, un EXPLAIN ANALYZE donnerait plus d'informations et permettrait donc de mieux vous aider.

---

Guillaume.

Marc Cousin

Membre

Re : multi index et between

Bonjour,

Un autre note: avez vous regardé les types natifs dans PostgreSQL (http://www.postgresql.org/docs/9.1/interactive/datatype-net-types.html et http://www.postgresql.org/docs/9.1/inte … -net.html), voire ceci : http://pgfoundry.org/projects/ip4r/

Ce sont des types de données optimisés pour ça (surtout ip4r, mais c'est externe, donc à compiler en plus). Déjà avec le type inet/cidr, vous pouvez faire des recherches de ce genre qui passent par un index:

CREATE TABLE test_cidr (a cidr);
CREATE INDEX idx1 on test_cidr (a );

Je remplis la table de cochonneries…

Ensuite on peut écrire:

EXPLAIN ANALYZE select * from test_cidr where a <<= '192.168.0.0'::inet;
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
Index Scan using idx1 on test_cidr  (cost=0.00..8.27 rows=25600 width=7) (actual time=0.010..0.010 rows=0 loops=1)
   Index Cond: (((a)::inet >= '192.168.0.0'::inet) AND ((a)::inet <= '192.168.0.0'::inet))
   Filter: ((a)::inet <<= '192.168.0.0'::inet)
Total runtime: 0.032 ms

Ça marche aussi si on met à droite un autre cidr:

marc=# EXPLAIN ANALYZE select * from test_cidr where a <<= '192.168.0.0/20'::cidr;
                                                     QUERY PLAN                                                     
---------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test_cidr  (cost=53.86..304.86 rows=25600 width=7) (actual time=0.461..3.102 rows=1920 loops=1)
   Filter: ((a)::inet <<= '192.168.0.0/20'::inet)
   ->  Bitmap Index Scan on idx1  (cost=0.00..47.46 rows=1920 width=0) (actual time=0.430..0.430 rows=1920 loops=1)
         Index Cond: (((a)::inet >= '192.168.0.0/20'::inet) AND ((a)::inet <= '192.168.15.255'::inet))
Total runtime: 4.669 ms
(5 lignes)

Attention, l'index ne marche que pour <<= (le plan cidr de gauche contient le plan cidr de droite). C'est l'intérêt de ip4r qui sait faire des choses plus compliquées.

Bon évidemment, ça ne marche que si le range d'ip peut être écrit en cidr… ce qui est, je l'espère, le cas.

---

Marc.