Vous n'êtes pas identifié(e).
- Contributions : Récentes | Sans réponse
Pages : 1
#1 Re : Général » ORDER BY hors du scope de DISTINCT ON » 13/04/2012 13:06:47
MERCI !
en effet ma première pensée venait à utiliser
SELECT DISTINCT (location) time FROM weather_reports ORDER BY time;
ce qui me semblait le plus logique, mais oublliant la virgule entre apres le (location) : j'ai eu un message d'erreur qui m'a laisser penser que la logique était mauvaise, d'où les complications qui on suivit.
dans mon exemple, location est unique, et les doublons provenant de jointures.
supposons que location n'est pas unique, nous devrions utiliser un DISTINCT ON (location) location, time ... ORDER BY time pour avoir les 'locations' les plus recents (comem expliqué dans la doc)
mais comment les classer ensuite ? une requete imbriquee semble indispensable, pour reprendre votre seconde proposition :
WITH req AS (SELECT DISTINCT ON (location) location, time FROM weather_reports ORDER BY location, time DESC) SELECT location FROM req ORDER BY time;
n'est-ce pas ?
par ailleurs pour revenir aux questions de performances, et à votre post précédent, voyez-vous des raisons à des performances différentes entre vos deux propositions ?
#2 Re : Général » ORDER BY hors du scope de DISTINCT ON » 13/04/2012 10:52:55
en fait ce que je veux faire est du style :
SELECT location FROM (
SELECT location, time FROM weather_reports GROUP BY location)
ORDER BY time;
mais question est : il y a-t-il une autre manière de faire plus "propre" (j'ai toujours l'impression que les requetes imbriquées sont un peu bricolage, mais je ne suis pas suffisement expert pour comprendre dans quelle mesure ce n'est qu'une impression.
#3 Général » ORDER BY hors du scope de DISTINCT ON » 12/04/2012 17:12:53
- ilrico
- Réponses : 4
Bonjour,
Dans la doc PostgreSQL, il est indiqué que :
DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example:
SELECT DISTINCT ON (location) location, time, report
FROM weather_reports
ORDER BY location, time DESC;
retrieves the most recent weather report for each location. But if we had not used ORDER BY to force descending order of time values for each location, we'd have gotten a report from an unpredictable time for each location.
The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). The ORDER BY clause will normally contain additional expression(s) that determine the desired precedence of rows within each DISTINCT ON group.
Or ce que je cherche à faire est d'avoir les résultats 'location' uniques (elles sont déjà uniques, le DISTINCT ON vient du fait que le résultat venant d'un select multi-tables, j'ai les doublons), mais le tout ordonné par 'time' : ie avoir 'time' comme premier filtre de classement (en absolu, pas pour chaque 'location') : actuellement, mon résultat est ordonné par 'location'.
J'espère être clair dans ma question, sinon je le reformulerai !
Merci !
#4 Optimisation » multi index et between » 11/03/2012 17:08:21
- ilrico
- Réponses : 3
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 !
#5 Re : Optimisation » count() ou champ dans table » 01/02/2012 00:10:12
ok merci pour cette réponse rapide (et le conseil final plein de sagesse, que je vais m'efforcer à suivre)
#6 Optimisation » count() ou champ dans table » 31/01/2012 22:54:58
- ilrico
- Réponses : 2
Bonjour,
Supposons que j'ai une table1 avec des objets sur chaque ligne, et une table2 avec une categorie sur chaque ligne (name, id)
Et une tableC de correspondance (objet_id, categorie_id)
Je cherche a afficher le nombre d'objet dans chaque catégorie.
Qu'est ce qui est le plus efficace sur long terme (a noter qu'il peut y avoir plusieurs millions de lignes dans la table de correspondance) ?
- faire une jointure table2 et tableC avec un SELECT name, count(tableC.object_id) FROM table2, tableC where categorie_id = table2.id ?
- ou ajouter une colonne num_objet integer dans la table2 et incrémenter (resp. soustraire) cette colonne à chaque fois qu'on affecte un objet à une catégorie (resp. qu'on détruit un objet) ?
plus généralement ma question porte sur l'interet de stocker une information type count() dans le champ d'une table...
merci d'avance !
#8 Général » DELETE avec ORDER BY et LIMIT... » 20/06/2011 00:28:29
- ilrico
- Réponses : 3
bonsoir,
j'ai une table qui se peuple d'un nombre élevé d'objets identiques. Leur unicité est importante car ils sont des sortes de jetons qui les utilisateurs de l'application capturent un à un.
pour les détruire j'aimerais avoir une requete du style (detruire les 50 plus récents par ex:)
DELETE FROM table WHERE creator_id = 5 ORDER BY time_creation DESC LIMIT 50;
ça ne marche pas ! (d'après mes recherches, sous MySQL cette syntaxe intuitive mais non conforme SQL fonctionne... je vais pas me faire des amis si je continue comme ça...)
ci-joint 2 liens que j'ai rapidement trouvé, qui me confirment que cela ne fonctionne pas :
http://archives.postgresql.org/pgsql-pa … g00255.php
http://drupal.org/node/510892
mais ils ne donnent pas de solution.
celle qui me vient en tête est de faire, un truc du genre (exemple en PHP) :
$result = pg_query("SELECT id FROM table WHERE creator_id = 50 ORDER BY time_creation DESC LIMIT 50;")
while($row = pg_fetch_row($result)) { pg_query("DELETE FROM table WHERE id=$row[0];) }
l'avantage ça prend 2 lignes, l'inconvenient, ça fait 51 requetes pour detruire 50 lignes...
...j'imagine qu'il y a plus propre, comment feriez vous par exemple ?
merci
#9 Général » RENAME sur une colonne de type SERIAL ou UNIQUE » 19/06/2011 12:53:34
- ilrico
- Réponses : 1
bonjour, j'ai remarqué que fait un RENAME sur une colonne qui a une séquence rattachée (cas des colonnes de type SERIAL ou avec contrainte UNIQUE plus généralement) ne modifie par le nom de la séquence.
est-ce que cela peut poser un problème ultérieurement ? (dans ce cas, il y a-t-il une astuce ?)
#10 Re : Optimisation » performance entre varchar(2) ou integer sur clause WHERE » 19/06/2011 12:50:04
Ah justement ça m'évite de créer un nouveau thread ! La question que je voulais poser ce matin est la suivante :
"La place des colonnes est-elle importante sous PostgreSQL ?", car en effet il me semble que vous certaines db il est fortement recommandé de placer les colonnes de type de taille fixe au début, et les type variables (text, varchar) à la fin.
Visiblement, d'après ce que gleu indique, PostgreSQL ne regroupe pas automatiquement les types fixes en debut de ligne, donc j'en deduis que la place des colonnes suivant leur type influe directement sur les performances.
Si oui, l'impact est-il vraiment mesurable sur une table qui fait < 1 Go ?
#11 Re : Optimisation » performance entre varchar(2) ou integer sur clause WHERE » 05/06/2011 20:16:06
ok, merci à tous les deux pour votre réponse.
#12 Optimisation » performance entre varchar(2) ou integer sur clause WHERE » 05/06/2011 16:57:25
- ilrico
- Réponses : 8
Bonjour, sur une table qui contiendra quelques millions de lignes, je lancerai des requetes SELECT avec (...) WHERE type_obj =
ma question : si je défini type_obj comme integer (de 0 à 4) cela aura-t-il un impact significatif en performance par rapport à un type_obj en varchar(2) (AA, BB, CC, DD, EE)
meme question entre integer et char(1)
La config matérielle est plutôt light (hébergement virtualisé de base)
Merci !
#13 Re : Général » Meilleure methode pour mettre a jour une table de correspondance » 08/05/2011 22:09:06
ok très clair à nouveau
merci & bonne soirée
#14 Re : Général » Meilleure methode pour mettre a jour une table de correspondance » 08/05/2011 19:14:34
ok j'ai mes réponses, claires & précises.
Dernière question : en terme de performance, sur disons 15 inserts, l'insert multiple est-il vraiment un plus ou c'est plus une simplification syntaxique (ie PostgeSQL décompose-t-il la synthaxe de l'insert multiple en une série d'insert simples (pas de gain de perf dans ce cas) ou optimise-t-il vraiment ?)
merci
#15 Général » Meilleure methode pour mettre a jour une table de correspondance » 08/05/2011 18:44:48
- ilrico
- Réponses : 7
bonjour,
c'est un problème trivial, mais je ne suis pas certain que les réponses aux questions soient aussi triviales que je le suspecte.
j'ai un univers de catégories, pour simplifier 10, numérotées de 1 à 10.
pour chaque utilisateur, son choix est stocké dans une table de "mapping" à deux colonnes (user_id, cat_id)
pourquoi ? car l'univers de catégories est variable, donc je stocke ses préférences ici et non pas dans la table users ou chaque catégories (en colonne) aurait un flag booleen.
ainsi pour l'utilisateur 25 qui a choisi 3 catégories (2,3,8) j'aurais trois lignes
user_id cat_id
25 2
25 3
25 8
supposons que l'utilisateur change son choix de catégories à (2, 3, 9, 10)
la méthode la plus simple est d'effacer les corespondances existantes pour l'utilisateur 25 et faire 4 INSERT avec les nouvelles:
DELETE FROM prefs WHERE user_id = 25;
INSERT INTO prefs VALUES (25, 2);
INSERT INTO prefs VALUES (25, 3);
INSERT INTO prefs VALUES (25, 9);
INSERT INTO prefs VALUES (25, 10);
soit 5 requetes
j'ai 2 questions (en partant du principe que cette table aura quelques dizaines milliers d'utilisateurs, soit en gros quelques centaines de milliers de lignes) :
- en terme "SQL", il y a-t-il plus simple ?
- sur une table de cette taille, la méthode "DELETE" puis "INSERT" présente-elle des inconvénients à long terme sur les performances de la table ?
une autre méthode, faire la différence entre le mapping actuel et le cible (ie enlever le 25,8 pour ajouter le 25,9 et 25,10) me semble beaucoup plus lourd
(en PHP cela donnerait :
$mapdb = tableau du resultat SELECT cat_id FROM prefs WHERE user_id = 25;
$mapcible = tableau des nouvelles préférences
$remove = array_diff($mapdb, $mapcible);
$add = array_diff($mapcible, $mapdb);
boucle de DELETE FROM prefs WHERE user_id = 25 AND cat_id = $remove[$i]
boucle de INSERT INTO prefs VALUES (25, $add[$i])
)
merci,
#16 Re : Général » prob UTF8 sous psql, avec client_encoding et server_encoding en UTF8 » 03/05/2011 10:05:39
passage en 114, ça fonctionne nickel maintenant c'etait bien ça. bien vu & merci bcp pour votre aide.
#17 Re : Général » prob UTF8 sous psql, avec client_encoding et server_encoding en UTF8 » 03/05/2011 09:57:31
en effet apres vérification c'est la 113 qui est installée, je vais passer a la 114.
merci pour vos explications tres precises, c'est en effet bien le bug décrit dans le lien.
#18 Re : Général » prob UTF8 sous psql, avec client_encoding et server_encoding en UTF8 » 03/05/2011 09:07:04
euh, en fait je n'ai pas tout décrit:
je me connecte via une squeeze 32bit (systeme A) en ssh vers un serveur squeeze 64bit (systeme B, postgresql/psql 8.4.7)
A a été installé il y a 2 ans alors que sqeeze était testing
B a été installé il y a 2 mois alors que squeeze était stable release (Debian 6.0)
mais les deux sources.list pointent sur la testing actuelle, donc un update du paquet me ferait passer en postgresql 9.0.x je suppose.
mais le probleme ne vient pas du A car lorsque je suis sur le shell de B via ssh j'ai acces aux caractères (é, ç...), c'est juste sous psql qu'il y a un problème.
#19 Re : Général » prob UTF8 sous psql, avec client_encoding et server_encoding en UTF8 » 03/05/2011 08:48:26
Lorsque je l'ai installé c'était la testing (qui à l'époque était la Squeeze), qui est passé en stable release depuis.
ça date d'il y a 2 ans je dirais.
#20 Général » prob UTF8 sous psql, avec client_encoding et server_encoding en UTF8 » 02/05/2011 23:28:37
- ilrico
- Réponses : 7
[RESOLU]
Bonjour,
Sous un système configuré en UTF8
OS: GNU/Linux Debian
Postgresql 8.4.7 (idem pour psql)
sous le shell, les caratères 'spécifiques' fonctionnent : é ç
mais pas sous psql (ça fait un bip lorsqu'on les tape au clavier sans rien afficher)
alors que
show client_encoding donne UTF8
et
show server_encoding donne UTF8
et
\l montre que la database est bien en UTF8 (ok en_US.UTF-8 mais cela ne devrait pas bloquer)
une idée svp ?
merci
Pages : 1