explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JdZq

Settings
# exclusive inclusive rows x rows loops node
1. 415.214 56,482.592 ↑ 104.2 1,466 1

Unique (cost=2,824,585.95..2,849,404.77 rows=152,732 width=177) (actual time=55,878.526..56,482.592 rows=1,466 loops=1)

2. 8,391.099 56,067.378 ↓ 1.0 775,630 1

Sort (cost=2,824,585.95..2,826,495.09 rows=763,656 width=177) (actual time=55,878.522..56,067.378 rows=775,630 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: 104704kB
3. 1,742.982 47,676.279 ↓ 1.0 775,630 1

WindowAgg (cost=2,575,695.86..2,590,968.98 rows=763,656 width=177) (actual time=45,424.552..47,676.279 rows=775,630 loops=1)

4. 2,713.817 45,933.297 ↓ 1.0 775,630 1

Sort (cost=2,575,695.86..2,577,605.00 rows=763,656 width=182) (actual time=45,424.500..45,933.297 rows=775,630 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: 123360kB
5. 2,501.916 43,219.480 ↓ 1.0 775,630 1

WindowAgg (cost=2,326,805.76..2,342,078.88 rows=763,656 width=182) (actual time=39,903.398..43,219.480 rows=775,630 loops=1)

6. 2,740.492 40,717.564 ↓ 1.0 775,630 1

Sort (cost=2,326,805.76..2,328,714.90 rows=763,656 width=180) (actual time=39,903.373..40,717.564 rows=775,630 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: 122184kB
7. 2,109.972 37,977.072 ↓ 1.0 775,630 1

WindowAgg (cost=2,077,915.66..2,093,188.78 rows=763,656 width=180) (actual time=34,920.044..37,977.072 rows=775,630 loops=1)

8. 2,359.215 35,867.100 ↓ 1.0 775,630 1

Sort (cost=2,077,915.66..2,079,824.80 rows=763,656 width=176) (actual time=34,919.990..35,867.100 rows=775,630 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: 119248kB
9. 1,727.619 33,507.885 ↓ 1.0 775,630 1

WindowAgg (cost=1,835,142.76..1,850,415.88 rows=763,656 width=176) (actual time=31,263.621..33,507.885 rows=775,630 loops=1)

10. 2,606.811 31,780.266 ↓ 1.0 775,630 1

Sort (cost=1,835,142.76..1,837,051.90 rows=763,656 width=175) (actual time=31,263.597..31,780.266 rows=775,630 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: 118496kB
11. 1,878.188 29,173.455 ↓ 1.0 775,630 1

WindowAgg (cost=1,592,369.86..1,607,642.98 rows=763,656 width=175) (actual time=26,702.695..29,173.455 rows=775,630 loops=1)

12. 2,675.968 27,295.267 ↓ 1.0 775,630 1

Sort (cost=1,592,369.86..1,594,279.00 rows=763,656 width=173) (actual time=26,702.674..27,295.267 rows=775,630 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: 116984kB
13. 1,873.661 24,619.299 ↓ 1.0 775,630 1

WindowAgg (cost=1,349,596.96..1,364,870.08 rows=763,656 width=173) (actual time=22,193.598..24,619.299 rows=775,630 loops=1)

14. 4,351.906 22,745.638 ↓ 1.0 775,630 1

Sort (cost=1,349,596.96..1,351,506.10 rows=763,656 width=165) (actual time=22,193.560..22,745.638 rows=775,630 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: 110928kB
15. 1,589.808 18,393.732 ↓ 1.0 775,630 1

WindowAgg (cost=1,112,933.06..1,128,206.18 rows=763,656 width=165) (actual time=16,351.620..18,393.732 rows=775,630 loops=1)

16. 2,285.192 16,803.924 ↓ 1.0 775,630 1

Sort (cost=1,112,933.06..1,114,842.20 rows=763,656 width=157) (actual time=16,351.589..16,803.924 rows=775,630 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: 100304kB
17. 1,199.627 14,518.732 ↓ 1.0 775,630 1

WindowAgg (cost=917,550.01..932,823.13 rows=763,656 width=157) (actual time=12,857.005..14,518.732 rows=775,630 loops=1)

18. 2,665.797 13,319.105 ↓ 1.0 775,630 1

Sort (cost=917,550.01..919,459.15 rows=763,656 width=125) (actual time=12,856.955..13,319.105 rows=775,630 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: 92728kB
19. 1,185.747 10,653.308 ↓ 1.0 775,630 1

WindowAgg (cost=717,602.73..755,785.53 rows=763,656 width=125) (actual time=7,348.231..10,653.308 rows=775,630 loops=1)

20. 529.200 9,467.561 ↓ 1.0 775,630 1

Subquery Scan on anon_1 (cost=717,602.73..740,512.41 rows=763,656 width=105) (actual time=7,347.453..9,467.561 rows=775,630 loops=1)

21. 1,120.944 8,938.361 ↓ 1.0 775,630 1

WindowAgg (cost=717,602.73..732,875.85 rows=763,656 width=349) (actual time=7,347.435..8,938.361 rows=775,630 loops=1)

22. 2,741.543 7,817.417 ↓ 1.0 775,630 1

Sort (cost=717,602.73..719,511.87 rows=763,656 width=45) (actual time=7,347.400..7,817.417 rows=775,630 loops=1)

  • Sort Key: aircraft_beacons.device_id, aircraft_beacons.receiver_id
  • Sort Method: external merge Disk: 40264kB
23. 4,291.382 5,075.874 ↓ 1.0 775,630 1

Bitmap Heap Scan on aircraft_beacons (cost=23,222.83..601,704.96 rows=763,656 width=45) (actual time=789.371..5,075.874 rows=775,630 loops=1)

  • Recheck Cond: (("timestamp" >= '2019-04-14 10:00:00'::timestamp without time zone) AND ("timestamp" <= '2019-04-14 10:59:59'::timestamp without time zone) AND (device_id IS NOT NULL))
  • Rows Removed by Index Recheck: 3326
  • Filter: ((error_count = 0) OR (error_count IS NULL))
  • Rows Removed by Filter: 281299
  • Heap Blocks: exact=5336 lossy=21135
24. 784.492 784.492 ↑ 1.0 1,056,929 1

Bitmap Index Scan on ix_aircraft_beacons_timestamp_device_receiver (cost=0.00..23,031.91 rows=1,075,500 width=0) (actual time=784.492..784.492 rows=1,056,929 loops=1)

  • Index Cond: (("timestamp" >= '2019-04-14 10:00:00'::timestamp without time zone) AND ("timestamp" <= '2019-04-14 10:59:59'::timestamp without time zone) AND (device_id IS NOT NULL))