[POSTGRESQL] Bitfield et Index

On 20/09/2010 12:26, Etienne wrote:

J'ai donc crée l'index suivant
CREATE idx_catalogue1 ON product ((actif_bitfield & 1));

et pour sélectionner les produits du catalogue 1 je fais
SELECT * FROM product WHERE (actif_bitfield & 1) = 1;

Et là, semblerai que mon index ne servent à rien...
pourquoi ??? Ai je fais une erreur ou postgreSQL a juste décide de ne
pas utiliser mon index ?

Salut,

Ok pour l'index, mais il faut voir l'explain analyse de la requete pour
en dire plus. Il est par exemple possible qu'il décide de ne pas
utiliser l'index parce qu'il y a trop peu de donnée dans la table.

--
Sébastien

Le 20/09/2010 17:11, Sebastien Lardiere a écrit :

On 20/09/2010 12:26, Etienne wrote:
Salut,

Ok pour l'index, mais il faut voir l'explain analyse de la requete pour
en dire plus. Il est par exemple possible qu'il décide de ne pas
utiliser l'index parce qu'il y a trop peu de donnée dans la table.

Mouaip.
c'est byzarre.
ma table produit hérite d'une autre table (celle qui contient
actif_bitfield)

si je crée l'index sur la table parent il n'est pas utilisé et si je le
crée sur la table produit directement il l'est.
C'est donc sans doute un choix de postgres. car je ne vois pas ce qui
l'empècherai d'utiliser l'index créé sur la table parent.

Par contre la grosse question qui reste en suspend c'est est il possible
de regrouper les indexes.
CREATE idx_catalogue1 ON product ((actif_bitfield & 1));
CREATE idx_catalogue2 ON product ((actif_bitfield & 2));
CREATE idx_catalogue3 ON product ((actif_bitfield & 4));
CREATE idx_catalogue4 ON product ((actif_bitfield & 8));

en un seul ?

Comment peut on savoir la taille (sur disque et mémoire) prise par les
tables et par les indexes.

Etienne

On 21/09/2010 08:48, Etienne wrote:

Salut,

ma table produit hérite d'une autre table (celle qui contient
actif_bitfield)

si je crée l'index sur la table parent il n'est pas utilisé et si je le
crée sur la table produit directement il l'est.
C'est donc sans doute un choix de postgres. car je ne vois pas ce qui
l'empècherai d'utiliser l'index créé sur la table parent.

Voila, tu connais la réponse,

Par contre la grosse question qui reste en suspend c'est est il possible
de regrouper les indexes.
CREATE idx_catalogue1 ON product ((actif_bitfield & 1));
CREATE idx_catalogue2 ON product ((actif_bitfield & 2));
CREATE idx_catalogue3 ON product ((actif_bitfield & 4));
CREATE idx_catalogue4 ON product ((actif_bitfield & 8));

en un seul ?

Oui, bien sur, mais est-ce que ça répond au besoin, je ne sais pas :

CREATE idx_catalogue ON product ((actif_bitfield & 1), (actif_bitfield &
2), (actif_bitfield & 4), (actif_bitfield & 8));

Mais c'est un autre index pour un autre usage.

Comment peut on savoir la taille (sur disque et mémoire) prise par les
tables et par les indexes.

select pg_relation_size('product') ; -> la taille de la table.
select pg_total_relation_size('product') ; -> la taille de la table et
de ses index.
select pg_relation_size('idx_catalogue1') ; -> la taille de l'index.

--
Sébastien

Le Mon, 20 Sep 2010 12:26:39 +0200, Etienne a écrit:

par exemple un produit est t-il actif pour un catalogue précis.

ma table est du genre
CREATE TABLE product (
"idproduct" serial,
...
"actif_bitfield" integer,
PRIMARY KEY (idproduct)
);

Ce n'est pas un schéma très normalisé, vous savez... ?!
Vous êtes sûr de vouloir faire comme ca ?

J'ai donc crée l'index suivant
CREATE idx_catalogue1 ON product ((actif_bitfield & 1));

Pourquoi pas directement sur actif_bitfield ?

et pour sélectionner les produits du catalogue 1 je fais SELECT * FROM
product WHERE (actif_bitfield & 1) = 1;

Et là, semblerai que mon index ne servent à rien... pourquoi ??? Ai je
fais une erreur ou postgreSQL a juste décide de ne pas utiliser mon
index ?

Si PostgreSQL ne s'en sert pas c'est qu'il a, a priori de bonnes raisons
de ne pas s'en servir:
- table trop petite
- table non analysée récemment
- requêtes non compatibles avec l'usage d'un index
- requêtes envoyant trop de résultats (la lecture séquentielle sera alors
plus rapide)
- tests faits avec des données de tests (aléatoires, séquentielles, etc.)
dans les tables éloignées des données utilisées en production
(un cas classique mais pas toujours mentionné)
- et plein d'autres raisons possibles, selon les paramètres, etc.

