Logo GNT
Actualités Tests & Guides Bons Plans
Twitter iPhone pliant OnePlus 11 PS5 Disney+ Orange Livebox Windows 11
Entraide Applications Applications SGBD [POSTGRESQL] Bitfield et Index

[POSTGRESQL] Bitfield et Index

17 réponses
Avatar
Etienne
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 ?

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 ??? :)
Signaler un problème avec ce contenu

7 réponses

Supprimer
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire

Veuillez sélectionner un problème

Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
1 2
Avatar
SQLpro
Le 25/09/2010 15:52, WebShaker a écrit :
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 ???



Hélas non... Enfin pas à ma connaissance ! PostGreSQL n'est pas aussi
complet que Oracle ou SQL Server !

A +


Etienne




--
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 *************************
Avatar
helios
WebShaker a écrit :
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.



chut arretez de l'instruire il croit encore qu'il y a moins de 65536
jours depuis Jesus Christ à nos jours



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.




remarque si on devait enlever ceux qui ont une utilité limité notre
"amis" n'existerait pas



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


oui son "cours" est un bon livre de préhistoire
Avatar
Sebastien Lardiere
On 25/09/2010 18:08, SQLpro wrote:
Existe t-il un script equivalent dans postgres qui permettrai de savoir
quel indexes créer ???



Hélas non... Enfin pas à ma connaissance ! PostGreSQL n'est pas aussi
complet que Oracle ou SQL Server !



N'affirmez pas si vite sans connaître. Il est bien évidemment possible
de s'appuyer sur les statistiques du catalogue système de PostgreSQL
pour comprendre quels index sont manquants et devraient être créé.

Mais par expérience, je ne suis pas du tout d'accord avec l'idée
d'automatiser la création d'index. Je préfère partir des besoins et des
requêtes que les développeurs utilisent pour trouver les bons index.

Mais ça dépend de plusieurs facteurs : la façon dont est utilisée la
base, les temps de réponse attendus, le temps qu'on peut y passer ; je
pars du principe qu'un index coute cher en traitement, et n'est pas
toujours le plus appropriés, chaque création d'index est donc évalué.

--
Sébastien
Avatar
helios
Sebastien Lardiere a écrit :
On 25/09/2010 18:08, SQLpro wrote:

Existe t-il un script equivalent dans postgres qui permettrai de savoir
quel indexes créer ???



Hélas non... Enfin pas à ma connaissance ! PostGreSQL n'est pas aussi
complet que Oracle ou SQL Server !




N'affirmez pas si vite sans connaître. Il est bien évidemment possible
de s'appuyer sur les statistiques du catalogue système de PostgreSQL
pour comprendre quels index sont manquants et devraient être créé.

Mais par expérience, je ne suis pas du tout d'accord avec l'idée
d'automatiser la création d'index. Je préfère partir des besoins et des
requêtes que les développeurs utilisent pour trouver les bons index.

Mais ça dépend de plusieurs facteurs : la façon dont est utilisée la
base, les temps de réponse attendus, le temps qu'on peut y passer ; je
pars du principe qu'un index coute cher en traitement, et n'est pas
toujours le plus appropriés, chaque création d'index est donc évalué.




chut arretez de l'instruire il croit encore qu'il y a moins de 65536
jours depuis Jesus Christ à nos jours
Avatar
SQLpro
On 27 sep, 13:46, Sebastien Lardiere
wrote:
On 25/09/2010 18:08, SQLpro wrote:

>> Existe t-il un script equivalent dans postgres qui permettrai de savoi r
>> quel indexes créer ???

> Hélas non... Enfin pas à ma connaissance ! PostGreSQL n'est pas aus si
> complet que Oracle ou SQL Server !

N'affirmez pas si vite sans connaître. Il est bien évidemment possibl e
de s'appuyer sur les statistiques du catalogue système de PostgreSQL
pour comprendre quels index sont manquants et devraient être créé.



Certes il est possible de savoir quel est l'utilisation des index :

"25.2. The Statistics Collector

PostgreSQL's statistics collector is a subsystem that supports
collection and reporting of information about server activity.
Presently, the collector can count accesses to tables and indexes in
both disk-block and individual-row term"
Extrait de : http://www.postgresql.org/docs/8.2/interactive/monitoring-sta ts.html

