explain.depesz.com

PostgreSQL's explain analyze made readable

Result: notB

Settings
# exclusive inclusive rows x rows loops node
1. 169,371.859 2,121,984.362 ↓ 1.3 8 1

HashAggregate (cost=15,041,022.08..15,041,022.54 rows=6 width=357) (actual time=2,121,984.339..2,121,984.362 rows=8 loops=1)

2. 46,464.036 1,952,612.503 ↓ 1.3 26,609,102 1

Hash Left Join (cost=13,506,928.79..14,448,475.64 rows=19,751,548 width=357) (actual time=1,473,782.113..1,952,612.503 rows=26,609,102 loops=1)

  • Hash Cond: (p.um_product_department_id = d.um_product_department_id)
3. 46,661.588 1,906,121.391 ↓ 1.3 26,609,102 1

Hash Join (cost=13,506,927.41..14,177,160.41 rows=19,751,548 width=228) (actual time=1,473,755.005..1,906,121.391 rows=26,609,102 loops=1)

  • Hash Cond: (i.m_locator_id = l.m_locator_id)
4. 40,934.199 1,859,448.811 ↓ 1.3 26,609,102 1

Merge Right Join (cost=13,506,926.02..13,905,575.24 rows=19,751,548 width=139) (actual time=1,473,743.989..1,859,448.811 rows=26,609,102 loops=1)

  • Merge Cond: (po.m_product_id = i.m_product_id)
5. 331.847 454.018 ↑ 1.0 46,926 1

Sort (cost=7,618.48..7,736.60 rows=47,246 width=9) (actual time=299.973..454.018 rows=46,926 loops=1)

  • Sort Key: po.m_product_id
  • Sort Method: external merge Disk: 872kB
6. 122.171 122.171 ↑ 1.0 46,929 1

Seq Scan on m_product_po po (cost=0.00..3,222.32 rows=47,246 width=9) (actual time=10.789..122.171 rows=46,929 loops=1)

  • Filter: (iscurrentvendor = 'Y'::bpchar)
7. 51,902.609 1,818,060.594 ↓ 3.0 26,609,102 1

Materialize (cost=13,499,307.54..13,623,611.01 rows=8,865,398 width=148) (actual time=1,473,443.997..1,818,060.594 rows=26,609,102 loops=1)

8. 76,583.037 1,766,157.985 ↓ 3.0 26,596,535 1

Merge Left Join (cost=13,499,307.54..13,601,447.51 rows=8,865,398 width=148) (actual time=1,473,443.982..1,766,157.985 rows=26,596,535 loops=1)

  • Merge Cond: ((p.m_product_id = trxsp.m_product_id) AND (i.m_attributesetinstance_id = trxsp.m_attributesetinstance_id))
9. 663,126.829 803,282.152 ↓ 3.0 26,596,535 1

Sort (cost=3,919,123.56..3,941,287.05 rows=8,865,398 width=69) (actual time=604,957.840..803,282.152 rows=26,596,535 loops=1)

  • Sort Key: p.m_product_id, i.m_attributesetinstance_id
  • Sort Method: external merge Disk: 2081856kB
10. 67,994.174 140,155.323 ↓ 3.0 26,596,535 1

Hash Join (cost=16,070.20..1,441,579.53 rows=8,865,398 width=69) (actual time=776.301..140,155.323 rows=26,596,535 loops=1)

  • Hash Cond: (i.m_product_id = p.m_product_id)
11. 71,404.942 71,404.942 ↓ 3.0 26,596,535 1

Seq Scan on m_transaction i (cost=0.00..1,083,406.38 rows=8,865,398 width=52) (actual time=19.981..71,404.942 rows=26,596,535 loops=1)

  • Filter: ((ad_client_id = 1000001::numeric) AND ((movementdate)::date <= '2019-05-12'::date))
12. 44.776 756.207 ↑ 1.0 47,040 1

Hash (cost=15,182.90..15,182.90 rows=47,064 width=25) (actual time=756.207..756.207 rows=47,040 loops=1)

  • Buckets: 4096 Batches: 4 Memory Usage: 546kB
13. 104.552 711.431 ↑ 1.0 47,040 1

Hash Join (cost=9,427.98..15,182.90 rows=47,064 width=25) (actual time=518.391..711.431 rows=47,040 loops=1)

  • Hash Cond: (p.m_product_id = pr.m_product_id)
14. 104.464 104.464 ↑ 1.0 47,040 1

Seq Scan on m_product p (cost=0.00..4,123.64 rows=47,064 width=17) (actual time=15.872..104.464 rows=47,040 loops=1)

15. 39.724 502.415 ↑ 1.0 47,040 1

Hash (cost=8,655.68..8,655.68 rows=47,064 width=8) (actual time=502.415..502.415 rows=47,040 loops=1)

  • Buckets: 4096 Batches: 2 Memory Usage: 669kB
