PostgreSQL DANS l'opérateur avec sous-requête des performances médiocres

Pourquoi l'opérateur "IN" si lent lorsqu'il est utilisé avec sous-requête?

select * 
from view1 
where id in (1,2,3,4,5,6,7,8,9,10) 
order by somedata;

s'exécute dans 9ms.

select * 
from view1 
where id in (select ext_id 
             from aggregate_table 
             order by somedata limit 10) 
order by somedata;

s'exécute dans 25000ms et semble utiliser balayage séquentiel sur la vue (view1) au lieu de l'analyse d'index sur des clés primaires renvoyées par la sous-requête comme en fait dans la première requête.

La sous-requête select ext_id from aggregate_table order by somedata limit 10 s'exécute dans 0,1 ms

de sorte que la lenteur de la seconde requête est causée par balayage séquentiel sur view1 qui est un point de vue
contenant trois SYNDICATS sur trois JOINTURES dans chaque SYNDICAT. La première UNION contient environ 1M de lignes, d'autres beaucoup moins. Les jointures avec les tableaux avec certains 100K lignes. Ce n'est pas si pertinente, si, je voulais juste comprendre le comportement de l'exploitant.

Ce que je suis en train d'accomplir est de prendre le résultat de la sous-requête (un ensemble de clés primaires) et de sélectionner les données à partir d'un complexe de vue (view1) à l'aide de seulement.

Je ne peux pas utiliser

select v1.* 
from view1 v1, 
     aggregate_table at 
where v1.id = at.ext_id 
order by at.somedata 
limit 10

parce que je ne veux pas trier les grandes joindre par somedata. Je veux juste de sélectionner 10 résultats à partir de la vue par des clés primaires et puis trier uniquement celles-ci.

La question est de savoir pourquoi DANS l'opérateur d'effectuer rapidement lorsque j'ai explicitement la liste de ces touches et si lent lorsque j'utilise un rapide sous-requête qui retourne exactement le même jeu de clés?

EXPLIQUER ANALYSER comme demandé

première requête - select * from view1 where id in (1,2,3,4,5,6,7,8,9,10) order by somedata;

    Sort  (cost=348.480..348.550 rows=30 width=943) (actual time=14.385..14.399 rows=10 loops=1)
Sort Key: "india".three
Sort Method:  quicksort  Memory: 30kB
->  Append  (cost=47.650..347.440 rows=30 width=334) (actual time=11.528..14.275 rows=10 loops=1)
->  Subquery Scan "*SELECT* 1"  (cost=47.650..172.110 rows=10 width=496) (actual time=11.526..12.301 rows=10 loops=1)
->  Nested Loop  (cost=47.650..172.010 rows=10 width=496) (actual time=11.520..12.268 rows=10 loops=1)
->  Hash Join  (cost=47.650..87.710 rows=10 width=371) (actual time=11.054..11.461 rows=10 loops=1)
Hash Cond: (hotel.alpha_five = juliet_xray.alpha_five)
->  Bitmap Heap Scan on sierra hotel  (cost=42.890..82.800 rows=10 width=345) (actual time=10.835..11.203 rows=10 loops=1)
Recheck Cond: (four = ANY ('quebec'::integer[]))
->  Bitmap Index Scan on seven  (cost=0.000..42.890 rows=10 width=0) (actual time=0.194..0.194 rows=10 loops=1)
Index Cond: (four = ANY ('quebec'::integer[]))
->  Hash  (cost=4.340..4.340 rows=34 width=30) (actual time=0.184..0.184 rows=34 loops=1)
->  Seq Scan on six juliet_xray  (cost=0.000..4.340 rows=34 width=30) (actual time=0.029..0.124 rows=34 loops=1)
->  Index Scan using charlie on juliet_two zulu  (cost=0.000..8.390 rows=1 width=129) (actual time=0.065..0.067 rows=1 loops=10)
Index Cond: (zulu.four = hotel.victor_whiskey)
->  Subquery Scan "*SELECT* 2"  (cost=4.760..97.420 rows=10 width=366) (actual time=0.168..0.168 rows=0 loops=1)
->  Hash Join  (cost=4.760..97.320 rows=10 width=366) (actual time=0.165..0.165 rows=0 loops=1)
Hash Cond: (alpha_xray.alpha_five = juliet_xray2.alpha_five)
->  Nested Loop  (cost=0.000..92.390 rows=10 width=340) (actual time=0.162..0.162 rows=0 loops=1)
->  Seq Scan on lima_echo alpha_xray  (cost=0.000..8.340 rows=10 width=216) (actual time=0.159..0.159 rows=0 loops=1)
Filter: (four = ANY ('quebec'::integer[]))
->  Index Scan using charlie on juliet_two xray  (cost=0.000..8.390 rows=1 width=128) (never executed)
Index Cond: (zulu2.four = alpha_xray.victor_whiskey)
->  Hash  (cost=4.340..4.340 rows=34 width=30) (never executed)
->  Seq Scan on six uniform  (cost=0.000..4.340 rows=34 width=30) (never executed)
->  Subquery Scan "*SELECT* 3"  (cost=43.350..77.910 rows=10 width=141) (actual time=1.775..1.775 rows=0 loops=1)
->  Hash Join  (cost=43.350..77.810 rows=10 width=141) (actual time=1.771..1.771 rows=0 loops=1)
Hash Cond: (golf.alpha_five = juliet_xray3.alpha_five)
->  Bitmap Heap Scan on lima_golf golf  (cost=38.590..72.910 rows=10 width=115) (actual time=0.110..0.110 rows=0 loops=1)
Recheck Cond: (four = ANY ('quebec'::integer[]))
->  Bitmap Index Scan on victor_hotel  (cost=0.000..38.590 rows=10 width=0) (actual time=0.105..0.105 rows=0 loops=1)
Index Cond: (four = ANY ('quebec'::integer[]))
->  Hash  (cost=4.340..4.340 rows=34 width=30) (actual time=0.118..0.118 rows=34 loops=1)
->  Seq Scan on six victor_kilo  (cost=0.000..4.340 rows=34 width=30) (actual time=0.007..0.063 rows=34 loops=1)
Total runtime: 14.728 ms

