explain.depesz.com

A tool for finding a real cause for slow queries.

Result: UMx

options
# exclusive inclusive rows x rows loops node
1. 3827.776 3827.776 ↓ 5.0 10 1
→

CTE Scan on with_articles wa (cost=301540.73..301543.29 rows=2 width=807) (actual time=3827.729..3827.776 rows=10 loops=1)

2.          

CTE with_articles
3. 0.020 3827.723 ↓ 5.0 10 1
→

Limit (cost=301540.72..301540.73 rows=2 width=2659) (actual time=3827.701..3827.723 rows=10 loops=1)

4. 2.762 3827.703 ↓ 5.0 10 1
→

Sort (cost=301540.72..301540.73 rows=2 width=2659) (actual time=3827.698..3827.703 rows=10 loops=1)

  • Sort Key: va.article_no, va.article_id
  • Sort Method: top-N heapsort Memory: 32kB
5. 2.789 3824.941 ↓ 638.0 1276 1
→

Hash Semi Join (cost=142058.29..301540.71 rows=2 width=2659) (actual time=3801.713..3824.941 rows=1276 loops=1)

  • Hash Cond: (va.article_id = atag.article_id)
6. 2.542 3516.211 ↓ 1277.0 1277 1
→

Hash Join (cost=139964.44..299446.82 rows=1 width=2667) (actual time=3495.748..3516.211 rows=1277 loops=1)

  • Hash Cond: (va.vat_tax_id = t.tax_id)
7. 2.679 3513.655 ↓ 1277.0 1277 1
→

Hash Left Join (cost=139963.39..299445.76 rows=1 width=2664) (actual time=3495.711..3513.655 rows=1277 loops=1)

  • Hash Cond: ((va.packaging_unit_handle)::text = (pul.packaging_unit_handle)::text)
8. 542.572 3510.312 ↓ 1277.0 1277 1
→

Merge Right Join (cost=139943.83..299426.18 rows=1 width=2656) (actual time=3495.022..3510.312 rows=1277 loops=1)

  • Merge Cond: (adl.object_revision_id = va.object_revision_id)
9. 1182.809 1182.809 ↓ 1.0 681978 1
→

Index Scan using fk_article_data_lang_object_revision_id on article_data_lang adl (cost=0.00..170488.92 rows=657003 width=2489) (actual time=0.019..1182.809 rows=681978 loops=1)

  • Filter: (language_id = 132)
10. 3.516 1784.931 ↓ 1277.0 1277 1
→

Sort (cost=127294.71..127294.72 rows=1 width=171) (actual time=1783.828..1784.931 rows=1277 loops=1)

  • Sort Key: va.object_revision_id
  • Sort Method: quicksort Memory: 360kB
11. 2.570 1781.415 ↓ 1277.0 1277 1
→

Hash Join (cost=125114.51..127294.70 rows=1 width=171) (actual time=1710.072..1781.415 rows=1277 loops=1)

  • Hash Cond: (va.article_id = obf.object_id)
12. 41.910 1755.633 ↓ 63.9 1277 1
→

Hash Semi Join (cost=124523.64..126703.67 rows=20 width=167) (actual time=1686.845..1755.633 rows=1277 loops=1)

  • Hash Cond: (va.article_id = "ANY_subquery".max)
13. 39.376 39.376 ↓ 1.0 46888 1
→

Seq Scan on mv_v_articles va (cost=0.00..2056.97 rows=46797 width=163) (actual time=0.005..39.376 rows=46888 loops=1)

14. 1.331 1674.347 ↓ 63.9 1277 1
→

Hash (cost=124523.39..124523.39 rows=20 width=4) (actual time=1674.347..1674.347 rows=1277 loops=1)

15. 2.037 1673.016 ↓ 63.9 1277 1
→

Subquery Scan "ANY_subquery" (cost=124522.89..124523.39 rows=20 width=4) (actual time=1669.707..1673.016 rows=1277 loops=1)

16. 3.577 1670.979 ↓ 63.9 1277 1
→

HashAggregate (cost=124522.89..124523.19 rows=20 width=34) (actual time=1669.705..1670.979 rows=1277 loops=1)

17. 586.930 1667.402 ↓ 76.3 1527 1
→

Hash Join (cost=27077.19..124522.74 rows=20 width=34) (actual time=1606.227..1667.402 rows=1527 loops=1)

  • Hash Cond: (adl.object_revision_id = o.object_revision_id)
18. 763.122 875.037 ↓ 1.0 681982 1
→

Bitmap Heap Scan on article_data_lang adl (cost=15324.33..110305.87 rows=657003 width=34) (actual time=138.039..875.037 rows=681982 loops=1)

  • Recheck Cond: (language_id = 132)
19. 111.915 111.915 ↓ 1.1 713256 1
→

Bitmap Index Scan on fk_article_data_lang_language_id (cost=0.00..15160.08 rows=657003 width=0) (actual time=111.915..111.915 rows=713256 loops=1)

  • Index Cond: (language_id = 132)
20. 1.605 205.435 ↓ 16.2 1527 1
→

Hash (cost=11751.68..11751.68 rows=94 width=8) (actual time=205.435..205.435 rows=1527 loops=1)

21. 42.806 203.830 ↓ 16.2 1527 1
→

Hash Join (cost=9518.28..11751.68 rows=94 width=8) (actual time=135.360..203.830 rows=1527 loops=1)

  • Hash Cond: (va.article_id = otf.object_id)
