explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rYHP

Settings
# exclusive inclusive rows x rows loops node
1. 0.157 1,508.220 ↑ 51,342,217.1 1,431 1

Append (cost=10,506.73..15,980,857,513.87 rows=73,470,712,710 width=228) (actual time=106.781..1,508.220 rows=1,431 loops=1)

2. 0.000 393.614 ↑ 1.3 1,423 1

Subquery Scan on *SELECT* 1 (cost=10,506.73..48,507.41 rows=1,898 width=75) (actual time=106.780..393.614 rows=1,423 loops=1)

3. 5.567 393.708 ↑ 1.3 1,423 1

Gather (cost=10,506.73..48,474.19 rows=1,898 width=63) (actual time=106.776..393.708 rows=1,423 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 16.835 388.141 ↑ 1.7 474 3 / 3

Parallel Hash Join (cost=9,506.73..47,284.39 rows=791 width=63) (actual time=101.792..388.141 rows=474 loops=3)

  • Hash Cond: (f0_2.l_orderkey = f0_1.o_orderkey)
5. 270.002 270.002 ↓ 2.3 90,568 3 / 3

Parallel Seq Scan on lineitem_radb f0_2 (cost=0.00..37,582.94 rows=39,445 width=34) (actual time=0.046..270.002 rows=90,568 loops=3)

  • Filter: ((l_orderkey IS NOT NULL) AND (lb_l_shipdate < '1995-03-17'::text) AND (CASE WHEN (l_shipdate < '1995-03-17'::text) THEN 1 ELSE 0 END > 0))
  • Rows Removed by Filter: 109623
6. 6.034 101.304 ↓ 4.3 5,313 3 / 3

Parallel Hash (cost=9,491.26..9,491.26 rows=1,238 width=95) (actual time=101.304..101.304 rows=5,313 loops=3)

  • Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 2368kB
7. 4.885 95.270 ↓ 4.3 5,313 3 / 3

Parallel Hash Join (cost=1,225.77..9,491.26 rows=1,238 width=95) (actual time=10.072..95.270 rows=5,313 loops=3)

  • Hash Cond: (f0_1.o_custkey = f0.c_custkey)
8. 80.462 80.462 ↓ 2.4 26,163 3 / 3

Parallel Seq Scan on orders_radb f0_1 (cost=0.00..8,203.75 rows=10,891 width=63) (actual time=0.050..80.462 rows=26,163 loops=3)

  • Filter: ((o_orderkey IS NOT NULL) AND (o_custkey IS NOT NULL) AND (o_orderdate IS NOT NULL) AND (o_shippriority IS NOT NULL) AND (ub_o_orderdate > '1995-03-15'::text) AND (CASE WHEN (o_orderdate > '1995-03-15'::text) THEN 1 ELSE 0 END > 0))
  • Rows Removed by Filter: 23837
9. 1.111 9.923 ↓ 1.0 1,019 3 / 3

Parallel Hash (cost=1,213.47..1,213.47 rows=984 width=40) (actual time=9.923..9.923 rows=1,019 loops=3)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 304kB
10. 8.812 8.812 ↓ 1.0 1,019 3 / 3

Parallel Seq Scan on customer_radb f0 (cost=0.00..1,213.47 rows=984 width=40) (actual time=0.461..8.812 rows=1,019 loops=3)

  • Filter: ((c_custkey IS NOT NULL) AND (lb_c_mktsegment <= 'BUILDING'::text) AND (ub_c_mktsegment >= 'BUILDING'::text) AND (CASE WHEN (c_mktsegment = 'BUILDING'::text) THEN 1 ELSE 0 END > 0))
  • Rows Removed by Filter: 3981
11. 0.011 1,114.449 ↑ 9,183,838,851.5 8 1

Nested Loop (cost=61,907.97..14,878,748,334.79 rows=73,470,710,812 width=228) (actual time=1,114.008..1,114.449 rows=8 loops=1)

  • Join Filter: (((min(f0_4.lb_o_orderkey)) <= f1.ub_l_orderkey) AND ((max(f0_4.ub_o_orderkey)) >= f1.lb_l_orderkey))
12. 0.006 349.350 ↑ 1,370,551.8 4 1

Nested Loop (cost=14,097.12..877,966.12 rows=5,482,207 width=276) (actual time=349.303..349.350 rows=4 loops=1)

  • Join Filter: ((f0_3.lb_c_custkey <= (max(f0_4.ub_o_custkey))) AND (f0_3.ub_c_custkey >= (min(f0_4.lb_o_custkey))))
13. 205.583 336.526 ↑ 4,933.0 2 1

HashAggregate (cost=12,709.62..12,882.27 rows=9,866 width=240) (actual time=336.495..336.526 rows=2 loops=1)

  • Group Key: CASE WHEN (f0_4.o_custkey >= 7500) THEN 7500 WHEN ((f0_4.o_custkey < 7500) AND (f0_4.o_custkey >= 1)) THEN 1 ELSE 1 END
14. 130.943 130.943 ↓ 1.0 78,490 1

Seq Scan on orders_radb f0_4 (cost=0.00..9,573.10 rows=78,413 width=63) (actual time=0.039..130.943 rows=78,490 loops=1)

  • Filter: (ub_o_orderdate > '1995-03-15'::text)
  • Rows Removed by Filter: 71510
15. 0.003 12.818 ↑ 2,500.5 2 2

Materialize (cost=1,387.50..1,550.03 rows=5,001 width=36) (actual time=6.403..6.409 rows=2 loops=2)

16. 0.002 12.815 ↑ 2,500.5 2 1

Subquery Scan on f0_3 (cost=1,387.50..1,525.03 rows=5,001 width=36) (actual time=12.802..12.815 rows=2 loops=1)

17. 0.594 12.813 ↑ 2,500.5 2 1

HashAggregate (cost=1,387.50..1,475.02 rows=5,001 width=36) (actual time=12.802..12.813 rows=2 loops=1)

  • Group Key: CASE WHEN (f0_5.c_custkey >= 7500) THEN 7500 WHEN ((f0_5.c_custkey < 7500) AND (f0_5.c_custkey >= 1)) THEN 1 ELSE 1 END
18. 12.219 12.219 ↑ 1.5 3,297 1

Seq Scan on customer_radb f0_5 (cost=0.00..1,299.65 rows=5,020 width=21) (actual time=0.015..12.219 rows=3,297 loops=1)

  • Filter: ((lb_c_mktsegment <= 'BUILDING'::text) AND (ub_c_mktsegment >= 'BUILDING'::text))
  • Rows Removed by Filter: 11703
19. 0.009 765.088 ↑ 60,307.5 2 4

Materialize (cost=47,810.85..51,730.84 rows=120,615 width=36) (actual time=191.175..191.272 rows=2 loops=4)

20. 0.001 765.079 ↑ 60,307.5 2 1

Subquery Scan on f1 (cost=47,810.85..51,127.76 rows=120,615 width=36) (actual time=764.693..765.079 rows=2 loops=1)

21. 209.677 765.078 ↑ 60,307.5 2 1

HashAggregate (cost=47,810.85..49,921.61 rows=120,615 width=36) (actual time=764.692..765.078 rows=2 loops=1)

  • Group Key: CASE WHEN (f0_6.l_orderkey >= 300000) THEN 300000 WHEN ((f0_6.l_orderkey < 300000) AND (f0_6.l_orderkey >= 1)) THEN 1 ELSE 1 END
22. 555.401 555.401 ↑ 1.0 282,108 1

Seq Scan on lineitem_radb f0_6 (cost=0.00..42,840.78 rows=284,004 width=23) (actual time=0.017..555.401 rows=282,108 loops=1)

  • Filter: (lb_l_shipdate < '1995-03-17'::text)
  • Rows Removed by Filter: 318464