Suite

Performances Postgres avec les recherches Radius

Performances Postgres avec les recherches Radius


Importer une application de mysql vers postgres et avoir plusieurs tables avec plus de 500 000 lignes dans chacun des points géographiques (lat, lon). Dans mysql, j'ai utilisé une requête de cadre de délimitation avec haversine pour sélectionner des lignes à une distance donnée d'un point central. Je comprends que je peux utiliser le module st_dwithin de postgis ou le module earthdistance (ou je pourrais probablement aussi porter ma requête mysql). Je me demande simplement si quelqu'un a une recommandation sur la méthode qui serait la meilleure en termes de performances avec ces grands ensembles de données.


Utilisez st_dwithin. Il utilisera l'index spatial et fera le travail très rapidement s'il ne s'agit que de points. 500 000 points, ce n'est pas tant que ça.


Améliorer les performances de COUNT/GROUP-BY dans une grande table PostgresSQL ?

J'exécute PostgresSQL 9.2 et j'ai une relation à 12 colonnes avec environ 6 700 000 lignes. Il contient des nœuds dans un espace 3D, chacun référençant un utilisateur (qui l'a créé). Pour demander quel utilisateur a créé combien de nœuds, je fais ce qui suit (ajout d'expliquer l'analyse pour plus d'informations):

Comme vous pouvez le voir, cela prend environ 1,7 seconde. Ce n'est pas trop grave compte tenu de la quantité de données, mais je me demande si cela peut être amélioré. J'ai essayé d'ajouter un index BTree sur la colonne utilisateur, mais cela n'a aidé en aucune façon.

Avez-vous des suggestions alternatives?

Par souci d'exhaustivité, voici la définition complète de la table avec tous ses indices (sans contraintes de clé étrangère, références et déclencheurs) :

Éditer: Voici le résultat, lorsque j'utilise la requête (et l'index) proposée par @ypercube (la requête prend environ 5,3 secondes sans EXPLAIN ANALYZE ):

Edit 2 : Voici le résultat, lorsque j'utilise un index sur project_id, user_id (mais pas encore d'optimisation de schéma) comme suggéré par @erwin-brandstetter (la requête s'exécute en 1,5 seconde à la même vitesse que ma requête d'origine):


Ne rangez pas lat et long sur une table comme ça. Utilisez plutôt un type de géométrie ou de géographie PostGIS.

Maintenant, lorsque vous devez l'interroger, vous pouvez utiliser KNN ( <-> ) qui le fera en fait sur un index.

Dans votre requête, vous avez explicitement HAVING distance < 5 . Vous pouvez également le faire sur l'index.

Cela garantit que rien n'est renvoyé si tous les points se trouvent en dehors de distance_in_meters .

De plus, x et y sont des nombres décimaux ST_MakePoint(46.06, 14.505)

Vous pouvez utiliser les extensions cube et earthdistance de PostgreSQL.

Disons que votre position actuelle est 35.697933, 139.707318 . Ensuite, votre requête ressemblera à ceci :

Veuillez noter que la distance est en miles (par défaut).

Voir cet aperçu, vous découvrirez comment déclarer un DOMAIN sur le type de point et comment remplacer l'opérateur de distance pour renvoyer la distance orthodromique.

Déclarez un type latlong hérité du point :

La distance orthodromique en kilomètres (distance sur une sphère avec le rayon de la terre) :

Remplacez l'opérateur de distance <-> à l'aide de cette fonction lorsqu'il est utilisé avec des latlongs :

Maintenant dans vos requêtes SQL, pour trouver les entités les plus proches :

Vous voudrez probablement stocker la position lat et long dans le même champ en utilisant le type latlong.


2 réponses 2

Async & LISTEN/NOTIFY est la bonne méthode !

Vous pouvez ajouter des déclencheurs sur UPDATE/INSERT et exécuter votre requête dans le corps du déclencheur, enregistrer Nombre de rangées dans une table simple et si la valeur a changé, appelez NOTIFY. Si vous avez besoin de plusieurs combinaisons de paramètres dans la requête, vous pouvez créer/détruire des déclencheurs à partir de votre programme.

