explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dQqD

Settings
# exclusive inclusive rows x rows loops node
1. 13,945.866 20,366.106 ↑ 7.1 82,369 1

GroupAggregate (cost=3,098,595.63..3,273,285.55 rows=587,193 width=249) (actual time=6,378.854..20,366.106 rows=82,369 loops=1)

  • Group Key: oh.id, oi.item_id, poh.partner_id, p.city, poh.amount, i.megnev, p.cid, l.name
2. 124.829 6,420.240 ↑ 7.1 82,369 1

Sort (cost=3,098,595.63..3,100,063.61 rows=587,193 width=149) (actual time=6,378.240..6,420.240 rows=82,369 loops=1)

  • Sort Key: oh.id DESC, oi.item_id, poh.partner_id, p.city, poh.amount, i.megnev, p.cid, l.name
  • Sort Method: external merge Disk: 13256kB
3. 1,163.863 6,295.411 ↑ 7.1 82,369 1

Nested Loop Left Join (cost=603,455.89..2,954,020.34 rows=587,193 width=149) (actual time=3,677.374..6,295.411 rows=82,369 loops=1)

  • Join Filter: ((((l.id)::character varying)::text = (COALESCE(ssv.signatory, (ssv.create_user)::character varying))::text) OR ((l.name)::text = (COALESCE(ssv.signatory, (ssv.create_user)::character varying))::text))
  • Rows Removed by Join Filter: 6260712
4. 110.204 4,802.072 ↑ 7.1 82,369 1

Hash Join (cost=603,455.89..1,936,704.50 rows=587,193 width=190) (actual time=3,677.351..4,802.072 rows=82,369 loops=1)

  • Hash Cond: (poh.ap_order_id = oi.order_id)
5. 1,238.121 4,570.690 ↑ 4.4 73,639 1

Hash Right Join (cost=590,181.36..1,900,219.47 rows=323,721 width=156) (actual time=3,556.060..4,570.690 rows=73,639 loops=1)

  • Hash Cond: (ssv.partner_id = poh.partner_id)
  • Join Filter: ((oh.sale_id = ssv.base_auto_sale_id) OR (oh.sale_id = ssv.extra_auto_sale_id) OR (oh.sale_id = ssv.extra_sale_id) OR (oh.sale_id = ssv.over_sale_id))
  • Rows Removed by Join Filter: 5805667
6. 11.233 3,152.307 ↑ 8.7 63,586 1

Subquery Scan on ssv (cost=579,824.12..597,830.77 rows=554,051 width=76) (actual time=3,113.250..3,152.307 rows=63,586 loops=1)

7. 15.099 3,141.074 ↑ 8.7 63,586 1

Unique (cost=579,824.12..592,290.26 rows=554,051 width=80) (actual time=3,113.246..3,141.074 rows=63,586 loops=1)

8. 88.050 3,125.975 ↑ 7.9 70,281 1

Sort (cost=579,824.12..581,209.24 rows=554,051 width=80) (actual time=3,113.245..3,125.975 rows=70,281 loops=1)

  • Sort Key: po.id, po.partner_id, po.signatory, po.create_user, poe.auto_sale_id, poe.sale_id, poe.sale_id, poe.sale_id
  • Sort Method: external merge Disk: 2752kB
9. 4.622 3,037.925 ↑ 7.9 70,281 1

Append (cost=478,025.74..502,349.59 rows=554,051 width=80) (actual time=2,946.215..3,037.925 rows=70,281 loops=1)

10. 8.385 2,955.956 ↑ 32.4 16,550 1

Hash Join (cost=478,025.74..486,137.23 rows=536,276 width=28) (actual time=2,946.214..2,955.956 rows=16,550 loops=1)

  • Hash Cond: (po_1.id = po.id)
11. 4.492 2,939.560 ↑ 32.4 16,550 1

Sort (cost=477,291.92..478,632.61 rows=536,276 width=164) (actual time=2,938.161..2,939.560 rows=16,550 loops=1)

  • Sort Key: po_1.id
  • Sort Method: quicksort Memory: 2901kB
12. 5.955 2,935.068 ↑ 32.4 16,550 1