16. 70.586 462.691 ↑ 1.0 47,040 1

Unique (cost=8,420.36..8,655.68 rows=47,064 width=8) (actual time=347.987..462.691 rows=47,040 loops=1)

17. 243.916 392.105 ↑ 1.0 47,040 1

Sort (cost=8,420.36..8,538.02 rows=47,064 width=8) (actual time=347.983..392.105 rows=47,040 loops=1)

  • Sort Key: pr.m_product_id
  • Sort Method: external merge Disk: 872kB
18. 148.189 148.189 ↑ 1.0 47,040 1

Seq Scan on m_product pr (cost=0.00..4,123.64 rows=47,064 width=8) (actual time=0.004..148.189 rows=47,040 loops=1)

19. 17,250.901 886,292.796 ↓ 10.3 26,596,531 1

Materialize (cost=9,580,183.98..9,593,145.19 rows=2,592,243 width=122) (actual time=868,441.893..886,292.796 rows=26,596,531 loops=1)

20. 1,150.735 869,041.895 ↑ 30.0 86,538 1

Sort (cost=9,580,183.98..9,586,664.59 rows=2,592,243 width=122) (actual time=868,441.876..869,041.895 rows=86,538 loops=1)

  • Sort Key: trxsp.m_product_id, trxsp.m_attributesetinstance_id
  • Sort Method: external merge Disk: 4280kB
21. 134.555 867,891.160 ↑ 30.0 86,538 1

Subquery Scan on trxsp (cost=8,379,658.29..8,812,288.82 rows=2,592,243 width=122) (actual time=533,592.889..867,891.160 rows=86,538 loops=1)

22. 268,849.833 867,756.605 ↑ 30.0 86,538 1

Hash Join (cost=8,379,658.29..8,786,366.39 rows=2,592,243 width=47) (actual time=533,592.887..867,756.605 rows=86,538 loops=1)

  • Hash Cond: (maxmt.m_transaction_id = mt.m_transaction_id)
23.          

CTE maxmt

24. 28,557.522 409,050.470 ↑ 30.0 86,538 1

GroupAggregate (cost=6,689,833.69..6,981,460.95 rows=2,592,243 width=29) (actual time=338,415.063..409,050.470 rows=86,538 loops=1)

25. 310,086.443 380,492.948 ↓ 1.0 25,926,993 1

Sort (cost=6,689,833.69..6,754,639.75 rows=25,922,422 width=29) (actual time=338,254.513..380,492.948 rows=25,926,993 loops=1)

  • Sort Key: m_transaction.m_product_id, m_transaction.m_attributesetinstance_id
  • Sort Method: external merge Disk: 987960kB
26. 70,406.505 70,406.505 ↓ 1.0 25,926,993 1

Seq Scan on m_transaction (cost=0.00..1,016,915.90 rows=25,922,422 width=29) (actual time=119.851..70,406.505 rows=25,926,993 loops=1)

  • Filter: ((m_movementline_id IS NULL) AND (m_productionline_id IS NULL) AND (movementdate <= to_date('2019-05-12 00:00:00.0'::text, 'yyyy-MM-dd'::text)))
27. 409,211.870 409,211.870 ↑ 30.0 86,538 1

CTE Scan on maxmt (cost=0.00..51,844.86 rows=2,592,243 width=58) (actual time=338,415.070..409,211.870 rows=86,538 loops=1)

28. 54,471.914 189,694.902 ↓ 1.0 26,596,535 1

Hash (cost=883,934.93..883,934.93 rows=26,596,193 width=32) (actual time=189,694.902..189,694.902 rows=26,596,535 loops=1)

  • Buckets: 2048 Batches: 2048 Memory Usage: 702kB
29. 135,222.988 135,222.988 ↓ 1.0 26,596,535 1

Seq Scan on m_transaction mt (cost=0.00..883,934.93 rows=26,596,193 width=32) (actual time=16.290..135,222.988 rows=26,596,535 loops=1)

30. 0.046 10.992 ↓ 1.4 23 1

Hash (cost=1.17..1.17 rows=17 width=111) (actual time=10.992..10.992 rows=23 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
31. 10.946 10.946 ↓ 1.4 23 1

Seq Scan on m_locator l (cost=0.00..1.17 rows=17 width=111) (actual time=10.919..10.946 rows=23 loops=1)

32. 0.032 27.076 ↑ 1.0 17 1

Hash (cost=1.17..1.17 rows=17 width=151) (actual time=27.076..27.076 rows=17 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
33. 27.044 27.044 ↑ 1.0 17 1

Seq Scan on um_product_department d (cost=0.00..1.17 rows=17 width=151) (actual time=27.025..27.044 rows=17 loops=1)