explain.depesz.com

PostgreSQL's explain analyze made readable

Result: H9Nr

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 37,587.880 ↑ 1.0 10 1

Limit (cost=1,506.82..1,506.85 rows=10 width=324) (actual time=37,587.878..37,587.880 rows=10 loops=1)

2. 2,766.190 37,587.876 ↑ 20.0 10 1

Sort (cost=1,506.82..1,507.32 rows=200 width=324) (actual time=37,587.875..37,587.876 rows=10 loops=1)

  • Sort Key: (sum(remote_scan.revenue)) DESC
  • Sort Method: top-N heapsort Memory: 27kB
3. 16,546.252 34,821.686 ↓ 67,945.7 13,589,138 1

HashAggregate (cost=1,500.00..1,502.50 rows=200 width=324) (actual time=27,644.087..34,821.686 rows=13,589,138 loops=1)

  • Group Key: remote_scan.c_id, remote_scan.c_last, remote_scan.c_city, remote_scan.c_phone, remote_scan.n_name
4. 12,927.896 18,275.434 ↓ 136.5 13,651,930 1

Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=100,000 width=324) (actual time=17,572.207..18,275.434 rows=13,651,930 loops=1)

  • Task Count: 128
  • Tuple data received from nodes: 1,018 MB
  • Tasks Shown: One of 128
  • -> Task
  • Tuple data received from node: 10 MB
  • Node: host=worker1 port=5,432 dbname=postgres
5. 1,256.696 5,347.538 ↑ 2.2 136,420 1

HashAggregate (cost=106,333.92..110,119.45 rows=302,842 width=109) (actual time=5,274.910..5,347.538 rows=136,420 loops=1)

  • Group Key: customer.c_id, customer.c_last, customer.c_city, customer.c_phone, nation.n_name
  • JIT:
  • Functions: 41
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 5.533 ms, Inlining 0.000 ms, Optimization 1.871 ms, Emission 39.514 ms, Total 46.918 ms
6. 543.944 4,090.842 ↓ 6.9 2,098,136 1

Nested Loop (cost=19,443.34..101,791.29 rows=302,842 width=82) (actual time=378.243..4,090.842 rows=2,098,136 loops=1)

  • Join Filter: ((customer.c_w_id = order_line.ol_w_id) AND (customer.c_d_id = order_line.ol_d_id))
7. 142.468 846.898 ↓ 3.2 300,000 1

Hash Join (cost=19,442.91..27,079.47 rows=92,626 width=110) (actual time=378.181..846.898 rows=300,000 loops=1)

  • Hash Cond: (ascii(substr((customer.c_state)::text, 1, 1)) = nation.n_nationkey)
8. 297.045 662.650 ↓ 1.0 300,000 1

Hash Join (cost=19,440.51..26,159.03 rows=298,794 width=87) (actual time=336.362..662.650 rows=300,000 loops=1)

  • Hash Cond: ((orders.o_c_id = customer.c_id) AND (orders.o_w_id = customer.c_w_id) AND (orders.o_d_id = customer.c_d_id))
9. 30.851 30.851 ↑ 1.0 300,000 1

Seq Scan on orders_102779 orders (cost=0.00..4,356.00 rows=300,000 width=28) (actual time=0.017..30.851 rows=300,000 loops=1)

10. 138.439 334.754 ↓ 1.0 300,000 1

Hash (cost=14,191.46..14,191.46 rows=299,946 width=64) (actual time=334.754..334.754 rows=300,000 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 32,237kB
11. 196.315 196.315 ↓ 1.0 300,000 1

Seq Scan on customer_102011 customer (cost=0.00..14,191.46 rows=299,946 width=64) (actual time=0.020..196.315 rows=300,000 loops=1)

12. 0.023 41.780 ↑ 1.0 62 1

Hash (cost=1.62..1.62 rows=62 width=30) (actual time=41.780..41.780 rows=62 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
13. 41.757 41.757 ↑ 1.0 62 1

Seq Scan on nation_102009 nation (cost=0.00..1.62 rows=62 width=30) (actual time=41.744..41.757 rows=62 loops=1)

14. 2,700.000 2,700.000 ↓ 2.3 7 300,000

Index Scan using order_line_i1_102907 on order_line_102907 order_line (cost=0.43..0.76 rows=3 width=28) (actual time=0.007..0.009 rows=7 loops=300,000)

  • Index Cond: ((ol_w_id = orders.o_w_id) AND (ol_d_id = orders.o_d_id) AND (ol_o_id = orders.o_id))
  • Filter: (orders.o_entry_d <= ol_delivery_d)
  • Rows Removed by Filter: 3
Planning time : 14.675 ms
Execution time : 37,829.430 ms