Unique (cost=315,483.52..338,275.25 rows=536,276 width=164) (actual time=2,927.855..2,935.068 rows=16,550 loops=1)

13. 21.314 2,929.113 ↑ 32.4 16,550 1

Sort (cost=315,483.52..316,824.21 rows=536,276 width=164) (actual time=2,927.853..2,929.113 rows=16,550 loops=1)

  • Sort Key: poe.brand_offer_id, po_1.id, poe.partner_id, poe.eng, poe.eng, poe.eng, poe.auto_sale_id, poe.sale_id, poe.sale_id, poe.sale_id, poe.ratio, poe.max_ratio, po_1.darab_ketheti, (NULL::integer), (NULL::integer), (((SubPlan 1) + po_1.add_to_extra_limit))
  • Sort Method: quicksort Memory: 2901kB
14. 3.330 2,907.799 ↑ 32.4 16,550 1

Append (cost=2,141.60..176,466.84 rows=536,276 width=164) (actual time=33.349..2,907.799 rows=16,550 loops=1)

15. 13.490 683.070 ↓ 49.0 4,561 1

Hash Join (cost=2,141.60..2,811.48 rows=93 width=70) (actual time=33.348..683.070 rows=4,561 loops=1)

  • Hash Cond: ((po_1.ratio = poe.ratio) AND (po_1.partner_id = poe.partner_id) AND (po_1.brand_offer_id = poe.brand_offer_id))
16. 2.542 2.542 ↓ 1.0 19,105 1

Seq Scan on partner_offer po_1 (cost=0.00..495.03 rows=19,103 width=25) (actual time=0.007..2.542 rows=19,105 loops=1)

17. 19.663 33.059 ↑ 1.0 53,731 1

