La requête est:
SELECT * FROM t WHERE a < CURRENT_TIMESTAMP AND c < CURRENT_TIMESTAMP ORDER BY b
Quel est le meilleur index?
Et si nous avons une autre requête:
SELECT * FROM t WHERE a < CURRENT_TIMESTAMP ORDER BY b
Quel est le meilleur indice? Y a-t-il un index qui peut servir les deux requêtes?
EDIT: Je m'excuse d'avoir changé la requête après que certaines réponses aient déjà été écrites. Les nouvelles requêtes reflètent mieux la situation que j'ai.
EDIT: Détails supplémentaires potentiellement pertinents:
1) 'SELECT *' est écrit pour simplifier la question (la requête réelle n'utilise pas * ).
2) Je peux diviser les requêtes en utilisant LIMIT si cela améliore les performances globales.
4 Réponses :
Ce que vous voulez dans ce cas particulier, c'est un index partiel (index filtré). Vous pouvez trouver la documentation ici: https://www.postgresql.org/docs /current/indexes-partial.html
CREATE INDEX idx___xxx ON table (b)
WHERE a<0;
Modifier comme la question a été modifiée: cette réponse est bonne tant que la clause where utilise une constante et que cette constante est connue avant l'opération d'indexation.
Cette réponse a des mérites. Un index partiel peut cependant être très utile si le nombre de lignes sélectionnées est inférieur à 5%.
Ma question d'origine avait "un <0" mais cela a été changé en "un
Il n'est pas possible d'utiliser ici un index partiel car la valeur de comparaison change avec le temps (CURRENT_TIMESTAMP).
Le meilleur index de votre cas est un index multi-colonnes dans cet ordre: (a, c, b)
Pour la première requête, le planificateur filtrera par a <0 et utilisera la deuxième colonne pour trier (aucun tri requis à la fin de la requête).
Pour le deuxième cas, le planificateur filtrera par a <0 et c <0 et utilisera la troisième colonne pour trier les enregistrements trouvés (en sautant également le tri à la fin).
La réponse de Dumitrescu Bogdan fonctionne, mais nécessite une procédure de tri à la fin et, dans le second cas, le nombre d'enregistrements filtrés est inférieur à mon approche.
Bonne référence pour en savoir plus sur les index multicolonnes: https://use-the-index-luke.com/sql/where-clause/the-equals-operator/concatenated-keys
De plus, celui-ci explique comment l'index peut éviter une opération de tri à la fin: https://use-the-index-luke.com/sql/sorting-grouping/indexed-order-by
<❯Mise à jour
Après la mise à jour de l'auteur, il faut deux index pour éviter l'opération de tri.
Pour le premier cas, vous aurez besoin d'un index sur (a, b) .
Pour le second cas, vous aurez besoin d'un index sur (a, c, b) . Avec cette configuration, dans les deux cas, vous évitez l'opération de tri final.
Merci pour votre réponse. J'ai échangé «b» et «c» lors de l'écriture de la deuxième requête par accident. Cela change-t-il la réponse?
@RayZhang, oui, ça change la réponse ... Dans ce cas, l'ordre des colonnes devrait être (a, c, b). Lorsque vous avez le temps, jetez un œil au lien que j'ai laissé dans la question. Cela explique très bien pourquoi la commande compte
Je ne pense pas que Postgres puisse éviter le tri avec les index proposés (pour order by b sans index qui a b comme colonne principale.)
@viniciusjssouza J'ai supprimé le vote négatif. J'ai [à tort] supposé que les conditions de filtrage n'étaient pas assez sélectives.
@ spencer7593 vous avez raison. L'auteur a changé la question, supprimant l'avantage d'éviter le tri sur le premier cas
Un index qui peut fonctionner est:
EXPLAIN ANALYZE
SELECT *
FROM t
WHERE a < CURRENT_TIMESTAMP AND c < CURRENT_TIMESTAMP
ORDER BY b;
Sort (cost=33.30..33.86 rows=227 width=12) (actual time=0.012..0.013 rows=0 loops=1)
Sort Key: b
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on t (cost=11.01..24.41 rows=227 width=12) (actual time=0.006..0.006 rows=0 loops=1)
Recheck Cond: ((a < 0) AND (c < 0))
-> Bitmap Index Scan on ix_t_006 (cost=0.00..10.95 rows=227 width=0)(actual time=0.005..0.005 rows=0 loops=1)
Index Cond: ((a < 0) AND (c < 0))
Après des tests répétés, j'ai trouvé que c'était un index "OK" pour gérer vos deux requêtes. Vous voyez que cet index gère vos clauses WHERE mais ORDER BY b n'est pas géré.
Au lieu de cela, ORDER BY est géré par un tri rapide en mémoire. Ceci est dû au fait qu'une "analyse d'index bitmap" est en cours sur les colonnes (a, c), ce qui signifie que tout ordre de tri implicite sera annulé. Donc, créer un index sur (a, c, b) ne sera pas efficace car l'ordre sera annulé, donc, la seule chose que nous avons en train de travailler est un index sur (a, c).
La chose il manque à votre question la quantité de résultats que vous obtiendrez et si un tri rapide en mémoire est satisfaisant.
Veuillez utiliser EXPLAIN ANALYZE SELECT ... pour vérifier.
Le premier la requête a abouti à:
EXPLAIN ANALYZE
SELECT *
FROM t
WHERE a < CURRENT_TIMESTAMP
ORDER BY b;
Sort (cost=59.91..61.61 rows=680 width=12) (actual time=0.012..0.012 rows=0 loops=1)
Sort Key: b
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on t (cost=9.42..27.92 rows=680 width=12) (actual time=0.005..0.006 rows=0 loops=1)
Recheck Cond: (a < 0)
-> Bitmap Index Scan on ix_t_006 (cost=0.00..9.25 rows=680 width=0) (actual time=0.004..0.004 rows=0 loops=1)
Index Cond: (a < 0)
Planning time: 0.091 ms
Execution time: 0.042 ms
La deuxième requête a abouti à:
CREATE INDEX ix_t_006 on t (a, c);
Après des tests répétés, j'ai constaté que ma réponse avait à éditer à plusieurs reprises. À un moment donné, je me suis retrouvé à vouloir supprimer ma réponse car je la changeais tellement. Je suis déçu de ne pas vous avoir trouvé de réponse qui vous a aidé avec ORDER BY.
Je pense que la clé est de continuer à essayer avec EXPLAIN ANALYZE jusqu'à ce que vous l'ayez.
Merci pour votre réponse. J'ai échangé «b» et «c» lors de l'écriture de la deuxième requête par accident. Cela change-t-il la réponse?
Ray, votre question mise à jour nécessitait une réécriture de la réponse
Ray, j'ai réécrit toute ma réponse après avoir testé les index avec EXPLAIN ANALYZE.
Merci pour cet effort. Je l'apprécie énormément.
@StephenQuan, votre approche n'est pas optimale car elle n'est pas capable de filtrer les lignes. L'exécuteur devrait balayer l'index jusqu'à ce qu'il trouve les lignes qui correspondent aux filtres sur a et c. L'ordre de b ne garantit pas l'ordre de b et c. Ces liens fournissent des explications sur le sujet: use-the- index-luke.com/sql/sorting-grouping/indexed-order-by
Aucun index ne sera probablement meilleur qu'une analyse de tas (c'est-à-dire une analyse complète de la table).
Vous n'incluez pas les statistiques de table, mais je suppose que les conditions de filtrage récupèrent plus de 5% des lignes (très probablement). Si tel est le cas, un Heap Scan sera plus rapide que n'importe quel index et moins gourmand en ressources.
Quel pourcentage de lignes ces requêtes renvoient-elles? 50%, 5%, 0,5%?
La requête renvoie <1% des lignes dans un cas d'utilisation et ~ 5-10% des lignes dans un autre cas d'utilisation. Cependant, dans le deuxième cas d'utilisation, j'ai la possibilité de diviser la requête en utilisant une clause LIMIT 1000, par exemple, qui présente d'autres avantages potentiels pour l'application sur laquelle je travaille.
Ensuite, les index peuvent être utiles dans le premier cas. Dans le second cas, aucun index ne sera probablement meilleur qu'une analyse de tas.
Comme la requête accède aux données historiques, la réponse @TheImpaler serait correcte, sauf si une limite a été utilisée. Mais, comme l'a souligné l'auteur des questions, il utilise une limite, ce qui rend l'index utile
@viniciusjssouza La LIMITE n'est-elle pas appliquée une fois que l'analyse ou le filtre initial est déjà fait, puisque les résultats doivent être triés avant que LIMIT puisse fonctionner? Pouvez-vous clarifier l'ordre des opérations qui permet à LIMIT de garder l'index utile?
@RayZhang, l'exécuteur de requêtes balaiera l'index en collectant les résultats jusqu'à ce qu'il atteigne la limite que vous avez fournie. Comme les enregistrements sont déjà triés (par l'index lui-même), il n'est pas nécessaire d'effectuer un tri final.
@RayZhang ce lien explique la technique: use-the- index-luke.com/sql/sorting-grouping/indexed-order-by
Vous devez définir ce qu'est CURRENT_TIMESTAMP? Cela change la question de savoir s'il s'agit simplement d'un espace réservé pour une constante ou s'il s'agit d'une colonne réelle dans le tableau t. En outre, le type des colonnes peut être important, vous devez fournir le ddl CREATE TABLE. Des exemples de données et des indications de propagation peuvent également être utiles pour décider quel est le meilleur indice pour vous.
CURRENT_TIMESTAMP est la "constante" magique fournie par Postgres qui est toujours égale à l'heure actuelle. Je ne sais pas s'il est techniquement correct d'appeler cette variable dynamique gérée par le système une «constante».
Aucun des index ne sera utile si la majorité de vos données appartient au passé (c'est-à-dire inférieur à current_timestamp). Vous utilisez également
SELECT *.@SalmanA, dans l'un des commentaires ci-dessous, il a souligné qu'il utilisait une limite.
J'ai écrit * pour garder la question simple mais l'implémentation réelle n'utilise pas *. La plupart des données appartiennent au futur (> CURRENT_TIMESTAMP) mais votre point est bien pris, merci.