explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 43u2

Settings
# exclusive inclusive rows x rows loops node
1. 2.350 309,121.929 ↑ 1.0 5,000 1

Limit (cost=20,107,459.45..20,107,471.95 rows=5,000 width=133) (actual time=309,119.348..309,121.929 rows=5,000 loops=1)

  • Buffers: shared hit=4783770 read=13107172 dirtied=1310 written=939
  • I/O Timings: read=696572.086 write=21.984
2. 81.748 309,119.579 ↑ 83.7 5,000 1

Sort (cost=20,107,459.45..20,108,506.00 rows=418,623 width=133) (actual time=309,119.345..309,119.579 rows=5,000 loops=1)

  • Sort Key: ((random() < '42'::double precision))
  • Sort Method: top-N heapsort Memory: 1088kB
  • Buffers: shared hit=4783770 read=13107172 dirtied=1310 written=939
  • I/O Timings: read=696572.086 write=21.984
3. 156.220 309,037.831 ↑ 1.4 295,866 1

Gather (cost=2,031,344.98..20,079,646.74 rows=418,623 width=133) (actual time=81,577.736..309,037.831 rows=295,866 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=4783765 read=13107172 dirtied=1310 written=939
  • I/O Timings: read=696572.086 write=21.984
4. 57.427 308,881.611 ↑ 1.8 98,622 3

Hash Join (cost=2,030,344.98..20,025,272.30 rows=174,426 width=231) (actual time=81,638.322..308,881.611 rows=98,622 loops=3)

  • Hash Cond: (deals.user_id = users.id)
  • Buffers: shared hit=4783765 read=13107172 dirtied=1310 written=939
  • I/O Timings: read=696572.086 write=21.984
5. 253,176.449 253,176.449 ↑ 47.3 98,988 3

Parallel Seq Scan on deals (cost=0.00..17,982,637.53 rows=4,681,829 width=235) (actual time=25,983.973..253,176.449 rows=98,988 loops=3)

  • Filter: (((status)::text = ANY ('{won,lost,tie}'::text[])) AND ((created_at)::date >= '2019-07-01'::date) AND ((data ->> 'ric'::text) = ANY ('{EURO,"AUD / USD","GBP / USD","USD / CAD","USD / CHF","NZD / USD","EUR / AUD","AUD / CAD","AUD / CHF","AUD / NZD","CAD / CHF","EUR / CAD","EUR / CHF","EUR / GBP","EUR / NZD","GBP / AUD","GBP / CAD","GBP / CHF","GBP / NZD","NZD / CAD","NZD / CHF","XAU / USD","XAG / USD","USD / JPY","EUR / JPY","AUD / JPY","CAD / JPY","CHF / JPY","GBP / JPY","NZD / JPY"}'::text[])))
  • Rows Removed by Filter: 75035093
  • Buffers: shared hit=745780 read=11720639
  • I/O Timings: read=653547.444
6. 286.248 55,647.735 ↓ 1.0 639,285 3

Hash (cost=2,022,363.60..2,022,363.60 rows=638,510 width=4) (actual time=55,647.735..55,647.735 rows=639,285 loops=3)

  • Buckets: 1048576 Batches: 1 Memory Usage: 30667kB
  • Buffers: shared hit=4037866 read=1386533 dirtied=1310 written=939
  • I/O Timings: read=43024.642 write=21.984
7. 55,361.487 55,361.487 ↓ 1.0 639,285 3

Seq Scan on users (cost=0.00..2,022,363.60 rows=638,510 width=4) (actual time=1.124..55,361.487 rows=639,285 loops=3)

  • Filter: ((NOT test) AND (deposits_count > 0))
  • Rows Removed by Filter: 16490889
  • Buffers: shared hit=4037866 read=1386533 dirtied=1310 written=939
  • I/O Timings: read=43024.642 write=21.984
Planning time : 5.234 ms
Execution time : 309,128.145 ms