L'amélioration de la vitesse de requête: SELECT simple dans les grandes postgres table

Je vais avoir des ennuis au sujet de la vitesse dans une requête SELECT sur une base de données Postgres.

J'ai un tableau avec deux colonnes de type entier clés: (int1,int2)
Ce tableau a environ 70 millions de lignes.

J'ai besoin de faire deux sortes de simples requêtes SELECT dans cet environnement:

SELECT * FROM table WHERE int1=X;
SELECT * FROM table WHERE int2=X;

Ces deux sélectionne retourne près de 10 000 lignes de chacun de ces 70 millions de dollars. Pour que cela fonctionne aussi vite que possible, je pensais que sur l'aide de deux HASH index, un pour chaque colonne. Malheureusement, les résultats ne sont pas bons:

                                                               QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on lec_sim  (cost=232.21..25054.38 rows=6565 width=36) (actual time=14.759..23339.545 rows=7871 loops=1)
   Recheck Cond: (lec2_id = 11782)
   ->  Bitmap Index Scan on lec_sim_lec2_hash_ind  (cost=0.00..230.56 rows=6565 width=0) (actual time=13.495..13.495 rows=7871 loops=1)
         Index Cond: (lec2_id = 11782)
 Total runtime: 23342.534 ms
(5 rows)

C'est un explain analyze exemple de l'une de ces requêtes. Il est à prendre autour de 23 secondes. Mes attentes sont pour obtenir cette information en moins d'une seconde.

Ce sont certains paramètres de la postgres db config:

work_mem = 128MB
shared_buffers = 2GB
maintenance_work_mem = 512MB
fsync = off
synchronous_commit = off
effective_cache_size = 4GB

Toute aide, des commentaires ou de la pensée, ce serait vraiment apprécié.

Vous en remercie d'avance.

  • Dans le cadre de votre temps total de combien cela est d'envoyer les données en arrière pour vous? Êtes-vous d'exécuter la requête sur la même machine que la base de données ou allez-vous sur le fil?
  • le temps indiqué dans l'expliquer de sortie est le temps pour la préparation de la requête sur le serveur (sans côté client allers-retours)
  • Hash index ne sont pas très efficaces dans PostgreSQL. Avez-vous essayé ordinaire B-Arbre d'index? Avez-vous un index pour chaque colonne, ou un indice combiné sur deux? Pour lequel des deux états est la publication du plan d'exécution?
  • JustBob, a_horse_with_no_name répondu mieux que je le ferais. Je crois que pour moi, pour recevoir les données sont assez instantanée puisqu'il y a 2 flotteurs seulement en tant que données.
  • a_horse_with_no_name, au début, ils sont le B-Arbre d'index, et, comme ils étaient en marche lente, j'ai changé d'index de hachage. Il y a 3 indices de réalité: la principale (int1, int2) comme B-Arbre, puis un index de hachage pour chaque colonne: hash (int1), hash (int2). Merci.
  • L'index de recherche ici a été très rapide tout le temps a été consacré à la récupération des lignes réelles. 23 secondes / 7871 rows = 2.9 millisecondes par ligne, ce qui est raisonnable pour la récupération des données qui sont dispersés à travers le sous-système de disque. Cherche sont lents; vous pouvez a) s'adapter à votre jeu de données dans la mémoire RAM, b) acheter des disques Ssd, ou c) d'organiser vos données à l'avance pour minimiser les cherche.
  • Merci beaucoup, je ne savais pas que c'était le moment d'être passé sur la recherche de données au lieu de chercher à l'index. Donc, oubliez sur les indices liés au questionnement, j'aimerais voir comment ai-je pu organiser ces données pour prendre plus rapidement de la HD. Peut-être que je pourrais commander les lignes (int1) dans une table, et une copie de la table de l'ordre de (int2), et d'effectuer la requête SELECT sur ces deux tableaux en fonction de l'index de la clé, je suis à la recherche d'. Toutes les meilleures idées ici? Serait-il travailler plus vite? Je vous remercie beaucoup.
  • Deux tables est une option, surtout si vous CLUSTER deux d'entre eux. Cependant, PostgreSQL 9.2 ajoute une fonctionnalité appelée index ne scanne qui est particulièrement utile, ici, de faire un btree indice au-dessus de toutes les colonnes d'intérêt (qui PostgreSQL maintient automatiquement dans l'ordre), et la requête peut (probablement) être résolues en utilisant uniquement l'index, sans aucun supplément de cherche.

InformationsquelleAutor alexdemartos | 2012-11-05
  1. 34

    L'extraction de mes commentaires dans une réponse: l'index de recherche ici a été très rapide tout le temps a été consacré à la récupération des lignes réelles. 23 secondes /7871 rows = 2.9 millisecondes par ligne, ce qui est raisonnable pour la récupération des données qui sont dispersés à travers le sous-système de disque. Cherche sont lents; vous pouvez a) s'adapter à votre jeu de données dans la mémoire RAM, b) acheter des disques Ssd, ou c) d'organiser vos données à l'avance pour minimiser les cherche.

    PostgreSQL 9.2 a une fonctionnalité appelée index-analyse uniquement qui permet (normalement) de répondre aux questions sans avoir accès à la table. Vous pouvez combiner cela avec la btree propriété index automatiquement le maintien de l'ordre à faire cette requête rapide. Vous mentionnez int1, int2, et deux flotteurs:

    CREATE INDEX sometable_int1_floats_key ON sometable (int1, float1, float2);
CREATE INDEX sometable_int2_floats_key ON sometable (int2, float1, float2);