Je crois l'avoir déjà dit, mais l'usage d'un index n'est pas une fin en
soit... Si cela va suffisamment vite sans, pourquoi le forcer ?
Il faut faire attention de ne pas optimiser en amont en dépensant une
énergie folle à un endroit où il n'y a en fait quasi rien à optimiser
(pour un résultat *visible* au final, toutes étapes confondues).

Si vous voulez absolument, vous pouvez toujours le forcer en désactivant
la lecture séquentielle par exemple pour votre requête, vous verrez bien
ce qu'il se passe... et que ca ne se passe pas forcément plus vite que
sans l'index !

PS: y a que moi qui ai des questions à poser sur les SGBDs ??? :)

PostgreSQL 9.0 est sorti :-)
Et il y a dedans des nouvelles fonctions utiles pour manipuler les champs
de bits.

.... WHERE get_bit(actif_bitfield::bytea,1) = 1

(mais je doute que cela soit intéressant question performances)

--
Patrick Mevzek . . . . . . . . . . . . . . Dot and Co
<http://www.dotandco.net/> <http://www.dotandco.com/>
<http://www.dotandco.net/ressources/icann_registrars/prices>
<http://icann-registrars-life.dotandco.net/>

Le Tue, 21 Sep 2010 08:48:10 +0200, Etienne a écrit:

si je crée l'index sur la table parent il n'est pas utilisé et si je le
crée sur la table produit directement il l'est. C'est donc sans doute un
choix de postgres. car je ne vois pas ce qui l'empècherai d'utiliser
l'index créé sur la table parent.

Vous utilisez donc simultanément un schéma "bizarre" (dont le mérite par
rapport à une forme normalisée n'est certes pas acquis d'avance) avec une
fonctionnalité de PostgreSQL qui marche "moyennement", donc vous vous
mettez beaucoup d'obstacles.

Je cite http://www.postgresql.org/docs/9.0/static/ddl-inherit.html :

5.8.1. Caveats
[..]

A serious limitation of the inheritance feature is that indexes
(including unique constraints) and foreign key constraints only apply to
single tables, not to their inheritance children.

Par contre la grosse question qui reste en suspend c'est est il possible
de regrouper les indexes.

Pourquoi faites-vous donc tant une fixation sur cet index ? Avez-vous
réellement identifié un problème avec vos requêtes en production ?
Où n'est-ce que de l'optimisation prématurée ?

--
Patrick Mevzek . . . . . . . . . . . . . . Dot and Co
<http://www.dotandco.net/> <http://www.dotandco.com/>
<http://www.dotandco.net/ressources/icann_registrars/prices>
<http://icann-registrars-life.dotandco.net/>

Le 20/09/10 12:26, Etienne a écrit :

Salut.

Je me rebiffe avec mon idée de champ de bit.

Bonjour Etienne !

Juste pour info : PostgreSQL 9.0, encore tout chaudement sorti du four,
propose de nouvelles fonctions de manipulations de bits nommées set_bit
et get_bit.

A la différence près qu'ils utilisent des varchar au lieu des integer
utilisés dans tes exemples.

cf
<http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.0#get_bit_and_set_bit_for_bit_strings>

En espérant que cela t'intéressera,

--
Bruno Baguette

Le 20/09/2010 12:26, Etienne a écrit :

Salut.

Je me rebiffe avec mon idée de champ de bit.
en fait j'ai des bits qui active une donnée en fonction du bit allumé.

par exemple un produit est t-il actif pour un catalogue précis.

ma table est du genre
CREATE TABLE product (
"idproduct" serial,
...
"actif_bitfield" integer,
PRIMARY KEY (idproduct)
);

Supponsons que le bit 1 concerne le catalogue 1
le 2 le catalogue 2, ...

REM: Ok je suis limité a 32 catalogue, mais c'est un detail vu que j'en
ai que 4.

J'ai donc crée l'index suivant
CREATE idx_catalogue1 ON product ((actif_bitfield & 1));

et pour sélectionner les produits du catalogue 1 je fais
SELECT * FROM product WHERE (actif_bitfield & 1) = 1;

Et là, semblerai que mon index ne servent à rien...
pourquoi ??? Ai je fais une erreur ou postgreSQL a juste décide de ne
pas utiliser mon index ?