deuxième requête - select * from view1 where id in (select ext_id from aggregate_table order by somedata limit 10) order by somedata;

Sort  (cost=254515.780..254654.090 rows=55325 width=943) (actual time=24687.475..24687.488 rows=10 loops=1)
Sort Key: "five".xray_alpha
Sort Method:  quicksort  Memory: 30kB
->  Hash Semi Join  (cost=54300.820..250157.370 rows=55325 width=943) (actual time=11921.783..24687.308 rows=10 loops=1)
Hash Cond: ("five".lima = "delta_echo".lima)
->  Append  (cost=54298.270..235569.720 rows=1106504 width=494) (actual time=3412.453..23091.938 rows=1106503 loops=1)
->  Subquery Scan "*SELECT* 1"  (cost=54298.270..234227.250 rows=1100622 width=496) (actual time=3412.450..20234.122 rows=1100622 loops=1)
->  Hash Join  (cost=54298.270..223221.030 rows=1100622 width=496) (actual time=3412.445..17078.021 rows=1100622 loops=1)
Hash Cond: (three_victor.xray_hotel = delta_yankee.xray_hotel)
->  Hash Join  (cost=54293.500..180567.160 rows=1100622 width=470) (actual time=3412.251..12108.676 rows=1100622 loops=1)
Hash Cond: (three_victor.tango_three = quebec_seven.lima)
->  Seq Scan on india three_victor  (cost=0.000..104261.220 rows=1100622 width=345) (actual time=0.015..3437.722 rows=1100622 loops=1)
->  Hash  (cost=44613.780..44613.780 rows=774378 width=129) (actual time=3412.031..3412.031 rows=774603 loops=1)
->  Seq Scan on oscar quebec_seven  (cost=0.000..44613.780 rows=774378 width=129) (actual time=4.142..1964.036 rows=774603 loops=1)
->  Hash  (cost=4.340..4.340 rows=34 width=30) (actual time=0.149..0.149 rows=34 loops=1)
->  Seq Scan on alpha_kilo delta_yankee  (cost=0.000..4.340 rows=34 width=30) (actual time=0.017..0.095 rows=34 loops=1)
->  Subquery Scan "*SELECT* 2"  (cost=4.760..884.690 rows=104 width=366) (actual time=7.846..10.161 rows=104 loops=1)
->  Hash Join  (cost=4.760..883.650 rows=104 width=366) (actual time=7.837..9.804 rows=104 loops=1)
Hash Cond: (foxtrot.xray_hotel = delta_yankee2.xray_hotel)
->  Nested Loop  (cost=0.000..877.200 rows=104 width=340) (actual time=7.573..9.156 rows=104 loops=1)
->  Seq Scan on four_india foxtrot  (cost=0.000..7.040 rows=104 width=216) (actual time=0.081..0.311 rows=104 loops=1)
->  Index Scan using three_delta on oscar alpha_victor  (cost=0.000..8.350 rows=1 width=128) (actual time=0.077..0.078 rows=1 loops=104)
Index Cond: (quebec_seven2.lima = foxtrot.tango_three)
->  Hash  (cost=4.340..4.340 rows=34 width=30) (actual time=0.216..0.216 rows=34 loops=1)
->  Seq Scan on alpha_kilo quebec_foxtrot  (cost=0.000..4.340 rows=34 width=30) (actual time=0.035..0.153 rows=34 loops=1)
->  Subquery Scan "*SELECT* 3"  (cost=4.760..457.770 rows=5778 width=141) (actual time=0.264..58.353 rows=5777 loops=1)
->  Hash Join  (cost=4.760..399.990 rows=5778 width=141) (actual time=0.253..39.062 rows=5777 loops=1)
Hash Cond: (four_uniform.xray_hotel = delta_yankee3.xray_hotel)
->  Seq Scan on whiskey four_uniform  (cost=0.000..315.780 rows=5778 width=115) (actual time=0.112..15.759 rows=5778 loops=1)
->  Hash  (cost=4.340..4.340 rows=34 width=30) (actual time=0.117..0.117 rows=34 loops=1)
->  Seq Scan on alpha_kilo golf  (cost=0.000..4.340 rows=34 width=30) (actual time=0.005..0.059 rows=34 loops=1)
->  Hash  (cost=2.430..2.430 rows=10 width=4) (actual time=0.303..0.303 rows=10 loops=1)
->  Subquery Scan "ANY_subquery"  (cost=0.000..2.430 rows=10 width=4) (actual time=0.092..0.284 rows=10 loops=1)
->  Limit  (cost=0.000..2.330 rows=10 width=68) (actual time=0.089..0.252 rows=10 loops=1)
->  Index Scan using tango_seven on zulu romeo  (cost=0.000..257535.070 rows=1106504 width=68) (actual time=0.087..0.227 rows=10 loops=1)
Total runtime: 24687.975 ms