Vous devez configurer les bons déclencheurs qui appelleront NOTIFY pour tous les clients qui utilisent LISTEN sur le même canal.

Il est difficile de dire comment vous implémentez exactement votre logique NOTIFY dans les déclencheurs, car cela dépend des éléments suivants :

  • À combien de clients le message est-il destiné ?
  • Quel est le poids/la taille de la requête en cours d'évaluation ?
  • Les déclencheurs peuvent-ils connaître la logique de la requête pour l'évaluer ?

Sur la base des réponses, vous pouvez envisager différentes approches, qui incluent, mais sans s'y limiter, les options suivantes et leurs combinaisons :

  • exécuter la requête/vue lorsque le résultat ne peut pas être évalué et mettre en cache le résultat
  • fournir une notification intelligente, si le résultat de la requête peut être évalué
  • utiliser la charge utile pour transmettre les détails de la mise à jour aux auditeurs
  • planifier des réexécutions de requête/vue pour une exécution tardive, si elle est lourde
  • faire la notification entière en tant que travail séparé

Certains scénarios peuvent devenir assez complexes. Par exemple, vous pouvez avoir un client maître qui peut effectuer la modification et plusieurs esclaves qui doivent être notifiés. Dans ce cas, le maître exécute la requête, vérifie si le résultat a changé, puis appelle une fonction sur le serveur PostgreSQL pour déclencher des notifications sur tous les esclaves.

Encore une fois, de nombreuses variantes sont possibles, en fonction des exigences spécifiques de la tâche à accomplir. Dans votre cas, vous ne fournissez pas suffisamment de détails pour proposer un chemin spécifique, mais les directives générales ci-dessus devraient vous aider.


SequelizeJS et SIG

Le support SIG pour SequelizeJS est, d'une part, pris en charge depuis 2014. D'un autre côté, malheureusement, il n'est implémenté que pour PostgreSQL et PostGIS. Il y a une discussion en cours pour mettre en œuvre un support plus large pour les SIG. Un autre inconvénient est que seule la géométrie est actuellement prise en charge. Si vous avez besoin d'un support géographique, SequelizeJS ne peut pas vous aider aujourd'hui car il n'est pas du tout implémenté en tant que type de données. Néanmoins, pour mon petit échantillon, il est tout à fait acceptable d'utiliser des données géométriques, même lors d'une recherche basée sur l'emplacement, car le rayon sera suffisamment petit pour obtenir de bons résultats. En fait, nous pouvons utiliser SequelizeJS à la fois pour PostgreSQL et MSSQL ! Les paragraphes suivants expliquent ce que vous devez faire pour y parvenir.

Préparer SequelizeJS

Pour l'exemple de backend, j'utilise Node.js v5.4.0. Dans un premier temps, nous devons installer les dépendances nécessaires. Un simple npm que je séquelle pg fastidieux est ce dont nous avons besoin. sequelize installera SequelizeJS. pg est le pilote de base de données pour PostgreSQL et fastidieux celui pour MSSQL.

Note latérale : Il existe des pilotes MSSQL officiels de Microsoft (ici et ici), mais ils sont actuellement pour Windows uniquement.

Créer la classe de connecteur de base de données

Commençons par créer une classe Database très simple et minimaliste dans ECMAScript 2015, qui se connecte à la base de données et crée un modèle :

