explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l8bN

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 3,191.665 ↓ 0.0 0 1

Sort (cost=501,256.51..501,335.99 rows=31,792 width=22) (actual time=3,191.665..3,191.665 rows=0 loops=1)

  • Sort Key: p.name
  • Sort Method: quicksort Memory: 25kB
2. 0.032 3,191.661 ↓ 0.0 0 1

Hash Join (cost=301,504.38..498,879.04 rows=31,792 width=22) (actual time=3,191.660..3,191.661 rows=0 loops=1)

  • Hash Cond: ((p.name_id)::text = (rn.name_id)::text)
3. 0.013 0.013 ↑ 9,121,414.0 1 1

Seq Scan on people p (cost=0.00..173,430.94 rows=9,121,414 width=24) (actual time=0.013..0.013 rows=1 loops=1)

  • Filter: (death_year IS NULL)
4. 0.001 3,191.616 ↓ 0.0 0 1

Hash (cost=301,099.53..301,099.53 rows=32,388 width=18) (actual time=3,191.615..3,191.616 rows=0 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 256kB
5. 0.001 3,191.615 ↓ 0.0 0 1

Subquery Scan on rn (cost=299,561.11..301,099.53 rows=32,388 width=18) (actual time=3,191.614..3,191.615 rows=0 loops=1)

6. 689.738 3,191.614 ↓ 0.0 0 1

Finalize HashAggregate (cost=299,561.11..300,775.65 rows=32,388 width=18) (actual time=3,191.614..3,191.614 rows=0 loops=1)

  • Group Key: pt.name_id
  • Filter: (count(*) > 10)
  • Rows Removed by Filter: 1346101
7. 201.141 2,501.876 ↓ 16.7 1,352,645 1

Gather (cost=290,451.99..298,953.84 rows=80,970 width=18) (actual time=2,199.728..2,501.876 rows=1,352,645 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 347.505 2,300.735 ↓ 11.1 450,882 3

Partial HashAggregate (cost=289,451.99..289,856.84 rows=40,485 width=18) (actual time=2,197.315..2,300.735 rows=450,882 loops=3)

  • Group Key: pt.name_id
9. 359.168 1,953.230 ↓ 18.3 739,585 3

Parallel Hash Join (cost=113,414.59..289,249.56 rows=40,485 width=10) (actual time=271.752..1,953.230 rows=739,585 loops=3)

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

Parallel Hash Join (cost=12,089.98..186,677.13 rows=475,359 width=30) (actual time=48.224..1,371.681 rows=1,103,709 loops=3)

  • Hash Cond: ((pt.title_id)::text = (r.title_id)::text)
11. 374.082 374.082 ↑ 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.007..374.082 rows=5,004,000 loops=3)

12. 6.795 48.016 ↑ 1.2 23,598 3

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

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

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

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

Parallel Hash (cost=98,739.66..98,739.66 rows=206,796 width=10) (actual time=222.381..222.381 rows=172,140 loops=3)

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

Parallel Seq Scan on titles t (cost=0.00..98,739.66 rows=206,796 width=10) (actual time=0.010..177.260 rows=172,140 loops=3)

  • Filter: (kind_id = 2)
  • Rows Removed by Filter: 1769749
Planning time : 0.607 ms
Execution time : 3,209.699 ms