SELECT float1,float2 FROM sometable WHERE int1=<value>; -- uses int1 index
SELECT float1,float2 FROM sometable WHERE int2=<value>; -- uses int2 index

    Notez également que ce n'est pas comme par magie effacer le disque cherche, il se déplace de la requête pour insérer l'heure. Il coûte aussi vous l'espace de stockage, puisque vous êtes de dupliquer les données. Encore, c'est sans doute le compromis que vous voulez.

    • Merci beaucoup @willglynn. Je pense que c'était ce que je cherchais. Je vais lui donner une chance demain et je post comment cela fonctionne.
    • Je suis complètement époustouflé par la façon étonnante ce est et comment il a influencé de façon spectaculaire les performances. La création d'un simple indice a chuté de requêtes de base de données à partir de ~8s à ~20ms sur une base de données de production qui contenant environ 20mil lignes.
    InformationsquelleAutor willglynn
  2. 21

    Merci willglyn. Comme vous l'avez remarqué, le problème a été à la recherche par le biais de la HD et pas à la recherche pour les index. Vous avez proposé de nombreuses solutions, comme le chargement de la base de données dans la mémoire RAM ou acheter un Ssd HD. Mais d'oublier ces deux-là, qui concernent la gestion des choses à l'extérieur de la base de données elle-même, vous avez proposé deux idées:

    1. Réorganiser les données afin de réduire la recherche des données.
    2. Utilisation de PostgreSQL 9.2 fonction "index-analyse uniquement"

    Depuis que je suis sous un Serveur PostgreSQL 9.1, j'ai décidé de prendre l'option "1".

    J'ai fait une copie de la table. Alors maintenant, j'ai la même table avec deux fois les mêmes données. J'ai créé un index pour chacune, la première étant indexé par (int1) et le second par le (int2). Ensuite, j'ai cluster eux deux (table de CLUSTER à l'AIDE de ind_intX) par son index.

    Je poste maintenant un explain analyze de la même requête, en fait dans l'un de ces clusters tables:

     PLAN DE REQUÊTE 
--------------------------------------------------------------------------------------------------------------------------------------------- 
Analyse d'Index à l'aide de lec_sim_lec2id_ind sur lec_sim_lec2id (cost=0.00..21626.82 rows=6604 largeur=36) (actual time=0.051..1.500 rows=8119 loops=1) 
Index Cond: (lec2_id = 12300) Total runtime: 
1.822 ms (3 lignes)

    Maintenant la recherche est très rapide. Je suis allé vers le bas à partir de 23 secondes pour ~2 millisecondes, ce qui est une amélioration remarquable. Je pense que ce problème est résolu pour moi, j'espère que ce pourrait également être utile pour d'autres personnes rencontrant le même problème.

    Merci beaucoup willglynn.

    • Si vous avez un statique de l'ensemble de données, vous avez terminé. Si non, vous aurez envie de maintenir le tri des tables à l'aide de déclencheurs (alors qu'il y a une seule source de vérité), et vous aurez besoin de relire régulièrementCLUSTER à maintenir sur le disque afin que vos modifications de données.
    InformationsquelleAutor alexdemartos
  3. 3

    J'ai eu une super requêtes lentes où simple à de nombreuses jointures (en PG v9.1) ont été effectuées entre une table qui était de 33 millions de lignes à une table d'enfant qui était de 2,4 milliards de lignes dans la taille. J'ai effectué un CLUSTER sur l'index de clés étrangères de la table enfant, mais a constaté que cela ne résout pas mon problème avec les délais d'attente de requête, de même le plus simple des requêtes. L'exécution d'ANALYSER aussi la ne résout pas le problème.

    Ce qui fait une énorme différence a été l'exécution d'un manuel de VIDE sur les deux à la table parent et de l'enfant tableau. Même en tant que parent de table a été l'achèvement de son processus de mise sous VIDE, je suis passé de 10 minutes de délais d'attente pour les résultats à venir de retour dans une seconde.

    Ce que je suis loin de prendre ce n'est que VIDE régulier des opérations sont toujours critique, même pour v9.1. La raison que j'ai fait, c'est que j'ai remarqué autovacuum ne l'avais pas lancé sur l'une des tables pour au moins deux semaines, et beaucoup de upserts et inserts ont eu lieu depuis lors. Il se peut que j'ai besoin d'améliorer l'autovacuum déclencheur pour prendre soin de ce problème à l'avenir, mais ce que je peux dire, c'est qu'un 640GO table avec un couple de milliards de lignes exécute bien si tout est nettoyé. Je n'ai pas encore eu de partition de la table pour obtenir de bonnes performances.

    • Vous devez enquêter sur pourquoi autovacuum n'était pas en cours d'exécution. Vous avez probablement quelques séances en "inactif dans la transaction" de mode. Assurez-vous de mettre fin à des opérations correctement dans votre code. Vous devriez probablement faire de votre autovacuum réglages plus agressifs.
    InformationsquelleAutor Robert Casey
  4. 0

    Pour une très simple et efficace, un liner, si vous avez rapide à l'état solide de stockage sur votre postgres machine, essayez de définir:

    random_page_cost=1.0

    Dans votre dans votre postgresql.conf.

    La valeur par défaut est random_page_cost=4.0 et c'est optimisée pour le stockage à haute temps de recherche comme de vieux disques en rotation. Cela modifie le calcul des coûts de recherche et s'appuie moins sur votre mémoire (ce qui pourrait éventuellement se passer de swap de toute façon)

    Ce paramètre amélioré ma requête de filtrage à partir de 8 secondes à 2 secondes sur une longue table avec un couple de millions de disques.

    L'autre amélioration majeure est venu de faire des index avec tous les booleen colonnes de ma table. Cette réduction de 2 seconde requête pour environ 1 seconde. Vérifiez @willglynn de réponse pour cela.

    Espérons que cette aide!

    InformationsquelleAutor Nick Woodhams