1) un WHERE contenant une fonction ne peut pas utiliser un index car le
prédicat de jointure n'est pas "sargeable".
Vous avez créé un index calculé, c'est bien... mais
2) la distribution (ou son inverse, la sélectivité) des données dans une
colonne ou une expression n'ayant que 2 valeurs (0 ou 1) est faible et
donc l'utilisation d'un index quasi nul, sauf si ladite distribution est
très déséquilibrée en faveur de l'une des deux valeurs et uniquement
pour un filtre appliqué à la valeur la plus faible (moins de 30% dans
tous les cas si l'index est couvrant, mais plutôt 10% en général).

Lisez les articles que j'ai écrit à ce sujet. En particulier :
http://sqlpro.developpez.com/cours/quoi-indexer/
http://sqlpro.developpez.com/optimisation/indexation/ComparaisonRequetesIndexation2.pdf

Sur la "sargeabilité" :
http://sqlpro.developpez.com/cours/quoi-indexer/#LVI-B
Sur la sélectivité des index :
http://sqlpro.developpez.com/optimisation/indexation/ComparaisonRequetesIndexation2.pdf
voir la progression de l'utilisation de l'index en fonction de sa
composition dans ce papier.

A +

Etienne

PS: dans la foulé, y a t-il un index unique que je puisse créer pour
remplacer mes 4 indexes:
CREATE idx_catalogue1 ON product ((actif_bitfield & 1));
CREATE idx_catalogue2 ON product ((actif_bitfield & 2));
CREATE idx_catalogue3 ON product ((actif_bitfield & 4));
CREATE idx_catalogue4 ON product ((actif_bitfield & 8));

Merci
Etienne

PS: y a que moi qui ai des questions à poser sur les SGBDs ??? :)

--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************

Le 25/09/10 11:33, SQLpro a écrit :

1) un WHERE contenant une fonction ne peut pas utiliser un index car
le prédicat de jointure n'est pas "sargeable".

Dans le cas de PostgreSQL, à partir du moment ou une fonction est
marquée comme étant immutable, elle peut parfaitement être indexée.

Et PostgreSQL ne se privera pas d'utiliser cet index s'il juge utile de
l'utiliser.

Une fonction immutable est une fonction dont le résultat ne dépend
strictement que de ses arguments et en aucun cas de valeurs provenant
d'autres tables ou basée selon l'heure.

cf. <http://www.postgresql.org/docs/9/static/sql-crea/functteindex.html>

HTH,

--
Bruno Baguette

Le 25/09/2010 11:33, SQLpro a écrit :

Le 20/09/2010 12:26, Etienne a écrit :
1) un WHERE contenant une fonction ne peut pas utiliser un index car le
prédicat de jointure n'est pas "sargeable".
Vous avez créé un index calculé, c'est bien... mais

sisi ca fonctionne avec postgresql.

2) la distribution (ou son inverse, la sélectivité) des données dans une
colonne ou une expression n'ayant que 2 valeurs (0 ou 1) est faible et
donc l'utilisation d'un index quasi nul, sauf si ladite distribution est
très déséquilibrée en faveur de l'une des deux valeurs et uniquement
pour un filtre appliqué à la valeur la plus faible (moins de 30% dans
tous les cas si l'index est couvrant, mais plutôt 10% en général).

oui par contre il est vrai que mon index a une utilité limitée, mais...
perceptible.
suffisament pour le laisser en tout cas.

Lisez les articles que j'ai écrit à ce sujet. En particulier :
http://sqlpro.developpez.com/cours/quoi-indexer/
http://sqlpro.developpez.com/optimisation/indexation/ComparaisonRequetesIndexation2.pdf

salut.
J'ai lu votre cours.
j'ai trouvé ca très intéressant

Du coup j'ai tout lu :)
Et c'est la fin qui a attriré mon attention.
Existe t-il un script equivalent dans postgres qui permettrai de savoir
quel indexes créer ???

Etienne

Le 25/09/2010 12:06, Bruno Baguette a écrit :

Le 25/09/10 11:33, SQLpro a écrit :

1) un WHERE contenant une fonction ne peut pas utiliser un index car
le prédicat de jointure n'est pas "sargeable".

Dans le cas de PostgreSQL, à partir du moment ou une fonction est
marquée comme étant immutable, elle peut parfaitement être indexée.

Et PostgreSQL ne se privera pas d'utiliser cet index s'il juge utile de
l'utiliser.

Pour un bit, c'est en pratique extrémement rare !

Une fonction immutable est une fonction dont le résultat ne dépend
strictement que de ses arguments et en aucun cas de valeurs provenant
d'autres tables ou basée selon l'heure.

En français on appelle cela le déterminisme...

cf. <http://www.postgresql.org/docs/9/static/sql-crea/functteindex.html>

HTH,

A +

--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************