explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Syfb

Settings
# exclusive inclusive rows x rows loops node
1. 7.171 464.863 ↓ 53.4 10,675 1

Unique (cost=3,673,396.67..4,064,962.14 rows=200 width=8) (actual time=437.027..464.863 rows=10,675 loops=1)

2.          

CTE disc

3. 15.383 130.603 ↑ 9.7 20,909 1

Merge Right Join (cost=1,478.20..4,835.25 rows=202,234 width=28) (actual time=76.269..130.603 rows=20,909 loops=1)

  • Merge Cond: (dc.discountid = d.discountid)
4. 0.002 0.006 ↓ 0.0 0 1

Sort (cost=142.54..147.64 rows=2,040 width=8) (actual time=0.006..0.006 rows=0 loops=1)

  • Sort Key: dc.discountid
  • Sort Method: quicksort Memory: 25kB
5. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on ecomm_discountcategories dc (cost=0.00..30.40 rows=2,040 width=8) (actual time=0.003..0.004 rows=0 loops=1)

6. 15.581 115.214 ↓ 1.1 20,909 1

Materialize (cost=1,335.66..1,698.55 rows=19,827 width=28) (actual time=76.261..115.214 rows=20,909 loops=1)

7. 15.186 99.633 ↓ 1.1 20,909 1

Merge Left Join (cost=1,335.66..1,648.98 rows=19,827 width=28) (actual time=76.258..99.633 rows=20,909 loops=1)

  • Merge Cond: (d.discountid = db.discountid)
8. 16.906 84.437 ↓ 10.5 20,909 1

Sort (cost=1,193.12..1,198.07 rows=1,983 width=24) (actual time=76.245..84.437 rows=20,909 loops=1)

  • Sort Key: d.discountid
  • Sort Method: quicksort Memory: 2,402kB
9. 36.662 67.531 ↓ 10.5 20,909 1

Hash Right Join (cost=2.49..1,084.51 rows=1,983 width=24) (actual time=43.377..67.531 rows=20,909 loops=1)

  • Hash Cond: (ds.discountid = d.discountid)
10. 30.828 30.828 ↓ 2.7 86,140 1

Seq Scan on ecomm_discountskus ds (cost=0.00..943.23 rows=31,723 width=8) (actual time=0.016..30.828 rows=86,140 loops=1)

11. 0.003 0.041 ↓ 2.0 2 1

