explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IcLI

Settings
# exclusive inclusive rows x rows loops node
1. 17,408.217 36,638.852 ↓ 75.7 160,683 1

GroupAggregate (cost=120,040.99..120,418 rows=2,124 width=624) (actual time=17,758.869..36,638.852 rows=160,683 loops=1)

  • Group Key: (time_bucket('00:10:00'::interval, cr.report_date_time)), cr.identity_id
  • Buffers: shared hit=14,057,041 read=1,394,505 written=74,543, temp read=253,284 written=253,329
2. 6,996.380 19,230.635 ↓ 1,357.8 2,883,924 1

Sort (cost=120,040.99..120,046.3 rows=2,124 width=453) (actual time=17,758.691..19,230.635 rows=2,883,924 loops=1)

  • Sort Key: (time_bucket('00:10:00'::interval, cr.report_date_time)), cr.identity_id
  • Sort Method: external merge Disk: 2,026,272kB
  • Buffers: shared hit=14,057,041 read=1,394,505 written=74,543, temp read=253,284 written=253,329
3. 0.000 12,234.255 ↓ 1,357.8 2,883,924 1

Gather (cost=2,013.59..119,923.62 rows=2,124 width=453) (actual time=5,977.683..12,234.255 rows=2,883,924 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
  • Buffers: shared hit=14,057,041 read=1,394,505 written=74,543
4. 297.149 12,586.974 ↓ 1,086.2 576,785 5 / 5

Hash Join (cost=1,013.59..118,711.22 rows=531 width=453) (actual time=5,934.44..12,586.974 rows=576,785 loops=5)

  • Buffers: shared hit=14,057,041 read=1,394,505 written=74,543
5. 332.881 12,289.768 ↓ 1,086.2 576,785 5 / 5

Nested Loop (cost=1,011.49..118,706.27 rows=531 width=426) (actual time=5,934.148..12,289.768 rows=576,785 loops=5)

  • Buffers: shared hit=14,056,904 read=1,394,505 written=74,543
6. 50.078 9,649.748 ↓ 1,086.2 576,785 5 / 5

Append (cost=1,011.06..114,288.65 rows=531 width=288) (actual time=5,934.065..9,649.748 rows=576,785 loops=5)

  • Buffers: shared read=1,394,446 written=74,538
7. 9,013.045 9,599.670 ↓ 1,086.2 576,785 5 / 5

Bitmap Heap Scan on contact_reports_p2020_07_04 cr (cost=1,011.06..114,285.99 rows=531 width=288) (actual time=5,934.064..9,599.67 rows=576,785 loops=5)

  • Filter: ((cr.report_date_time >= '2020-07-04 11:30:00'::timestamp without time zone) AND (cr.report_date_time <= '2020-07-04 19:30:00'::timestamp without time zone) AND (cr.provider_id = ANY ('{12055157,12055159}'::integer[])) AND _st_contains('0103000020E610000001000000120000002FBC7AF782DF61C007F0AB1720D1544020945266ECF957C01DB26B77E0E354400FBAD4F74A2640C0A326D552AB115440C275F811BF5A3FC042C266482A8F4540B6A9906C682B48C0DFEA316C083C454099D4344F452E4BC08972F1EE4FD643408362F571B3824FC070B7A675CF1744400FDAF3107FDB50C00CCB7B90CD1D4540B5D63E32890852C00FAD420B037B4540E3933436C1AF53C0862FB9BE401044403455804F682859C082996DF00475444012A756BA5BCB61C0B4ECAEC278B745403AE36B27EDCC61C034C4AFD243D14840E2E165B394EC64C0CF09B87E3C7F4A40277D2093B56165C0CDFF6DD345464D40277D2093B56165C09EAF7A5B0F495140276DBDAD872365C028B0E2308B7552402FBC7AF782DF61C007F0AB1720D15440'::geometry, cr.geopoint))
  • Heap Blocks: exact=159,328 lossy=106,245
  • Buffers: shared read=1,394,446 written=74,538
8. 586.625 586.625 ↓ 431.6 13,701,992 1 / 5

Bitmap Index Scan on contact_reports_p2020_07_04_geopoint_idx (cost=0..1,010.53 rows=31,748 width=0) (actual time=2,933.125..2,933.125 rows=13,701,992 loops=1)

  • Index Cond: ('0103000020E610000001000000120000002FBC7AF782DF61C007F0AB1720D1544020945266ECF957C01DB26B77E0E354400FBAD4F74A2640C0A326D552AB115440C275F811BF5A3FC042C266482A8F4540B6A9906C682B48C0DFEA316C083C454099D4344F452E4BC08972F1EE4FD643408362F571B3824FC070B7A675CF1744400FDAF3107FDB50C00CCB7B90CD1D4540B5D63E32890852C00FAD420B037B4540E3933436C1AF53C0862FB9BE401044403455804F682859C082996DF00475444012A756BA5BCB61C0B4ECAEC278B745403AE36B27EDCC61C034C4AFD243D14840E2E165B394EC64C0CF09B87E3C7F4A40277D2093B56165C0CDFF6DD345464D40277D2093B56165C09EAF7A5B0F495140276DBDAD872365C028B0E2308B7552402FBC7AF782DF61C007F0AB1720D15440'::geometry ~ cr.geopoint)
  • Buffers: shared read=80,922
9. 2,307.139 2,307.139 ↑ 1.0 1 2,883,924 / 5

Index Scan using pk_identities on identities i (cost=0.43..8.32 rows=1 width=142) (actual time=0.004..0.004 rows=1 loops=2,883,924)

  • Index Cond: (i.identity_id = cr.identity_id)
  • Buffers: shared hit=14,056,904 read=59 written=5
10. 0.021 0.057 ↓ 1.3 66 5 / 5

Hash (cost=1.49..1.49 rows=49 width=23) (actual time=0.056..0.057 rows=66 loops=5)

  • Buffers: shared hit=5
11. 0.036 0.036 ↓ 1.3 66 5 / 5

Seq Scan on providers p (cost=0..1.49 rows=49 width=23) (actual time=0.028..0.036 rows=66 loops=5)

  • Buffers: shared hit=5
Planning time : 51.263 ms
Execution time : 37,134.018 ms