explain.depesz.com

PostgreSQL's explain analyze made readable

Result: T4Z4

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.101 532,360.034 ↑ 274.5 2 1

Hash Join (cost=9,557,090.48..9,631,915.27 rows=549 width=24) (actual time=532,359.972..532,360.034 rows=2 loops=1)

  • Hash Cond: (l.user_id = u.id)
2. 0.261 532,356.027 ↑ 274.5 2 1

GroupAggregate (cost=9,556,818.68..9,631,630.43 rows=549 width=8) (actual time=532,355.982..532,356.027 rows=2 loops=1)

  • Group Key: l.user_id
3. 1.170 532,355.766 ↑ 18,998.4 525 1

Sort (cost=9,556,818.68..9,581,754.10 rows=9,974,168 width=8) (actual time=532,355.693..532,355.766 rows=525 loops=1)

  • Sort Key: l.user_id
  • Sort Method: quicksort Memory: 49kB
4. 1,688.744 532,354.596 ↑ 18,998.4 525 1

Nested Loop (cost=47,127.80..8,124,599.37 rows=9,974,168 width=8) (actual time=16,488.386..532,354.596 rows=525 loops=1)

5. 487,686.217 500,771.596 ↑ 1.0 2,491,188 1

Bitmap Heap Scan on api_logs_201812 l (cost=47,127.80..2,414,388.18 rows=2,493,542 width=758) (actual time=13,103.413..500,771.596 rows=2,491,188 loops=1)

  • Recheck Cond: (api_method = ANY ('{27,28}'::integer[]))
  • Rows Removed by Index Recheck: 5445219
  • Filter: (((request -> 'requestParams'::text) -> 'products'::text) <> 'null'::jsonb)
  • Rows Removed by Filter: 312
  • Heap Blocks: exact=26212 lossy=1479760
6. 13,085.379 13,085.379 ↑ 1.0 2,491,500 1

Bitmap Index Scan on api_logs_201812_api_method_index (cost=0.00..46,504.41 rows=2,506,072 width=0) (actual time=13,085.379..13,085.379 rows=2,491,500 loops=1)

  • Index Cond: (api_method = ANY ('{27,28}'::integer[]))
7. 29,894.256 29,894.256 ↓ 0.0 0 2,491,188

Function Scan on jsonb_array_elements p (cost=0.01..2.26 rows=4 width=0) (actual time=0.012..0.012 rows=0 loops=2,491,188)

  • Filter: (((value ->> 'id'::text))::integer = ANY ('{19660,19658,19659,19661}'::integer[]))
  • Rows Removed by Filter: 5
8. 1.293 3.906 ↑ 1.0 4,880 1

Hash (cost=210.80..210.80 rows=4,880 width=20) (actual time=3.906..3.906 rows=4,880 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 329kB
9. 2.613 2.613 ↑ 1.0 4,880 1

Seq Scan on users u (cost=0.00..210.80 rows=4,880 width=20) (actual time=0.015..2.613 rows=4,880 loops=1)

Planning time : 143.083 ms
Execution time : 532,360.665 ms