Hash (cost=2.48..2.48 rows=1 width=20) (actual time=0.041..0.041 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
12. 0.038 0.038 ↓ 2.0 2 1

Seq Scan on ecomm_discounts d (cost=0.00..2.48 rows=1 width=20) (actual time=0.035..0.038 rows=2 loops=1)

  • Filter: (isactive AND ((startdate)::date <= ('now'::cstring)::date) AND ((enddate)::date >= ('now'::cstring)::date))
  • Rows Removed by Filter: 51
13. 0.005 0.010 ↓ 0.0 0 1

Sort (cost=142.54..147.64 rows=2,040 width=8) (actual time=0.010..0.010 rows=0 loops=1)

  • Sort Key: db.discountid
  • Sort Method: quicksort Memory: 25kB
14. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on ecomm_discountbrands db (cost=0.00..30.40 rows=2,040 width=8) (actual time=0.004..0.005 rows=0 loops=1)

15.          

CTE prod

16. 15.691 57.296 ↓ 2.5 18,166 1

Hash Join (cost=2,848.03..4,198.62 rows=7,376 width=16) (actual time=27.002..57.296 rows=18,166 loops=1)

  • Hash Cond: (s2.productid = p3.productid)
17. 14.773 14.773 ↓ 2.1 22,346 1

Seq Scan on ecomm_skus s2 (cost=0.00..1,237.23 rows=10,562 width=8) (actual time=0.018..14.773 rows=22,346 loops=1)

  • Filter: isactive
  • Rows Removed by Filter: 1,824
18. 5.674 26.832 ↑ 1.5 12,891 1

Hash (cost=2,605.65..2,605.65 rows=19,390 width=12) (actual time=26.832..26.832 rows=12,891 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 810kB
19. 21.158 21.158 ↑ 1.5 12,891 1

Seq Scan on ecomm_products p3 (cost=0.00..2,605.65 rows=19,390 width=12) (actual time=0.012..21.158 rows=12,891 loops=1)

  • Filter: (isactive AND visible)
  • Rows Removed by Filter: 4,316
20. 6.659 457.692 ↑ 2,096.0 10,675 1

Subquery Scan on x (cost=3,664,362.80..3,999,990.35 rows=22,375,170 width=8) (actual time=437.025..457.692 rows=10,675 loops=1)

21. 8.486 451.033 ↑ 2,096.0 10,675 1

Unique (cost=3,664,362.80..3,776,238.65 rows=22,375,170 width=4) (actual time=437.023..451.033 rows=10,675 loops=1)

22. 28.322 442.547 ↑ 1,410.2 15,867 1

Sort (cost=3,664,362.80..3,720,300.72 rows=22,375,170 width=4) (actual time=437.022..442.547 rows=15,867 loops=1)

  • Sort Key: prod.productid
  • Sort Method: quicksort Memory: 1,128kB
23. 47.386 414.225 ↑ 1,410.2 15,867 1

Append (cost=22,488.60..626,955.70 rows=22,375,170 width=4) (actual time=242.110..414.225 rows=15,867 loops=1)

24. 26.331 287.913 ↑ 470.1 15,867 1

Merge Join (cost=22,488.60..134,401.33 rows=7,458,390 width=4) (actual time=242.108..287.913 rows=15,867 loops=1)

  • Merge Cond: (prod.skuid = disc.skuid)
25. 18.058 90.677 ↓ 2.4 17,839 1

Sort (cost=621.38..639.82 rows=7,376 width=8) (actual time=81.488..90.677 rows=17,839 loops=1)

  • Sort Key: prod.skuid
  • Sort Method: quicksort Memory: 1,620kB
26. 72.619 72.619 ↓ 2.5 18,166 1

CTE Scan on prod (cost=0.00..147.52 rows=7,376 width=8) (actual time=27.005..72.619 rows=18,166 loops=1)

27. 21.383 170.905 ↑ 9.7 20,909 1

Sort (cost=21,867.22..22,372.81 rows=202,234 width=4) (actual time=160.467..170.905 rows=20,909 loops=1)

  • Sort Key: disc.skuid
  • Sort Method: quicksort Memory: 1,749kB
28. 149.522 149.522 ↑ 9.7 20,909 1

CTE Scan on disc (cost=0.00..4,044.68 rows=202,234 width=4) (actual time=76.272..149.522 rows=20,909 loops=1)

29. 0.005 47.395 ↓ 0.0 0 1

Merge Join (cost=22,488.60..134,401.33 rows=7,458,390 width=4) (actual time=47.395..47.395 rows=0 loops=1)

  • Merge Cond: (prod_1.brandid = disc_1.brandid)
30. 11.848 23.664 ↑ 7,376.0 1 1

Sort (cost=621.38..639.82 rows=7,376 width=8) (actual time=23.664..23.664 rows=1 loops=1)

  • Sort Key: prod_1.brandid
  • Sort Method: quicksort Memory: 1,620kB
31. 11.816 11.816 ↓ 2.5 18,166 1

CTE Scan on prod prod_1 (cost=0.00..147.52 rows=7,376 width=8) (actual time=0.006..11.816 rows=18,166 loops=1)

32. 11.270 23.726 ↑ 202,234.0 1 1

Sort (cost=21,867.22..22,372.81 rows=202,234 width=4) (actual time=23.725..23.726 rows=1 loops=1)

  • Sort Key: disc_1.brandid
  • Sort Method: quicksort Memory: 1,422kB
33. 12.456 12.456 ↑ 9.7 20,909 1

CTE Scan on disc disc_1 (cost=0.00..4,044.68 rows=202,234 width=4) (actual time=0.002..12.456 rows=20,909 loops=1)

34. 0.004 31.531 ↓ 0.0 0 1

Merge Join (cost=22,488.60..134,401.33 rows=7,458,390 width=4) (actual time=31.530..31.531 rows=0 loops=1)

  • Merge Cond: (prod_2.categoryid = disc_2.categoryid)
35. 7.661 14.874 ↑ 7,376.0 1 1

Sort (cost=621.38..639.82 rows=7,376 width=8) (actual time=14.874..14.874 rows=1 loops=1)

  • Sort Key: prod_2.categoryid
  • Sort Method: quicksort Memory: 1,620kB
36. 7.213 7.213 ↓ 2.5 18,166 1

CTE Scan on prod prod_2 (cost=0.00..147.52 rows=7,376 width=8) (actual time=0.001..7.213 rows=18,166 loops=1)

37. 8.257 16.653 ↑ 202,234.0 1 1

Sort (cost=21,867.22..22,372.81 rows=202,234 width=4) (actual time=16.652..16.653 rows=1 loops=1)

  • Sort Key: disc_2.categoryid
  • Sort Method: quicksort Memory: 1,422kB
38. 8.396 8.396 ↑ 9.7 20,909 1

CTE Scan on disc disc_2 (cost=0.00..4,044.68 rows=202,234 width=4) (actual time=0.002..8.396 rows=20,909 loops=1)

Planning time : 1.890 ms
Execution time : 469.622 ms