explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JgKr

Settings
# exclusive inclusive rows x rows loops node
1. 3.493 1,426.309 ↑ 1.0 5,000 1

Hash Left Join (cost=27,767.53..27,859.91 rows=5,000 width=172) (actual time=1,421.223..1,426.309 rows=5,000 loops=1)

  • Hash Cond: ((c.category_id = r2.ct_id_temp) AND (s.state_id = r2.st_id_temp))
2. 1.242 1.669 ↑ 1.0 5,000 1

Nested Loop (cost=0.00..66.13 rows=5,000 width=132) (actual time=0.036..1.669 rows=5,000 loops=1)

3. 0.027 0.027 ↑ 1.0 100 1

Seq Scan on category c (cost=0.00..2.00 rows=100 width=10) (actual time=0.018..0.027 rows=100 loops=1)

4. 0.384 0.400 ↑ 1.0 50 100

Materialize (cost=0.00..1.75 rows=50 width=122) (actual time=0.000..0.004 rows=50 loops=100)

5. 0.016 0.016 ↑ 1.0 50 1

Seq Scan on state s (cost=0.00..1.50 rows=50 width=122) (actual time=0.010..0.016 rows=50 loops=1)

6. 1.970 1,421.147 ↑ 1.0 5,000 1

Hash (cost=27,692.53..27,692.53 rows=5,000 width=48) (actual time=1,421.147..1,421.147 rows=5,000 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 338kB
7. 2.129 1,419.177 ↑ 1.0 5,000 1

Subquery Scan on r2 (cost=27,580.03..27,692.53 rows=5,000 width=48) (actual time=1,412.854..1,419.177 rows=5,000 loops=1)

8. 27.457 1,417.048 ↑ 1.0 5,000 1

Finalize HashAggregate (cost=27,580.03..27,642.53 rows=5,000 width=48) (actual time=1,412.853..1,417.048 rows=5,000 loops=1)

  • Group Key: ct.category_id, st.state_id
9. 84.959 1,389.591 ↓ 1.5 15,000 1

Gather (cost=26,392.53..27,455.03 rows=10,000 width=48) (actual time=1,379.913..1,389.591 rows=15,000 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
10. 456.536 1,304.632 ↑ 1.0 5,000 3

Partial HashAggregate (cost=25,392.53..25,455.03 rows=5,000 width=48) (actual time=1,299.111..1,304.632 rows=5,000 loops=3)

  • Group Key: ct.category_id, st.state_id
11. 148.875 848.096 ↑ 1.3 333,333 3

Hash Join (cost=4,087.11..19,142.53 rows=416,667 width=17) (actual time=39.117..848.096 rows=333,333 loops=3)

  • Hash Cond: (p.category_id = ct.category_id)
12. 166.336 698.828 ↑ 1.3 333,333 3

Hash Join (cost=4,083.86..17,999.17 rows=416,667 width=17) (actual time=38.696..698.828 rows=333,333 loops=3)

  • Hash Cond: (s_1.product_id = p.product_id)
13. 148.767 531.669 ↑ 1.3 333,333 3

Hash Join (cost=4,053.36..16,870.28 rows=416,667 width=17) (actual time=37.853..531.669 rows=333,333 loops=3)

  • Hash Cond: (c_1.state_id = st.state_id)
14. 274.203 382.564 ↑ 1.3 333,333 3

Parallel Hash Join (cost=4,051.23..15,681.70 rows=416,667 width=17) (actual time=37.497..382.564 rows=333,333 loops=3)

  • Hash Cond: (s_1.customer_id = c_1.customer_id)
15. 71.779 71.779 ↑ 1.3 333,333 3

Parallel Seq Scan on sale s_1 (cost=0.00..10,536.67 rows=416,667 width=17) (actual time=0.015..71.779 rows=333,333 loops=3)

16. 3.586 36.582 ↑ 1.3 33,333 3

Parallel Hash (cost=3,530.40..3,530.40 rows=41,667 width=8) (actual time=36.581..36.582 rows=33,333 loops=3)

  • Buckets: 131072 Batches: 1 Memory Usage: 4960kB
17. 32.996 32.996 ↓ 1.2 50,000 2

Parallel Index Only Scan using c_index on customer c_1 (cost=0.29..3,530.40 rows=41,667 width=8) (actual time=0.904..32.996 rows=50,000 loops=2)

  • Heap Fetches: 100000
18. 0.024 0.338 ↑ 1.0 50 3

Hash (cost=1.50..1.50 rows=50 width=4) (actual time=0.338..0.338 rows=50 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
19. 0.314 0.314 ↑ 1.0 50 3

Seq Scan on state st (cost=0.00..1.50 rows=50 width=4) (actual time=0.307..0.314 rows=50 loops=3)

20. 0.262 0.823 ↑ 1.0 1,000 3

Hash (cost=18.00..18.00 rows=1,000 width=8) (actual time=0.823..0.823 rows=1,000 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 48kB
21. 0.561 0.561 ↑ 1.0 1,000 3

Seq Scan on product p (cost=0.00..18.00 rows=1,000 width=8) (actual time=0.294..0.561 rows=1,000 loops=3)

22. 0.047 0.393 ↑ 1.0 100 3

Hash (cost=2.00..2.00 rows=100 width=4) (actual time=0.393..0.393 rows=100 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
23. 0.346 0.346 ↑ 1.0 100 3

Seq Scan on category ct (cost=0.00..2.00 rows=100 width=4) (actual time=0.330..0.346 rows=100 loops=3)