Mais actuellement PostGreSQL ne propose aucun moyen de savoir quels
sont les index manquants que le moteur aurait aimer avoir pour
accélérer l'exécution des requêtes, ce que SQL Server permet par la
simple vue système sys.dm_db_missing_index_details


Mais par expérience, je ne suis pas du tout d'accord avec l'idée
d'automatiser la création d'index. Je préfère partir des besoins et des
requêtes que les développeurs utilisent pour trouver les bons index.



Nous sommes d'accord, mais c'est un début. Cependant j'ai constaté en
pratique que le fait d'avoir trop d'index n'était pas si pénalisant
que cela. Dans une base audité, les exploitant avaient laissé faire le
tuning automatique ce qui avait conduit à créer parfois plus de 100
index pour certaines tables.
Les mise à jour étaient plus lentes (mais pas de beaucoup, car toute
mise à jour a besoin préalablement d'un index pour positionner
l'insertion, l'update ou le delete) mais presque tous les SELECT
étaient d'une efficacité redoutable. Bref, l'un compensait l'autre et
la RAM était optimisée (un index est beaucoup plus fin en RAM qu'une
table !).
Seul inconvénient : la taille de la base constituée à 80% d'index
contre 20% de données.
Après rectification et passage à un relatif 70/30, les performances
globales étaient identiques !


Mais ça dépend de plusieurs facteurs : la façon dont est utilisée la
base, les temps de réponse attendus, le temps qu'on peut y passer ; je
pars du principe qu'un index coute cher en traitement, et n'est pas
toujours le plus appropriés, chaque création d'index est donc évalu é.



Grosso modo chaque entrée d'index coute entre 10 et 30% le cout d'une
ligne en terme de ressources (volume, cout de traitement...).

A +


--
Sébastien
Avatar
Sebastien Lardiere
On 10/05/2010 02:28 PM, SQLpro wrote:
Seul inconvénient : la taille de la base constituée à 80% d'index
contre 20% de données.
Après rectification et passage à un relatif 70/30, les performances
globales étaient identiques !




C'est un inconvénient de taille, tout de même, surtout lorsque, et c'est
mon cas, les volumes évoluent vite.

En travaillant les requetes avec les developpeurs, J'ai remarqué qu'il
etait assez simple de tomber sur un jeu limité d'index, en réécrivant
correctement la requete.

En matière de base de données, si tous les chemins menent à Rome, il est
quand même préférable de voir si on peut pas prendre le même chemin.

--
Sébastien
Avatar
SQLpro
Le 08/10/2010 14:32, Sebastien Lardiere a écrit :
On 10/05/2010 02:28 PM, SQLpro wrote:
Seul inconvénient : la taille de la base constituée à 80% d'index
contre 20% de données.
Après rectification et passage à un relatif 70/30, les performances
globales étaient identiques !




C'est un inconvénient de taille, tout de même, surtout lorsque, et c'est
mon cas, les volumes évoluent vite.

En travaillant les requetes avec les developpeurs, J'ai remarqué qu'il
etait assez simple de tomber sur un jeu limité d'index, en réécrivant
correctement la requete.



deux choses :

- la récriture des requêtes pour "sargeablilité"

c'est à dire le fait de permettre au prédicat de devenir cherchable.
Exemple : WHERE MaColonne LIKE '%toto' => créer un index
REVERSE(MaColonne) ou bien créer une colonne calculée REVERSE(maColonne)
et mettre un index dessus et faire LIKE 'otot%'

- la synthétisation de certains index en un seul

Exemple : X2 (A, B)
X3 (A) INCLUDE (B, E)
X4 (A, B, C)
X4 (A, C) INCLUDE (E)
peut se résumer à :
X1 (A, B, C) INCLUDE (D, E)


En matière de base de données, si tous les chemins menent à Rome, il est
quand même préférable de voir si on peut pas prendre le même chemin.



Oui, mais le volume des index n'est pas le plus important, car très peu
de pages sont montées en RAM pour les besoins de recherches. Donc en
définitive, la seule pénalisation sont les écritures....

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 *************************
1 2