Pourriez-vous nous montrer le explain analyze SELECT ...? Peut-être à l'aide de depesz
Je voudrais essayer de mettre les résultats de la sous-requête dans la table temporaire, et do IN (select id from temptable). La différence est "limite" de la clause qui pourrait causer de l'optimiseur pour exécuter des sous-sélection pour chaque ligne dans le tableau 1. PS cela semble effectivement la même chose que @Clodoaldo suggère
essayé create temporary table aggregate_table_tmp as select ext_id from aggregate_table order by somedata limit 10 et ensuite l'utiliser dans la sous-requête select * from table1 where id in (select ext_id from aggregate_table_tmp) order by somedata - pas de chance. Même 25000ms.
"table1 ... is a view containing three joins" - Sur les vues et les jointures - j'ai remarqué (sur SQL server cependant) que le changement de JOIN à LEFT JOIN ou la suppression de ORDER BY peut faire une énorme différence dans la performance (plus précisément sur le point de vue, pas nécessairement les requêtes afférentes), la peine de jouer un peu avec elle. Ne serait pas "vue1" moins nom trompeur?
Je voudrais voir la définition de la vue. Contient-il de l'UNION ?
Oh, bien sûr, c'est un view containing three UNIONS et plusieurs jointures dans ces. C'est pourquoi je ne veux pas utiliser la requête qui trie l'ensemble de la vue, sinon c'est horriblement lent. Aussi, d'accord avec @Dukeling, mis à jour le post avec vue1 au lieu de table1.
Pouvez-vous remplacer les Syndicats par l'UNION de TOUS? (sans perte de fonctionnalité)
Ils sont en réalité de l'UNION ALLs. Aussi, même si je accélérer la requête de ce point de vue jusqu'à 2x, cela ne suffit toujours pas pour l'utilisateur d'être à l'aise. Donc, je voudrais juste requête en clé primaire, il est assez rapide. Je pense que je peux encore faire qu'à partir de l'application en fournissant les résultats de la sous-requête comme de simples entiers à la vue de requête. Il vient de me énigmes, pourquoi avec une sous-requête est beaucoup plus lent.
Peut-être un EXISTS (...) au lieu de la IN (...) va générer un plan différent, en raison de la sous-requête corrélée. (plus ou moins comme la réponse par @Dukeling ) de Mettre le résultat de la limitation de la sous-requête dans un temptable pourraient les aider à bien (à mon humble avis DANS=mauvais et LIMITE=even_more_bad)
Pourriez-vous afficher la définition de la vue et le réel de requête? Je pense que cela aiderait beaucoup à comprendre votre expliquer le résultat.
Aussi, essayez d'augmenter join_collapse_limit et from_collapse_limit avant l'exécution de la requête: SET join_collapse_limit TO 12;. Abaisser ensuite également d'aider, mais vous pouvez changer la vue de requête et de beaucoup et essayer la meilleure option.
EXISTS n'aide pas, il semble que, lorsque Postgres est conscient de exactement le nombre de touches, il effectue beaucoup plus rapidement, car il utilise Recheck Cond: (uid = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[]))

