explain.depesz.com

A tool for finding a real cause for slow queries.

Result: UMx

options
Did it help? Consider supporting us - Bitcoin address: 12v2hUztAk2LgzQ9H9LMwuU32urHMjZQnq
# exclusive inclusive rows x rows loops node
1. 3,827.776 3,827.776 ↓ 5.0 10 1

CTE Scan on with_articles wa (cost=301,540.73..301,543.29 rows=2 width=807) (actual time=3,827.729..3,827.776 rows=10 loops=1)

2.          

CTE with_articles

3. 0.020 3,827.723 ↓ 5.0 10 1

Limit (cost=301,540.72..301,540.73 rows=2 width=2,659) (actual time=3,827.701..3,827.723 rows=10 loops=1)

4. 2.762 3,827.703 ↓ 5.0 10 1

Sort (cost=301,540.72..301,540.73 rows=2 width=2,659) (actual time=3,827.698..3,827.703 rows=10 loops=1)

  • Sort Key: va.article_no, va.article_id
  • Sort Method: top-N heapsort Memory: 32kB
5. 2.789 3,824.941 ↓ 638.0 1,276 1

Hash Semi Join (cost=142,058.29..301,540.71 rows=2 width=2,659) (actual time=3,801.713..3,824.941 rows=1,276 loops=1)

  • Hash Cond: (va.article_id = atag.article_id)
6. 2.542 3,516.211 ↓ 1,277.0 1,277 1

Hash Join (cost=139,964.44..299,446.82 rows=1 width=2,667) (actual time=3,495.748..3,516.211 rows=1,277 loops=1)

  • Hash Cond: (va.vat_tax_id = t.tax_id)
7. 2.679 3,513.655 ↓ 1,277.0 1,277 1

Hash Left Join (cost=139,963.39..299,445.76 rows=1 width=2,664) (actual time=3,495.711..3,513.655 rows=1,277 loops=1)

  • Hash Cond: ((va.packaging_unit_handle)::text = (pul.packaging_unit_handle)::text)
8. 542.572 3,510.312 ↓ 1,277.0 1,277 1

Merge Right Join (cost=139,943.83..299,426.18 rows=1 width=2,656) (actual time=3,495.022..3,510.312 rows=1,277 loops=1)

  • Merge Cond: (adl.object_revision_id = va.object_revision_id)
9. 1,182.809 1,182.809 ↓ 1.0 681,978 1

Index Scan using fk_article_data_lang_object_revision_id on article_data_lang adl (cost=0.00..170,488.92 rows=657,003 width=2,489) (actual time=0.019..1,182.809 rows=681,978 loops=1)

  • Filter: (language_id = 132)
10. 3.516 1,784.931 ↓ 1,277.0 1,277 1

Sort (cost=127,294.71..127,294.72 rows=1 width=171) (actual time=1,783.828..1,784.931 rows=1,277 loops=1)

  • Sort Key: va.object_revision_id
  • Sort Method: quicksort Memory: 360kB
11. 2.570 1,781.415 ↓ 1,277.0 1,277 1

Hash Join (cost=125,114.51..127,294.70 rows=1 width=171) (actual time=1,710.072..1,781.415 rows=1,277 loops=1)

  • Hash Cond: (va.article_id = obf.object_id)
12. 41.910 1,755.633 ↓ 63.9 1,277 1

Hash Semi Join (cost=124,523.64..126,703.67 rows=20 width=167) (actual time=1,686.845..1,755.633 rows=1,277 loops=1)

  • Hash Cond: (va.article_id = "ANY_subquery".max)
13. 39.376 39.376 ↓ 1.0 46,888 1

Seq Scan on mv_v_articles va (cost=0.00..2,056.97 rows=46,797 width=163) (actual time=0.005..39.376 rows=46,888 loops=1)

14. 1.331 1,674.347 ↓ 63.9 1,277 1

Hash (cost=124,523.39..124,523.39 rows=20 width=4) (actual time=1,674.347..1,674.347 rows=1,277 loops=1)

15. 2.037 1,673.016 ↓ 63.9 1,277 1