Décortiquons ce code – tout d'abord : Import Sequelize, afin que nous puissions l'utiliser. Ensuite, nous définissons la classe Database avec un champ public appelé models et deux fonctions publiques appelées getDialect et initialize . Le champ public contiendra notre exemple de modèle, nous pourrons donc l'utiliser plus tard. La fonction getDialect renvoie le dialecte utilisé postgres ou mssql . La fonction initialize est utilisée pour initialiser et se connecter à la base de données. À l'intérieur, nous vérifions si nous voulons nous connecter à PostgreSQL ou à MSSQL. Après la connexion, nous créons un SampleModel avec un identifiant de clé primaire auto-incrémenté et un point de type GEOMETRY(‘POINT’) . SequelizeJS prend en charge différents types de géométries, mais cela dépend du moteur de base de données sous-jacent. Avec GEOMETRY(‘POINT’) nous disons au moteur de base de données, nous voulons uniquement stocker la géométrie de type point. D'autres types valides seraient LINESTRING ou POLYGON . Ou vous pouvez omettre complètement le type pour utiliser différents types dans la même colonne. Enfin, nous stockons notre modèle dans notre champ public, il est donc accessible via this.models.SampleModel ultérieurement. Enfin, nous utilisons syncDatabase() qui appelle sequelize.sync() et renvoie une Promise . sequelize.sync() créera les tables nécessaires pour vos modèles définis dans ce cas.

*Remarque : *Toutes les méthodes SequelizeJS qui communiquent avec la base de données renverront un Promise .

Le module est exporté en tant qu'instance/singleton.

Créer l'adaptateur SampleService

Vient ensuite une classe de service qui utilisera notre base de données et notre modèle pour créer des entités et lire des données. Le service englobera les implémentations réelles des différents moteurs de base de données et fournira des méthodes d'accès qui pourraient être utilisées par une interface utilisateur ou une API Web pour accéder aux données.

Au début, nous importons deux classes : SampleServiceMSSQL et SampleServicePostgreSQL , car nous avons besoin d'approches différentes pour gérer nos données géométriques. Ensuite, nous définissons un SampleService qui a une dépendance à la base de données. Notez en bas que nous exportons la classe et non une instance. N'oubliez pas que database.initialize() renverra une promesse lorsque tout sera configuré. Nous construirons donc le service plus tard, lorsque la Promesse aura été résolue.

Dans la classe, nous vérifions quel moteur de base de données sous-jacent nous avons. Dans le cas de MSSQL, nous construisons SampleServiceMSSQL, sinon SampleServicePostgreSQL . Les deux obtiennent le modèle comme premier argument. Même raison ici : cela garantit une promesse de database.initialize () résolue.

La classe elle-même définit deux méthodes. Le premier create() créera une nouvelle entrée dans la base de données par la latitude et la longitude fournies. Pour ce faire, un objet point est créé avec un type de propriété de valeur « Point » et des coordonnées de propriété contenant un tableau avec latitude et longitude . Ce format s'appelle GeoJSON et peut être utilisé dans SequelizeJS. Ensuite, nous appelons la méthode create de l'adaptateur.

Exactement la même chose est fait avec la deuxième méthode getAround() . Le but de cette méthode sera d'obtenir tous les points dans un rayon autour de la latitude et de la longitude données.

Veuillez noter que cet exemple ne contient aucune validation d'entrée par intention en raison de la portée de cet article de blog.

Nous avons maintenant une base de données et une classe de service qui fonctionnent comme un adaptateur pour les implémentations concrètes. Construisons les implémentations pour PostgreSQL et MSSQL !

Implémenter la classe d'adaptateur SampleServicePostgreSQL

Nous commençons par construire la classe SampleServicePostgreSQL :

C'est notre adaptateur pour PostgreSQL. La mise en œuvre de la méthode create est vraiment simple. Chaque modèle SequelizeJS contient une méthode create qui insère les données du modèle dans la base de données sous-jacente. En raison de la prise en charge de PostGIS, nous pouvons simplement appeler model.create(point) et laisser SequelizeJS s'occuper d'insérer correctement nos données.

Jetons un coup d'œil à la méthode getAround. Comme mentionné ci-dessus, SequelizeJS prend en charge PostGIS. Malheureusement, c'est un support très basique. Il prend en charge l'insertion, la mise à jour et la lecture, mais aucune autre méthode comme ST_Distance_Sphere ou ST_MakePoint via une abstraction API bien définie. Mais selon ce problème Github, il est actuellement en discussion. Soit dit en passant, les méthodes mentionnées sont des normes ouvertes de l'Open Geospatial Consortium (OGC). Nous verrons ces méthodes plus tard, lors de l'implémentation de l'adaptateur MS SQL Server.

