#	exclusive	inclusive	rows x	rows	loops	node
1.	0.070	663,976.007	↑ 274.5	2	1	→ Hash Join (cost=9,551,800.78..9,626,572.56 rows=549 width=24) (actual time=663,975.876..663,976.007 rows=2 loops=1) Hash Cond: (l.user_id = u.id)
2.	6.352	663,947.735	↑ 274.5	2	1	→ GroupAggregate (cost=9,551,528.98..9,626,287.72 rows=549 width=8) (actual time=663,947.611..663,947.735 rows=2 loops=1) Group Key: l.user_id
3.	1.140	663,941.383	↑ 18,985.0	525	1	→ Sort (cost=9,551,528.98..9,576,446.73 rows=9,967,100 width=8) (actual time=663,941.303..663,941.383 rows=525 loops=1) Sort Key: l.user_id Sort Method: quicksort Memory: 49kB
4.	1,429.175	663,940.243	↑ 18,985.0	525	1	→ Nested Loop (cost=47,114.04..8,120,378.28 rows=9,967,100 width=8) (actual time=20,392.133..663,940.243 rows=525 loops=1)
5.	610,703.065	627,634.436	↑ 1.0	2,491,188	1	→ Bitmap Heap Scan on api_logs_201812 l (cost=47,114.03..2,414,213.52 rows=2,491,775 width=758) (actual time=16,955.046..627,634.436 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.	16,931.371	16,931.371	↑ 1.0	2,491,500	1	→ Bitmap Index Scan on api_logs_201812_api_method_index (cost=0.00..46,491.09 rows=2,504,296 width=0) (actual time=16,931.371..16,931.371 rows=2,491,500 loops=1) Index Cond: (api_method = ANY ('{27,28}'::integer[]))
7.	34,876.632	34,876.632	↓ 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.014..0.014 rows=0 loops=2,491,188) Filter: (((value ->> 'id'::text))::integer = ANY ('{19660,19658,19659,19661}'::integer[])) Rows Removed by Filter: 5
8.	1.328	28.202	↑ 1.0	4,880	1	→ Hash (cost=210.80..210.80 rows=4,880 width=20) (actual time=28.202..28.202 rows=4,880 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 329kB
9.	26.874	26.874	↑ 1.0	4,880	1	→ Seq Scan on users u (cost=0.00..210.80 rows=4,880 width=20) (actual time=14.506..26.874 rows=4,880 loops=1)

QUERY PLAN
Hash Join  (cost=9551800.78..9626572.56 rows=549 width=24) (actual time=663975.876..663976.007 rows=2 loops=1)
  Hash Cond: (l.user_id = u.id)
  ->  GroupAggregate  (cost=9551528.98..9626287.72 rows=549 width=8) (actual time=663947.611..663947.735 rows=2 loops=1)
        Group Key: l.user_id
        ->  Sort  (cost=9551528.98..9576446.73 rows=9967100 width=8) (actual time=663941.303..663941.383 rows=525 loops=1)
              Sort Key: l.user_id
              Sort Method: quicksort  Memory: 49kB
              ->  Nested Loop  (cost=47114.04..8120378.28 rows=9967100 width=8) (actual time=20392.133..663940.243 rows=525 loops=1)
                    ->  Bitmap Heap Scan on api_logs_201812 l  (cost=47114.03..2414213.52 rows=2491775 width=758) (actual time=16955.046..627634.436 rows=2491188 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
                          ->  Bitmap Index Scan on api_logs_201812_api_method_index  (cost=0.00..46491.09 rows=2504296 width=0) (actual time=16931.371..16931.371 rows=2491500 loops=1)
                                Index Cond: (api_method = ANY ('{27,28}'::integer[]))
                    ->  Function Scan on jsonb_array_elements p  (cost=0.01..2.26 rows=4 width=0) (actual time=0.014..0.014 rows=0 loops=2491188)
                          Filter: (((value ->> 'id'::text))::integer = ANY ('{19660,19658,19659,19661}'::integer[]))
                          Rows Removed by Filter: 5
  ->  Hash  (cost=210.80..210.80 rows=4880 width=20) (actual time=28.202..28.202 rows=4880 loops=1)
        Buckets: 8192  Batches: 1  Memory Usage: 329kB
        ->  Seq Scan on users u  (cost=0.00..210.80 rows=4880 width=20) (actual time=14.506..26.874 rows=4880 loops=1)
Planning time: 31.293 ms
Execution time: 663980.279 ms

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	610,703.065 ms	92.0 %
Bitmap Index Scan	1	16,931.371 ms	2.5 %
Function Scan	1	34,876.632 ms	5.3 %
GroupAggregate	1	6.352 ms	0.0 %
Hash	1	1.328 ms	0.0 %
Hash Join	1	0.070 ms	0.0 %
Nested Loop	1	1,429.175 ms	0.2 %
Seq Scan	1	26.874 ms	0.0 %
Sort	1	1.140 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
api_logs_201812	1	610,703.065 ms	92.0 %
Bitmap Heap Scan	1	610,703.065 ms	100.0 %
users	1	26.874 ms	0.0 %
Seq Scan	1	26.874 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8vHj

Color mode:

Visible columns:

Per node type stats

Per table stats