Hash (cost=1,201.31..1,201.31 rows=53,731 width=32) (actual time=33.059..33.059 rows=53,731 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 4077kB
18. 13.396 13.396 ↑ 1.0 53,731 1

Seq Scan on partner_offer_eng poe (cost=0.00..1,201.31 rows=53,731 width=32) (actual time=0.007..13.396 rows=53,731 loops=1)

19.          

SubPlan (forHash Join)

20. 633.979 633.979 ↑ 1.0 1 4,561

Result (cost=0.00..0.26 rows=1 width=4) (actual time=0.139..0.139 rows=1 loops=4,561)

21. 36.510 2,221.399 ↑ 44.7 11,989 1

Merge Join (cost=8,747.95..165,611.22 rows=536,183 width=148) (actual time=58.497..2,221.399 rows=11,989 loops=1)

  • Merge Cond: (sbm.partner_offer_id = sem.partner_offer_id)
22.          

CTE selected_base_matrix

23. 8.802 9.989 ↓ 2.0 11,991 1

Bitmap Heap Scan on partner_matrix pm (cost=449.72..2,845.47 rows=5,949 width=24) (actual time=1.540..9.989 rows=11,991 loops=1)

  • Filter: ((is_base IS TRUE) AND (is_selected IS TRUE))
  • Rows Removed by Filter: 11991
  • Heap Blocks: exact=2149
24. 1.187 1.187 ↓ 1.0 23,982 1

Bitmap Index Scan on partner_matrix_is_selected_idx (cost=0.00..448.23 rows=23,975 width=0) (actual time=1.187..1.187 rows=23,982 loops=1)

  • Index Cond: (is_selected = true)
25.          

CTE selected_extra_matrix

26. 2.954 15.140 ↑ 1.5 11,991 1

Hash Left Join (cost=583.28..3,026.35 rows=18,026 width=37) (actual time=4.013..15.140 rows=11,991 loops=1)

  • Hash Cond: (pm_1.over_limit_sale_id = sh.id)
27. 8.472 9.809 ↑ 1.5 11,991 1

Bitmap Heap Scan on partner_matrix pm_1 (cost=452.74..2,848.49 rows=18,026 width=32) (actual time=1.624..9.809 rows=11,991 loops=1)

  • Filter: ((is_base IS FALSE) AND (is_selected IS TRUE))
  • Rows Removed by Filter: 11991
  • Heap Blocks: exact=2149
28. 1.337 1.337 ↓ 1.0 23,982 1

Bitmap Index Scan on partner_matrix_is_selected_idx (cost=0.00..448.23 rows=23,975 width=0) (actual time=1.337..1.337 rows=23,982 loops=1)

  • Index Cond: (is_selected = true)
29. 0.876 2.377 ↓ 1.0 3,230 1

Hash (cost=90.24..90.24 rows=3,224 width=9) (actual time=2.377..2.377 rows=3,230 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 171kB
30. 1.501 1.501 ↓ 1.0 3,230 1

Seq Scan on sale_head sh (cost=0.00..90.24 rows=3,224 width=9) (actual time=0.008..1.501 rows=3,230 loops=1)

31. 6.454 35.889 ↓ 2.0 11,989 1

Sort (cost=1,241.37..1,256.24 rows=5,949 width=50) (actual time=33.725..35.889 rows=11,989 loops=1)

  • Sort Key: sbm.partner_offer_id
  • Sort Method: quicksort Memory: 1336kB
32. 5.639 29.435 ↓ 2.0 11,989 1

Hash Join (cost=733.82..868.41 rows=5,949 width=50) (actual time=11.441..29.435 rows=11,989 loops=1)

  • Hash Cond: (sbm.partner_offer_id = po_2.id)
33. 13.926 13.926 ↓ 2.0 11,991 1

CTE Scan on selected_base_matrix sbm (cost=0.00..118.98 rows=5,949 width=32) (actual time=1.544..13.926 rows=11,991 loops=1)

34. 4.242 9.870 ↓ 1.0 19,105 1

Hash (cost=495.03..495.03 rows=19,103 width=18) (actual time=9.870..9.870 rows=19,105 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1227kB
35. 5.628 5.628 ↓ 1.0 19,105 1

Seq Scan on partner_offer po_2 (cost=0.00..495.03 rows=19,103 width=18) (actual time=0.014..5.628 rows=19,105 loops=1)

36. 6.485 26.947 ↑ 1.5 11,991 1

Sort (cost=1,634.76..1,679.82 rows=18,026 width=56) (actual time=24.422..26.947 rows=11,991 loops=1)

  • Sort Key: sem.partner_offer_id
  • Sort Method: quicksort Memory: 1321kB
37. 20.462 20.462 ↑ 1.5 11,991 1

CTE Scan on selected_extra_matrix sem (cost=0.00..360.52 rows=18,026 width=56) (actual time=4.018..20.462 rows=11,991 loops=1)

38.          

SubPlan (forMerge Join)

39. 2,122.053 2,122.053 ↑ 1.0 1 11,989

Result (cost=0.00..0.26 rows=1 width=4) (actual time=0.177..0.177 rows=1 loops=11,989)

40. 3.676 8.011 ↓ 1.0 19,105 1

Hash (cost=495.03..495.03 rows=19,103 width=12) (actual time=8.011..8.011 rows=19,105 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1106kB
41. 4.335 4.335 ↓ 1.0 19,105 1

Seq Scan on partner_offer po (cost=0.00..495.03 rows=19,103 width=12) (actual time=0.032..4.335 rows=19,105 loops=1)

42. 20.789 77.347 ↓ 3.0 53,731 1

Merge Join (cost=7,276.21..7,901.60 rows=17,775 width=28) (actual time=48.315..77.347 rows=53,731 loops=1)

  • Merge Cond: ((poe_1.brand_offer_id = po_3.brand_offer_id) AND (poe_1.partner_id = po_3.partner_id))
43. 27.560 37.697 ↑ 1.0 53,731 1

Sort (cost=5,422.81..5,557.14 rows=53,731 width=16) (actual time=32.701..37.697 rows=53,731 loops=1)

  • Sort Key: poe_1.brand_offer_id, poe_1.partner_id
  • Sort Method: quicksort Memory: 3884kB
44. 10.137 10.137 ↑ 1.0 53,731 1

Seq Scan on partner_offer_eng poe_1 (cost=0.00..1,201.31 rows=53,731 width=16) (actual time=0.014..10.137 rows=53,731 loops=1)

45. 13.533 18.861 ↓ 2.8 54,328 1

Sort (cost=1,853.40..1,901.16 rows=19,103 width=16) (actual time=15.604..18.861 rows=54,328 loops=1)

  • Sort Key: po_3.brand_offer_id, po_3.partner_id
  • Sort Method: quicksort Memory: 2121kB
46. 5.328 5.328 ↓ 1.0 19,105 1

Seq Scan on partner_offer po_3 (cost=0.00..495.03 rows=19,103 width=16) (actual time=0.017..5.328 rows=19,105 loops=1)

47. 32.159 180.262 ↓ 1.0 73,639 1

Hash (cost=8,318.42..8,318.42 rows=72,466 width=104) (actual time=180.262..180.262 rows=73,639 loops=1)

  • Buckets: 32768 Batches: 4 Memory Usage: 2839kB
48. 24.465 148.103 ↓ 1.0 73,639 1

Hash Join (cost=2,559.95..8,318.42 rows=72,466 width=104) (actual time=31.109..148.103 rows=73,639 loops=1)

  • Hash Cond: (oh.partner_id = p.id)
49. 54.894 122.488 ↓ 1.0 73,639 1

Hash Join (cost=2,419.31..7,987.31 rows=72,466 width=94) (actual time=29.951..122.488 rows=73,639 loops=1)

  • Hash Cond: (oh.id = poh.ap_order_id)
50. 37.709 37.709 ↓ 1.0 78,759 1

Seq Scan on order_head oh (cost=0.00..2,846.30 rows=78,738 width=82) (actual time=0.006..37.709 rows=78,759 loops=1)

  • Filter: (torolt IS FALSE)
  • Rows Removed by Filter: 1371
51. 17.305 29.885 ↑ 1.0 73,747 1

Hash (cost=1,136.47..1,136.47 rows=73,747 width=12) (actual time=29.885..29.885 rows=73,747 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2614kB
52. 12.580 12.580 ↑ 1.0 73,747 1

Seq Scan on partner_order_head poh (cost=0.00..1,136.47 rows=73,747 width=12) (actual time=0.005..12.580 rows=73,747 loops=1)

53. 0.532 1.150 ↑ 1.0 3,362 1

Hash (cost=98.62..98.62 rows=3,362 width=18) (actual time=1.150..1.150 rows=3,362 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 200kB
54. 0.618 0.618 ↑ 1.0 3,362 1

Seq Scan on partner p (cost=0.00..98.62 rows=3,362 width=18) (actual time=0.008..0.618 rows=3,362 loops=1)

55. 27.212 121.178 ↑ 1.4 93,218 1

Hash (cost=10,426.43..10,426.43 rows=133,768 width=42) (actual time=121.178..121.178 rows=93,218 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 2242kB
56. 53.542 93.966 ↑ 1.4 93,218 1

Merge Join (cost=21.88..10,426.43 rows=133,768 width=42) (actual time=0.398..93.966 rows=93,218 loops=1)

  • Merge Cond: (oi.item_id = ((i.amkod)::numeric))
57. 34.931 34.931 ↑ 1.0 93,218 1

Index Scan using tp_order_item_item_id on order_item oi (cost=0.29..7,030.35 rows=93,218 width=13) (actual time=0.020..34.931 rows=93,218 loops=1)

58. 5.300 5.493 ↓ 325.4 93,394 1

Sort (cost=21.59..22.30 rows=287 width=43) (actual time=0.366..5.493 rows=93,394 loops=1)

  • Sort Key: ((i.amkod)::numeric)
  • Sort Method: quicksort Memory: 57kB
59. 0.193 0.193 ↑ 1.0 287 1

Seq Scan on item i (cost=0.00..9.87 rows=287 width=43) (actual time=0.015..0.193 rows=287 loops=1)

60. 329.452 329.476 ↑ 1.0 77 82,369

Materialize (cost=0.00..4.16 rows=77 width=19) (actual time=0.000..0.004 rows=77 loops=82,369)

61. 0.024 0.024 ↑ 1.0 77 1

Seq Scan on latogato l (cost=0.00..3.77 rows=77 width=19) (actual time=0.011..0.024 rows=77 loops=1)