Retour à la méthode getAround. Nous déclarons d'abord notre requête paramétrée. Nous sélectionnons l'identifiant, le createdAt et calculons une distance. OK attend. Qu'est-ce qu'il se passe ici? Nous n'avons pas de propriété createdAt dans notre modèle, n'est-ce pas ? Eh bien, nous l'avons fait, mais pas explicitement. Par défaut, SequelizeJS crée automatiquement une propriété supplémentaire createdAt et updatedAt pour nous et en garde la trace. SequelizeJS ne serait pas SequelizeJS, si vous ne pouvez pas changer ce comportement.

Qu'en est-il de la distance ST_Distance_Sphere(ST_MakePoint(:latitude, :longitude), « point ») ? Nous utilisons ST_MakePoint pour créer un point à partir de nos paramètres de latitude et de longitude. Ensuite, nous utilisons le résultat comme premier paramètre pour ST_Distance_Sphere. Le deuxième paramètre « point » fait référence à notre colonne de table. Ainsi, pour chaque ligne de notre table SampleModels (SequelizeJS pluralise automatiquement les noms de table par défaut), nous calculons la distance sphérique (bien qu'il s'agisse d'un objet de géométrie plane) entre le point donné et celui de notre colonne. Soyez prudent ici et ne vous trompez pas! ST_Distance_Sphere calcule la distance avec un rayon moyen terrestre donné de 6370986 mètres. Si vous souhaitez utiliser un vrai Spheroid selon le SRID mentionné ci-dessus, vous devez utiliser ST_DistanceSpheroid.

La partie WHERE de la requête sera utilisée pour sélectionner uniquement les données qui se trouvent dans un rayon fourni représenté par le paramètre nommé maxDistance . Enfin, nous exécutons cette requête sur notre PostgreSQL en appelant model.sequelize.query . Le premier paramètre est notre requête, le second est quelques options. Comme vous l'avez peut-être remarqué, nous avons utilisé des espaces réservés nommés dans notre requête. Par conséquent, nous utilisons les objets de remplacement pour indiquer à SequelizeJS les valeurs des espaces réservés. la latitude et la longitude sont explicites. maxDistance est défini sur 10 kilomètres, nous n'obtenons donc que des points dans le rayon donné. Avec la propriété type, nous définissons le type de la requête sur une instruction SELECT.

Jusqu'ici, tout va bien, notre adaptateur PostgreSQL est terminé. Passons à l'adaptateur MSSQL !

Implémenter la classe d'adaptateur SampleServiceMSSQL

Le code de la classe SampleServiceMSSQL est le suivant :

Voyons cela, étape par étape. En raison de l'absence totale de prise en charge de la géométrie dans MSSQL, nous devons maintenant tout faire manuellement. Jetez un œil à la méthode de création. Nous commençons par définir notre requête INSERT et insérons les valeurs : point , createdAt et updatedAt . Si nous exécutons une requête brute, nous devons veiller à définir les valeurs createdAt et updatedAt. Pour la valeur du point, nous utilisons la géométrie::Point($, $, 0) . Si vous n'êtes pas familier avec les chaînes modélisées de JavaScript, cela peut vous faire un peu mal aux yeux. La syntaxe $ insère simplement la valeur dans la chaîne. geometry::Point() est l'équivalent de MSSQL au ST_MakePoint mentionné ci-dessus avec une différence. Il veut avoir un troisième paramètre, le SRID. Puisque nous ne l'utilisons pas ici, nous pouvons simplement utiliser 0.

Vous avez peut-être remarqué que nous n'utilisons pas de paramètres nommés ici. SequelizeJS reconnaît automatiquement tout ce qui est préfixé par deux points. Il essaierait donc de remplacer :Point par un paramètre nommé. Heureusement, les objets de remplacement peuvent également être un tableau et remplacent tous les points d'interrogation par les valeurs définies dans l'ordre de leur apparition. De plus, nous fournissons un modèle de propriété avec la valeur de notre modèle. Cela indique à SequelizeJS de mapper automatiquement le résultat de l'instruction INSERT à notre modèle. Enfin, nous définissons le type de requête sur INSERT .

