explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vfHY

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 2,870.352 ↓ 0.0 0 1

Sort (cost=501,909.03..501,991.67 rows=33,056 width=22) (actual time=2,870.352..2,870.352 rows=0 loops=1)

  • Sort Key: p.name
  • Sort Method: quicksort Memory: 25kB
2. 0.057 2,870.346 ↓ 0.0 0 1

Hash Join (cost=302,063.54..499,427.75 rows=33,056 width=22) (actual time=2,870.346..2,870.346 rows=0 loops=1)

  • Hash Cond: ((p.name_id)::text = (rn.name_id)::text)
3. 0.012 0.012 ↑ 9,117,642.0 1 1

Seq Scan on people p (cost=0.00..173,430.38 rows=9,117,642 width=24) (actual time=0.012..0.012 rows=1 loops=1)

  • Filter: (death_year IS NULL)
4. 0.000 2,870.277 ↓ 0.0 0 1

Hash (cost=301,642.43..301,642.43 rows=33,689 width=18) (actual time=2,870.277..2,870.277 rows=0 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 512kB
5. 0.001 2,870.277 ↓ 0.0 0 1

Subquery Scan on rn (cost=300,042.22..301,642.43 rows=33,689 width=18) (actual time=2,870.277..2,870.277 rows=0 loops=1)

6. 642.264 2,870.276 ↓ 0.0 0 1

Finalize HashAggregate (cost=300,042.22..301,305.54 rows=33,689 width=18) (actual time=2,870.275..2,870.276 rows=0 loops=1)

  • Group Key: pt.name_id
  • Filter: (count(*) > 10)
  • Rows Removed by Filter: 1346101
7. 192.871 2,228.012 ↓ 16.1 1,352,616 1

Gather (cost=290,567.24..299,410.55 rows=84,222 width=18) (actual time=1,936.546..2,228.012 rows=1,352,616 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 336.806 2,035.141 ↓ 10.7 450,872 3

Partial HashAggregate (cost=289,567.24..289,988.35 rows=42,111 width=18) (actual time=1,934.237..2,035.141 rows=450,872 loops=3)

  • Group Key: pt.name_id
9. 299.695 1,698.335 ↓ 17.6 739,585 3

Parallel Hash Join (cost=113,513.39..289,356.69 rows=42,111 width=10) (actual time=248.903..1,698.335 rows=739,585 loops=3)

  • Hash Cond: ((r.title_id)::text = (t.title_id)::text)
10. 785.998 1,196.134 ↓ 2.3 1,103,709 3

Parallel Hash Join (cost=12,092.44..186,679.60 rows=478,531 width=30) (actual time=45.285..1,196.134 rows=1,103,709 loops=3)

  • Hash Cond: ((pt.title_id)::text = (r.title_id)::text)
11. 365.041 365.041 ↑ 1.2 5,004,000 3

Parallel Seq Scan on people_titles pt (cost=0.00..158,167.76 rows=6,254,976 width=20) (actual time=0.006..365.041 rows=5,004,000 loops=3)

12. 5.105 45.095 ↑ 1.3 23,598 3

Parallel Hash (cost=11,722.33..11,722.33 rows=29,609 width=10) (actual time=45.094..45.095 rows=23,598 loops=3)

  • Buckets: 131072 Batches: 1 Memory Usage: 4416kB
13. 39.990 39.990 ↑ 1.3 23,598 3

Parallel Seq Scan on ratings r (cost=0.00..11,722.33 rows=29,609 width=10) (actual time=0.009..39.990 rows=23,598 loops=3)

  • Filter: ((rating > '5'::numeric) AND (votes > 500))
  • Rows Removed by Filter: 286020
14. 36.639 202.506 ↑ 1.2 172,140 3

Parallel Hash (cost=98,749.17..98,749.17 rows=213,742 width=10) (actual time=202.506..202.506 rows=172,140 loops=3)

  • Buckets: 524288 Batches: 1 Memory Usage: 28384kB
15. 165.867 165.867 ↑ 1.2 172,140 3

Parallel Seq Scan on titles t (cost=0.00..98,749.17 rows=213,742 width=10) (actual time=0.009..165.867 rows=172,140 loops=3)

  • Filter: (kind_id = 2)
  • Rows Removed by Filter: 1769749
Planning time : 0.618 ms
Execution time : 2,886.327 ms