22. 50.580 50.580 ↓ 1.0 46888 1
→

Seq Scan on mv_v_articles va (cost=0.00..2056.97 rows=46797 width=4) (actual time=0.003..50.580 rows=46888 loops=1)

23. 1.674 110.444 ↓ 16.2 1527 1
→

Hash (cost=9517.11..9517.11 rows=94 width=12) (actual time=110.444..110.444 rows=1527 loops=1)

24. 40.901 108.770 ↓ 16.2 1527 1
→

Hash Join (cost=2056.60..9517.11 rows=94 width=12) (actual time=15.422..108.770 rows=1527 loops=1)

  • Hash Cond: (o.object_id = otf.object_id)
25. 53.875 64.732 ↓ 1.0 46888 1
→

Bitmap Heap Scan on objects o (cost=1465.73..8588.92 rows=44851 width=8) (actual time=11.534..64.732 rows=46888 loops=1)

  • Recheck Cond: ((object_type_handle)::text = 'article'::text)
  • Filter: ((object_state_handle)::text <> 'deleted'::text)
26. 10.857 10.857 ↓ 1.7 77493 1
→

Bitmap Index Scan on ix_objects_object_type_handle (cost=0.00..1454.52 rows=45079 width=0) (actual time=10.857..10.857 rows=77493 loops=1)

  • Index Cond: ((object_type_handle)::text = 'article'::text)
27. 1.466 3.137 ↓ 1.0 1527 1
→

Hash (cost=572.32..572.32 rows=1484 width=4) (actual time=3.137..3.137 rows=1527 loops=1)

28. 1.456 1.671 ↓ 1.0 1527 1
→

Bitmap Heap Scan on objects_to_frontends otf (cost=71.77..572.32 rows=1484 width=4) (actual time=0.229..1.671 rows=1527 loops=1)

  • Recheck Cond: ((frontend_handle)::text = 'shop_collectors'::text)
29. 0.215 0.215 ↓ 1.0 1536 1
→

Bitmap Index Scan on fk_objects_to_frontends_frontend_handle (cost=0.00..71.40 rows=1484 width=0) (actual time=0.215..0.215 rows=1536 loops=1)

  • Index Cond: ((frontend_handle)::text = 'shop_collectors'::text)
30. 1.433 23.212 ↓ 1.0 1527 1
→

Hash (cost=572.32..572.32 rows=1484 width=4) (actual time=23.212..23.212 rows=1527 loops=1)

31. 21.511 21.779 ↓ 1.0 1527 1
→

Bitmap Heap Scan on objects_to_frontends obf (cost=71.77..572.32 rows=1484 width=4) (actual time=0.285..21.779 rows=1527 loops=1)

  • Recheck Cond: ((frontend_handle)::text = 'shop_collectors'::text)
32. 0.268 0.268 ↓ 1.0 1536 1
→

Bitmap Index Scan on fk_objects_to_frontends_frontend_handle (cost=0.00..71.40 rows=1484 width=0) (actual time=0.268..0.268 rows=1536 loops=1)

  • Index Cond: ((frontend_handle)::text = 'shop_collectors'::text)
33. 0.316 0.664 ↓ 1.1 287 1
→

Hash (cost=16.18..16.18 rows=271 width=13) (actual time=0.664..0.664 rows=287 loops=1)

34. 0.348 0.348 ↓ 1.1 287 1
→

Seq Scan on packaging_units_lang pul (cost=0.00..16.18 rows=271 width=13) (actual time=0.010..0.348 rows=287 loops=1)

  • Filter: (language_id = 132)
35. 0.005 0.014 ↑ 1.0 2 1
→

Hash (cost=1.02..1.02 rows=2 width=11) (actual time=0.014..0.014 rows=2 loops=1)

36. 0.009 0.009 ↑ 1.0 2 1
→

Seq Scan on taxes t (cost=0.00..1.02 rows=2 width=11) (actual time=0.007..0.009 rows=2 loops=1)

  • Filter: ((object_state_handle)::text <> 'deleted'::text)
37. 67.736 305.941 ↓ 1.0 44736 1
→

Hash (cost=1535.39..1535.39 rows=44676 width=4) (actual time=305.941..305.941 rows=44736 loops=1)

38. 172.762 238.205 ↓ 1.0 44736 1
→

Hash Join (cost=62.45..1535.39 rows=44676 width=4) (actual time=1.440..238.205 rows=44736 loops=1)

  • Hash Cond: (atag.article_group_id = mvagt.article_group_id)
39. 64.026 64.026 ↓ 1.0 44736 1
→

Seq Scan on articles_to_article_groups atag (cost=0.00..858.65 rows=44676 width=8) (actual time=0.013..64.026 rows=44736 loops=1)

  • Filter: ((article_group_assignment_type_handle)::text = 'default'::text)
40. 0.674 1.417 ↑ 2.0 709 1
→

Hash (cost=44.73..44.73 rows=1418 width=4) (actual time=1.417..1.417 rows=709 loops=1)

41. 0.743 0.743 ↑ 2.0 709 1
→

Seq Scan on mv_v_article_groups_tree mvagt (cost=0.00..44.73 rows=1418 width=4) (actual time=0.018..0.743 rows=709 loops=1)

  • Filter: (tree_sort ~~ '01%'::text)