Passons maintenant à notre dernière méthode getAround . C'est fondamentalement le même que celui de l'adaptateur PostgreSQL, mais comme nous n'utilisons pas de SRID pour le calcul, MS SQL Server calculera sur un plan. C'est pourquoi nous multiplions le résultat par le rayon moyen de la Terre pour obtenir la distance en mètres. Remarque : Ceci est légèrement moins précis que la version PostgreSQL du calcul avec ST_Distance_Sphere .

Wow. Respirez profondément, nous avons terminé les cours de base de données et de service. La dernière chose à faire est un peu d'orchestration pour tout essayer !


1,609344 étant le facteur de conversion des miles en kilomètres. Source : Wikipédia. En supposant le mètre comme unité ! La documentation:

Pour les géométries : La distance est spécifiée en unités définies par le système de référence spatiale des géométries. Pour que cette fonction ait un sens, les géométries source doivent toutes les deux être de la même projection de coordonnées, avoir le même SRID.

Pour la géographie les unités sont en mètres

Je pense que votre SRID 4326 utilise des degrés, ne pas mètres. Instructions dans cette réponse connexe :

Vous avez besoin d'un indice sur geo_point pour rendre cela rapide (utilisé par ST_DWithin() automatiquement):

Un index GiST peut également être utilisé pour identifier la plus proche voisins (en combinaison avec un nombre maximum - LIMIT ).


15.2. Fonctions indexées spatialement¶

Seul un sous-ensemble de fonctions utilisera automatiquement un index spatial, s'il est disponible.

Les quatre premiers sont les plus couramment utilisés dans les requêtes, et ST_DWithin est très important pour effectuer des requêtes de style "à distance" ou "dans un rayon" tout en améliorant les performances de l'index.

Afin d'ajouter une accélération d'index à d'autres fonctions qui ne figurent pas dans cette liste (le plus souvent, ST_Relate), ajoutez une clause d'index uniquement comme décrit ci-dessous.


Le postgis évolue de la même manière que les échelles postgresql. L'index postgis fonctionnera à peu près de la même manière que les autres relations, vous pouvez le vérifier ici.

Si vous jetez un œil au lien, il explique qu'il indexe à l'aide d'un algorithme géométrique qui est effectué à chaque opération d'insertion, il peut donc ne pas être assez réactif dans les applications en temps réel.

Alors qu'elasticsearch a une indexation en temps réel, basée sur l'index Lucene. La recherche élastique convient généralement mieux aux applications lourdes en temps réel, puis Postgresql.

Postgresql a un énorme avantage, c'est sa simplicité. Il est beaucoup plus facile d'implémenter le test et de maintenir une telle fonctionnalité en utilisant Postgresql. Par exemple, je préfère créer rapidement un prototype basé sur Postgresql, et s'il commence à mal fonctionner à cause d'une grande quantité d'écritures, etc. Je passe à l'implémentation d'elasticsearch.


SÉLECTIONNER DISTINCT

Le problème suivant est l'utilisation de SELECT DISTINCT , qui doit être évitée, car la base de données aurait besoin de travailler pour dédupliquer les résultats. Une requête bien conçue sur une base de données correctement normalisée devrait simplement produire les bons résultats sans nécessiter une telle déduplication. En d'autres termes, si vous avez besoin de SELECT DISTINCT , alors soit

  • Votre schéma de base de données est incorrectement normalisé
  • Votre base de données contient des données indésirables, qui n'ont pas été rejetées par les contraintes de table
  • Votre requête est mal conçue de sorte que les JOIN produisent des lignes en double

Je crois que la dernière explication s'applique ici. Vous effectuez beaucoup de LEFT OUTER JOIN s pour les recherches de texte, mais vous n'êtes pas intéressé par les colonnes des tables jointes. La suppression des colonnes des tables jointes produit des lignes parasites.

Au lieu de LEFT OUTER JOIN , vous devez utiliser des clauses WHERE EXISTS avec des sous-requêtes corrélées.