explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ihpe

Settings
# exclusive inclusive rows x rows loops node
1. 613.162 1,114,192.456 ↓ 0.0 0 1

Insert on device_stats (cost=17,238,557.89..17,415,170.05 rows=941,932 width=150) (actual time=1,114,192.456..1,114,192.456 rows=0 loops=1)

2. 46.358 1,113,579.294 ↑ 440.2 2,140 1

Subquery Scan on *SELECT* (cost=17,238,557.89..17,415,170.05 rows=941,932 width=150) (actual time=1,101,991.748..1,113,579.294 rows=2,140 loops=1)

3. 6,224.990 1,113,532.936 ↑ 440.2 2,140 1

Unique (cost=17,238,557.89..17,391,621.75 rows=941,932 width=177) (actual time=1,101,985.352..1,113,532.936 rows=2,140 loops=1)

4. 63,856.717 1,107,307.946 ↑ 1.0 4,687,011 1

Sort (cost=17,238,557.89..17,250,332.04 rows=4,709,657 width=177) (actual time=1,101,985.345..1,107,307.946 rows=4,687,011 loops=1)

  • Sort Key: anon_1.device_id, (max(anon_1.dr) OVER (?)), (max(anon_1.altitude) OVER (?)), (count(anon_1.device_id) OVER (?)), (first_value(anon_1.name) OVER (?)), (first_value(anon_1."timestamp") OVER (?)), (first_value(anon_1."timestamp") OVER (?)), (first_value(anon_1.aircraft_type) OVER (?)), (first_value(anon_1.stealth) OVER (?)), (first_value(anon_1.software_version) OVER (?)), (first_value(anon_1.hardware_version) OVER (?)), (first_value(anon_1.real_address) OVER (?))
  • Sort Method: external sort Disk: 639224kB
5. 38,864.472 1,043,451.229 ↑ 1.0 4,687,011 1

WindowAgg (cost=15,396,649.93..15,490,843.07 rows=4,709,657 width=177) (actual time=998,827.894..1,043,451.229 rows=4,687,011 loops=1)

6. 16,960.669 1,004,586.757 ↑ 1.0 4,687,011 1

Sort (cost=15,396,649.93..15,408,424.07 rows=4,709,657 width=182) (actual time=998,827.862..1,004,586.757 rows=4,687,011 loops=1)

  • Sort Key: anon_1.device_id, (CASE WHEN (anon_1.real_address IS NULL) THEN NULL::timestamp without time zone ELSE anon_1."timestamp" END) DESC NULLS LAST
  • Sort Method: external sort Disk: 747488kB
7. 40,260.448 987,626.088 ↑ 1.0 4,687,011 1

WindowAgg (cost=13,554,741.97..13,648,935.11 rows=4,709,657 width=182) (actual time=942,538.962..987,626.088 rows=4,687,011 loops=1)

8. 17,527.173 947,365.640 ↑ 1.0 4,687,011 1

Sort (cost=13,554,741.97..13,566,516.11 rows=4,709,657 width=180) (actual time=942,538.883..947,365.640 rows=4,687,011 loops=1)

  • Sort Key: anon_1.device_id, (CASE WHEN (anon_1.hardware_version IS NULL) THEN NULL::timestamp without time zone ELSE anon_1."timestamp" END) DESC NULLS LAST
  • Sort Method: external sort Disk: 739784kB
9. 42,125.615 929,838.467 ↑ 1.0 4,687,011 1

WindowAgg (cost=11,712,834.01..11,807,027.15 rows=4,709,657 width=180) (actual time=866,550.991..929,838.467 rows=4,687,011 loops=1)

10. 197,090.050 887,712.852 ↑ 1.0 4,687,011 1

Sort (cost=11,712,834.01..11,724,608.15 rows=4,709,657 width=176) (actual time=866,550.833..887,712.852 rows=4,687,011 loops=1)

  • Sort Key: anon_1.device_id, (CASE WHEN (anon_1.software_version IS NULL) THEN NULL::timestamp without time zone ELSE anon_1."timestamp" END) DESC NULLS LAST
  • Sort Method: external merge Disk: 720552kB
11. 36,310.582 690,622.802 ↑ 1.0 4,687,011 1

WindowAgg (cost=9,918,076.05..10,012,269.19 rows=4,709,657 width=176) (actual time=649,117.426..690,622.802 rows=4,687,011 loops=1)

12. 23,243.183 654,312.220 ↑ 1.0 4,687,011 1

Sort (cost=9,918,076.05..9,929,850.19 rows=4,709,657 width=175) (actual time=649,117.396..654,312.220 rows=4,687,011 loops=1)

  • Sort Key: anon_1.device_id, (CASE WHEN (anon_1.stealth IS NULL) THEN NULL::timestamp without time zone ELSE anon_1."timestamp" END) DESC NULLS LAST
  • Sort Method: external sort Disk: 715944kB