InformationsquelleAutor Snifff | 2013-02-20

36

Semble que j'ai enfin trouvé une solution:
```
select * 
from view1 
where view1.id = ANY(
(select array(select ext_id 
from aggregate_table 
order by somedata limit 10)
)::integer[]
) 
order by view1.somedata;
```
Après l'élaboration @Dukeling l'idée:

Je soupçonne where id in (1,2,3,4,5,6,7,8,9,10) peut être optimisé et
where id in (select ...) ne peut, la raison étant que
(1,2,3,4,5,6,7,8,9,10) est une expression constante, tandis que la sélection est
pas.

et la localisation de ces plus rapidement dans le plan de requête
```
Recheck Cond: (id = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[]))
Index Cond: (id = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[]))
```
cela fonctionne même plus rapide que la première requête dans la question, d'environ 1,2 ms, et maintenant, il utilise
```
Recheck Cond: (id = ANY ($1))
Index Cond: (id = ANY ($1))
```
et bitmap analyses dans le plan.
- À l'aide de ARRAY est une belle astuce pour instruire PG à utiliser les index dans la sous-requête. BTW, le ci-dessus ANY clause peut être simplifié à cette ANY(array(<your select query>))
- Je n'ai pas de big data (encore) d'expérimenter avec mais pourquoi select array(select ...) au lieu de l'suggéré par @BlueSmith array(select ...)? Aussi ne ::integer[] faire une différence? Par exemple, si j'ai de la chaîne de valeurs, ai-je besoin de jeter un type pour des performances plus rapides?
- Alors, quel genre d'échelle de temps delta est là, avec cette solution?
- 24687.975 ms vs 1.2 ms. En supposant qu'il existe un indice d'utilisation etc. Mais je crois que cela a été corrigé dans la plus récente PG versions et n'est plus un problème, planificateur de requête prend soin.
InformationsquelleAutor Snifff
3

Je soupçonne where id in (1,2,3,4,5,6,7,8,9,10) peut être optimisé et where id in (select ...) ne pouvez pas, la raison en étant que (1,2,3,4,5,6,7,8,9,10) est une expression constante, tandis que la select ne l'est pas.

Comment sur:
```
WITH myCTE AS
(
SELECT ext_id
FROM aggregate_table
ORDER BY somedata
LIMIT 10
)
SELECT *
FROM myCTE
LEFT JOIN table1
ON myCTE.ext_id = table1.id
ORDER BY somedata
```
- même chose que @Clodoaldo de la variante, 24000 ms
- Changé à LEFT JOIN, peut faire une différence. Ligne de fond semble être que PostgreSQL est en train de faire un travail terrible à optimiser, j'aimerais voir MySQL ou les performances de SQL Server sur les mêmes données.
- LEFT JOIN ne faire une différence en temps jusqu'à 65000ms 🙁
InformationsquelleAutor Dukeling

Vous devez vous connecter pour publier un commentaire.