explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9Xlt

Settings
# exclusive inclusive rows x rows loops node
1. 0.074 3,607.590 ↑ 1.0 1 1

Aggregate (cost=93,642.52..93,642.53 rows=1 width=16) (actual time=3,607.590..3,607.590 rows=1 loops=1)

2.          

Initplan (for Aggregate)

3. 0.214 40.079 ↑ 1.0 1 1

Aggregate (cost=27,592.96..27,592.97 rows=1 width=8) (actual time=40.079..40.079 rows=1 loops=1)

4. 18.910 39.865 ↓ 14.3 3,551 1

Bitmap Heap Scan on api_calls api_calls_1 (cost=5,375.81..27,592.34 rows=248 width=0) (actual time=21.784..39.865 rows=3,551 loops=1)

  • Recheck Cond: ((device_device_id IS NOT NULL) AND (created >= '2020-06-01 00:00:00'::timestamp without time zone) AND (created <= '2020-06-30 23:59:59'::timestamp without time zone))
  • Filter: ((request ~~ '%ONBOARD_2SCHOOL%'::text) OR (request ~~ '%ONBOARD_2PLACE%'::text))
  • Rows Removed by Filter: 2,961
  • Heap Blocks: exact=5,554
5. 1.197 20.955 ↓ 0.0 0 1

BitmapAnd (cost=5,375.81..5,375.81 rows=9,290 width=0) (actual time=20.955..20.955 rows=0 loops=1)

6. 3.917 3.917 ↓ 1.0 27,909 1

Bitmap Index Scan on api_calls_api_device_device_id (cost=0.00..1,169.67 rows=27,900 width=0) (actual time=3.917..3.917 rows=27,909 loops=1)

  • Index Cond: (device_device_id IS NOT NULL)
7. 15.841 15.841 ↑ 1.0 161,203 1

Bitmap Index Scan on api_calls_api_created (cost=0.00..4,205.76 rows=164,134 width=0) (actual time=15.841..15.841 rows=161,203 loops=1)

  • Index Cond: ((created >= '2020-06-01 00:00:00'::timestamp without time zone) AND (created <= '2020-06-30 23:59:59'::timestamp without time zone))
8. 0.108 3,567.437 ↑ 133.7 193 1

Merge Join (cost=61,023.69..65,985.03 rows=25,805 width=0) (actual time=349.536..3,567.437 rows=193 loops=1)

  • Merge Cond: (a.device_device_id = device_devices.id)
9. 0.916 3,567.237 ↑ 2,362.3 193 1

Subquery Scan on a (cost=61,018.45..142,972.38 rows=455,927 width=8) (actual time=349.443..3,567.237 rows=193 loops=1)

  • Filter: (a.diff < '-01:00:00'::interval)
  • Rows Removed by Filter: 3,719
10. 4.303 3,566.321 ↑ 349.6 3,912 1

Result (cost=61,018.45..125,875.13 rows=1,367,780 width=40) (actual time=344.834..3,566.321 rows=3,912 loops=1)

11. 3,097.398 3,562.018 ↑ 349.6 3,912 1

ProjectSet (cost=61,018.45..84,841.73 rows=1,367,780 width=64) (actual time=344.817..3,562.018 rows=3,912 loops=1)

12. 83.307 464.620 ↓ 1.2 161,203 1

Gather Merge (cost=61,018.45..76,976.99 rows=136,778 width=340) (actual time=342.825..464.620 rows=161,203 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
13. 15.808 381.313 ↑ 1.3 53,734 3 / 3

Result (cost=60,018.42..60,873.29 rows=68,389 width=340) (actual time=330.239..381.313 rows=53,734 loops=3)

14. 192.728 365.505 ↑ 1.3 53,734 3 / 3

Sort (cost=60,018.42..60,189.40 rows=68,389 width=340) (actual time=330.234..365.505 rows=53,734 loops=3)

  • Sort Key: api_calls.device_device_id, api_calls.id
  • Sort Method: external merge Disk: 21,032kB
  • Worker 0: Sort Method: external merge Disk: 16,872kB
  • Worker 1: Sort Method: external merge Disk: 16,832kB
15. 172.777 172.777 ↑ 1.3 53,734 3 / 3

Parallel Seq Scan on api_calls (cost=0.00..43,770.78 rows=68,389 width=340) (actual time=0.069..172.777 rows=53,734 loops=3)

  • Filter: ((created >= '2020-06-01 00:00:00'::timestamp without time zone) AND (created <= '2020-06-30 23:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 110,664
16. 0.052 0.092 ↑ 1.0 55 1

Sort (cost=5.23..5.37 rows=57 width=8) (actual time=0.085..0.092 rows=55 loops=1)

  • Sort Key: device_devices.id
  • Sort Method: quicksort Memory: 27kB
17. 0.040 0.040 ↑ 1.0 57 1

Seq Scan on device_devices (cost=0.00..3.57 rows=57 width=8) (actual time=0.020..0.040 rows=57 loops=1)

Planning time : 1.470 ms
Execution time : 3,611.617 ms