13. 37,468.136 631,069.037 ↑ 1.0 4,687,011 1

WindowAgg (cost=8,123,318.09..8,217,511.23 rows=4,709,657 width=175) (actual time=588,414.212..631,069.037 rows=4,687,011 loops=1)

14. 23,685.525 593,600.901 ↑ 1.0 4,687,011 1

Sort (cost=8,123,318.09..8,135,092.23 rows=4,709,657 width=173) (actual time=588,414.182..593,600.901 rows=4,687,011 loops=1)

  • Sort Key: anon_1.device_id, (CASE WHEN (anon_1.aircraft_type IS NULL) THEN NULL::timestamp without time zone ELSE anon_1."timestamp" END) DESC NULLS LAST
  • Sort Method: external sort Disk: 706784kB
15. 37,723.246 569,915.376 ↑ 1.0 4,687,011 1

WindowAgg (cost=6,328,560.13..6,422,753.27 rows=4,709,657 width=173) (actual time=507,883.993..569,915.376 rows=4,687,011 loops=1)

16. 178,387.319 532,192.130 ↑ 1.0 4,687,011 1

Sort (cost=6,328,560.13..6,340,334.27 rows=4,709,657 width=165) (actual time=507,881.162..532,192.130 rows=4,687,011 loops=1)

  • Sort Key: anon_1.device_id, (CASE WHEN (anon_1."timestamp" IS NULL) THEN NULL::timestamp without time zone ELSE anon_1."timestamp" END) DESC NULLS LAST
  • Sort Method: external merge Disk: 670152kB
17. 33,676.870 353,804.811 ↑ 1.0 4,687,011 1

WindowAgg (cost=4,580,941.92..4,675,135.06 rows=4,709,657 width=165) (actual time=315,570.081..353,804.811 rows=4,687,011 loops=1)

18. 22,079.990 320,127.941 ↑ 1.0 4,687,011 1

Sort (cost=4,580,941.92..4,592,716.06 rows=4,709,657 width=157) (actual time=315,568.688..320,127.941 rows=4,687,011 loops=1)

  • Sort Key: anon_1.device_id, (CASE WHEN (anon_1."timestamp" IS NULL) THEN NULL::timestamp without time zone ELSE anon_1."timestamp" END)
  • Sort Method: external sort Disk: 606072kB
19. 27,822.373 298,047.951 ↑ 1.0 4,687,011 1

WindowAgg (cost=3,097,320.65..3,191,513.79 rows=4,709,657 width=157) (actual time=256,694.899..298,047.951 rows=4,687,011 loops=1)

20. 111,461.417 270,225.578 ↑ 1.0 4,687,011 1

Sort (cost=3,097,320.65..3,109,094.80 rows=4,709,657 width=125) (actual time=256,694.784..270,225.578 rows=4,687,011 loops=1)

  • Sort Key: anon_1.device_id, (CASE WHEN (anon_1.name IS NULL) THEN NULL::timestamp without time zone ELSE anon_1."timestamp" END)
  • Sort Method: external merge Disk: 560344kB
21. 12,804.071 158,764.161 ↑ 1.0 4,687,011 1

WindowAgg (cost=1,623,265.08..1,858,747.93 rows=4,709,657 width=125) (actual time=104,256.561..158,764.161 rows=4,687,011 loops=1)

22. 4,762.501 145,960.090 ↑ 1.0 4,687,011 1

Subquery Scan on anon_1 (cost=1,623,265.08..1,764,554.79 rows=4,709,657 width=105) (actual time=104,207.560..145,960.090 rows=4,687,011 loops=1)

23. 10,154.651 141,197.589 ↑ 1.0 4,687,011 1

WindowAgg (cost=1,623,265.08..1,717,458.22 rows=4,709,657 width=349) (actual time=104,207.537..141,197.589 rows=4,687,011 loops=1)

24. 60,611.694 131,042.938 ↑ 1.0 4,687,011 1

Sort (cost=1,623,265.08..1,635,039.23 rows=4,709,657 width=45) (actual time=104,207.481..131,042.938 rows=4,687,011 loops=1)

  • Sort Key: aircraft_beacons.device_id, aircraft_beacons.receiver_id
  • Sort Method: external merge Disk: 243168kB
25. 70,431.244 70,431.244 ↑ 1.0 4,687,011 1

Seq Scan on aircraft_beacons (cost=0.00..761,834.96 rows=4,709,657 width=45) (actual time=37.435..70,431.244 rows=4,687,011 loops=1)

  • Filter: ((device_id IS NOT NULL) AND ("timestamp" >= '2019-04-12 00:00:00'::timestamp without time zone) AND ("timestamp" <= '2019-04-12 23:59:59'::timestamp without time zone) AND ((error_count = 0) OR (error_count IS NULL)))
  • Rows Removed by Filter: 10967602
Planning time : 16.599 ms
Execution time : 1,114,914.031 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint device_stats_device_id_fkey 137.668 ms 2140 0.064 ms