explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fNGN

Settings
# exclusive inclusive rows x rows loops node
1. 37.890 2,694.389 ↑ 10.8 5,000 1

Hash Left Join (cost=96,322.72..99,883.87 rows=54,000 width=172) (actual time=2,661.196..2,694.389 rows=5,000 loops=1)

  • Hash Cond: ((c.category_id = r2.ct_id_temp) AND (s.state_id = r2.st_id_temp))
2. 0.496 0.779 ↑ 10.8 5,000 1

Nested Loop (cost=0.00..692.65 rows=54,000 width=132) (actual time=0.064..0.779 rows=5,000 loops=1)

3. 0.033 0.033 ↑ 10.8 50 1

Seq Scan on state s (cost=0.00..15.40 rows=540 width=122) (actual time=0.027..0.033 rows=50 loops=1)

4. 0.213 0.250 ↑ 1.0 100 50

Materialize (cost=0.00..2.50 rows=100 width=10) (actual time=0.001..0.005 rows=100 loops=50)

5. 0.037 0.037 ↑ 1.0 100 1

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

6. 10.295 2,655.720 ↑ 10.8 5,000 1

Hash (cost=95,037.72..95,037.72 rows=54,000 width=48) (actual time=2,655.720..2,655.720 rows=5,000 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 650kB
7. 1.154 2,645.425 ↑ 10.8 5,000 1

Subquery Scan on r2 (cost=72,039.52..95,037.72 rows=54,000 width=48) (actual time=2,175.639..2,645.425 rows=5,000 loops=1)

8. 21.992 2,644.271 ↑ 10.8 5,000 1

Finalize GroupAggregate (cost=72,039.52..94,497.72 rows=54,000 width=48) (actual time=2,175.639..2,644.271 rows=5,000 loops=1)

  • Group Key: ct.category_id, st.state_id
9. 0.000 2,622.279 ↑ 7.2 15,000 1

Gather Merge (cost=72,039.52..92,472.72 rows=108,000 width=48) (actual time=2,175.553..2,622.279 rows=15,000 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
10. 898.284 7,434.342 ↑ 10.8 5,000 3

Partial GroupAggregate (cost=71,039.50..79,006.82 rows=54,000 width=48) (actual time=2,061.017..2,478.114 rows=5,000 loops=3)

  • Group Key: ct.category_id, st.state_id
11. 2,160.432 6,536.058 ↑ 1.3 333,333 3

Sort (cost=71,039.50..72,081.26 rows=416,704 width=17) (actual time=2,060.908..2,178.686 rows=333,333 loops=3)

  • Sort Key: ct.category_id, st.state_id
  • Sort Method: external merge Disk: 9544kB
  • Worker 0: Sort Method: external merge Disk: 8320kB
  • Worker 1: Sort Method: external merge Disk: 8632kB
12. 524.847 4,375.626 ↑ 1.3 333,333 3

Hash Join (cost=3,348.90..23,595.96 rows=416,704 width=17) (actual time=97.376..1,458.542 rows=333,333 loops=3)

  • Hash Cond: (p.category_id = ct.category_id)
13. 778.422 3,849.696 ↑ 1.3 333,333 3

Hash Join (cost=3,345.65..22,452.51 rows=416,704 width=17) (actual time=96.977..1,283.232 rows=333,333 loops=3)

  • Hash Cond: (s_1.product_id = p.product_id)
14. 484.062 3,068.817 ↑ 1.3 333,333 3

Hash Join (cost=3,315.15..21,323.52 rows=416,704 width=17) (actual time=96.136..1,022.939 rows=333,333 loops=3)

  • Hash Cond: (c_1.state_id = st.state_id)
15. 1,715.946 2,583.810 ↑ 1.3 333,333 3

Hash Join (cost=3,293.00..20,198.93 rows=416,704 width=17) (actual time=95.796..861.270 rows=333,333 loops=3)

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

Parallel Seq Scan on sale s_1 (cost=0.00..10,537.04 rows=416,704 width=17) (actual time=0.019..197.148 rows=333,333 loops=3)

17. 180.354 276.420 ↑ 1.0 100,000 3

Hash (cost=1,652.00..1,652.00 rows=100,000 width=8) (actual time=92.140..92.140 rows=100,000 loops=3)

  • Buckets: 131072 Batches: 2 Memory Usage: 2976kB
18. 96.066 96.066 ↑ 1.0 100,000 3

Seq Scan on customer c_1 (cost=0.00..1,652.00 rows=100,000 width=8) (actual time=0.299..32.022 rows=100,000 loops=3)

19. 0.081 0.945 ↑ 10.8 50 3

Hash (cost=15.40..15.40 rows=540 width=4) (actual time=0.315..0.315 rows=50 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
20. 0.864 0.864 ↑ 10.8 50 3

Seq Scan on state st (cost=0.00..15.40 rows=540 width=4) (actual time=0.281..0.288 rows=50 loops=3)

21. 0.774 2.457 ↑ 1.0 1,000 3

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

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

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

23. 0.123 1.083 ↑ 1.0 100 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
24. 0.960 0.960 ↑ 1.0 100 3

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