explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7Tok

Settings
# exclusive inclusive rows x rows loops node
1. 0.433 47,065.326 ↑ 1.0 5,000 1

Limit (cost=1,299,507.90..1,299,520.40 rows=5,000 width=133) (actual time=47,064.638..47,065.326 rows=5,000 loops=1)

  • Buffers: shared hit=5231778 read=941675 dirtied=560 written=1
  • I/O Timings: read=131173.880 write=0.059
2. 88.024 47,064.893 ↑ 1.6 5,000 1

Sort (cost=1,299,507.90..1,299,527.81 rows=7,963 width=133) (actual time=47,064.636..47,064.893 rows=5,000 loops=1)

  • Sort Key: ((random() < '42'::double precision))
  • Sort Method: top-N heapsort Memory: 1088kB
  • Buffers: shared hit=5231778 read=941675 dirtied=560 written=1
  • I/O Timings: read=131173.880 write=0.059
3. 95.015 46,976.869 ↓ 37.6 299,080 1

Gather (cost=1,001.45..1,298,991.94 rows=7,963 width=133) (actual time=1,037.138..46,976.869 rows=299,080 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=5231778 read=941675 dirtied=560 written=1
  • I/O Timings: read=131173.880 write=0.059
4. 269.683 46,881.854 ↓ 30.0 99,693 3

Nested Loop (cost=1.45..1,296,976.65 rows=3,318 width=231) (actual time=1,032.509..46,881.854 rows=99,693 loops=3)

  • Buffers: shared hit=5231778 read=941675 dirtied=560 written=1
  • I/O Timings: read=131173.880 write=0.059
5. 1,416.740 46,612.169 ↓ 30.0 99,693 3

Nested Loop (cost=1.01..1,289,010.63 rows=3,322 width=239) (actual time=1,032.497..46,612.169 rows=99,693 loops=3)

  • Buffers: shared hit=4034976 read=941675 dirtied=560 written=1
  • I/O Timings: read=131173.880 write=0.059
6. 45,195.415 45,195.415 ↓ 1.1 100,061 3

Parallel Index Scan using deals_created_at_finished_at_option_type_status_idx_1909 on deals (cost=0.57..1,070,082.06 rows=89,162 width=235) (actual time=1,032.402..45,195.415 rows=100,061 loops=3)

  • Index Cond: (created_at >= '2019-07-01 00:00:00'::timestamp without time zone)
  • Filter: (((status)::text = ANY ('{won,lost,tie}'::text[])) 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: 753393
  • Buffers: shared hit=2842890 read=932057 dirtied=559 written=1
  • I/O Timings: read=128516.422 write=0.059
7. 0.014 0.014 ↑ 1.0 1 300,184

Index Scan using idx_users_13032019_01 on users (cost=0.44..2.46 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=300,184)

  • Index Cond: (id = deals.user_id)
  • Filter: ((NOT test) AND (deposits_count > 0))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1192086 read=9618 dirtied=1
  • I/O Timings: read=2657.458
8. 0.002 0.002 ↑ 1.0 1 299,080

Index Scan using idx_users_13032019_01 on users users_1 (cost=0.44..2.40 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=299,080)

  • Index Cond: (id = users.id)
  • Filter: (NOT test)
  • Buffers: shared hit=1196802
Planning time : 2.938 ms
Execution time : 47,066.280 ms