Subquery Scan "ANY_subquery" (cost=124,522.89..124,523.39 rows=20 width=4) (actual time=1,669.707..1,673.016 rows=1,277 loops=1)

16. 3.577 1,670.979 ↓ 63.9 1,277 1

HashAggregate (cost=124,522.89..124,523.19 rows=20 width=34) (actual time=1,669.705..1,670.979 rows=1,277 loops=1)

17. 586.930 1,667.402 ↓ 76.3 1,527 1

Hash Join (cost=27,077.19..124,522.74 rows=20 width=34) (actual time=1,606.227..1,667.402 rows=1,527 loops=1)

  • Hash Cond: (adl.object_revision_id = o.object_revision_id)
18. 763.122 875.037 ↓ 1.0 681,982 1

Bitmap Heap Scan on article_data_lang adl (cost=15,324.33..110,305.87 rows=657,003 width=34) (actual time=138.039..875.037 rows=681,982 loops=1)

  • Recheck Cond: (language_id = 132)
19. 111.915 111.915 ↓ 1.1 713,256 1

Bitmap Index Scan on fk_article_data_lang_language_id (cost=0.00..15,160.08 rows=657,003 width=0) (actual time=111.915..111.915 rows=713,256 loops=1)

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

Hash (cost=11,751.68..11,751.68 rows=94 width=8) (actual time=205.435..205.435 rows=1,527 loops=1)

21. 42.806 203.830 ↓ 16.2 1,527 1

Hash Join (cost=9,518.28..11,751.68 rows=94 width=8) (actual time=135.360..203.830 rows=1,527 loops=1)

  • Hash Cond: (va.article_id = otf.object_id)
22. 50.580 50.580 ↓ 1.0 46,888 1

Seq Scan on mv_v_articles va (cost=0.00..2,056.97 rows=46,797 width=4) (actual time=0.003..50.580 rows=46,888 loops=1)

23. 1.674 110.444 ↓ 16.2 1,527 1

Hash (cost=9,517.11..9,517.11 rows=94 width=12) (actual time=110.444..110.444 rows=1,527 loops=1)

24. 40.901 108.770 ↓ 16.2 1,527 1

Hash Join (cost=2,056.60..9,517.11 rows=94 width=12) (actual time=15.422..108.770 rows=1,527 loops=1)

  • Hash Cond: (o.object_id = otf.object_id)
25. 53.875 64.732 ↓ 1.0 46,888 1

Bitmap Heap Scan on objects o (cost=1,465.73..8,588.92 rows=44,851 width=8) (actual time=11.534..64.732 rows=46,888 loops=1)

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

Bitmap Index Scan on ix_objects_object_type_handle (cost=0.00..1,454.52 rows=45,079 width=0) (actual time=10.857..10.857 rows=77,493 loops=1)

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

Hash (cost=572.32..572.32 rows=1,484 width=4) (actual time=3.137..3.137 rows=1,527 loops=1)

28. 1.456 1.671 ↓ 1.0 1,527 1

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

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

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

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

Hash (cost=572.32..572.32 rows=1,484 width=4) (actual time=23.212..23.212 rows=1,527 loops=1)

31. 21.511 21.779 ↓ 1.0 1,527 1

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

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

Bitmap Index Scan on fk_objects_to_frontends_frontend_handle (cost=0.00..71.40 rows=1,484 width=0) (actual time=0.268..0.268 rows=1,536 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 44,736 1

Hash (cost=1,535.39..1,535.39 rows=44,676 width=4) (actual time=305.941..305.941 rows=44,736 loops=1)

38. 172.762 238.205 ↓ 1.0 44,736 1

Hash Join (cost=62.45..1,535.39 rows=44,676 width=4) (actual time=1.440..238.205 rows=44,736 loops=1)

  • Hash Cond: (atag.article_group_id = mvagt.article_group_id)
39. 64.026 64.026 ↓ 1.0 44,736 1

Seq Scan on articles_to_article_groups atag (cost=0.00..858.65 rows=44,676 width=8) (actual time=0.013..64.026 rows=44,736 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=1,418 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=1,418 width=4) (actual time=0.018..0.743 rows=709 loops=1)

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