explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3Mtx

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 2,054.746 ↑ 1.0 9 1

Limit (cost=2,825,738.93..2,825,739.98 rows=9 width=16) (actual time=2,049.985..2,054.746 rows=9 loops=1)

2. 0.000 2,054.738 ↑ 3,282.7 9 1

Gather Merge (cost=2,825,738.93..2,829,185.97 rows=29,544 width=16) (actual time=2,049.983..2,054.738 rows=9 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 0.108 6,128.064 ↑ 2,954.4 5 3

Sort (cost=2,824,738.90..2,824,775.83 rows=14,772 width=16) (actual time=2,042.687..2,042.688 rows=5 loops=3)

  • Sort Key: pm.start_time DESC, pm.id
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
4. 0.159 6,127.956 ↑ 2,954.4 5 3

Hash Join (cost=558,986.25..2,824,430.91 rows=14,772 width=16) (actual time=1,981.943..2,042.652 rows=5 loops=3)

  • Hash Cond: (plot.type_id = plot_type.id)
5. 0.156 6,127.590 ↑ 4,946.2 5 3

Hash Join (cost=558,979.27..2,824,357.17 rows=24,731 width=24) (actual time=1,981.824..2,042.530 rows=5 loops=3)

  • Hash Cond: (pm.location_id = plot.id)
6. 127.578 6,112.872 ↑ 4,946.2 5 3

Hash Join (cost=558,468.67..2,823,781.63 rows=24,731 width=24) (actual time=1,976.921..2,037.624 rows=5 loops=3)

  • Hash Cond: (pm.pest_id = pest.id)
7. 4,404.469 5,981.925 ↓ 1.5 422,682 3

Parallel Bitmap Heap Scan on pest_measurement pm (cost=558,309.35..2,822,904.56 rows=273,233 width=40) (actual time=1,580.698..1,993.975 rows=422,682 loops=3)

  • Recheck Cond: ((start_time >= '2019-04-08 00:00:00'::timestamp without time zone) AND (project_id = '72154491368833025'::bigint) AND (owner_loc_id = ANY ('{72155209181364238,72155209181364235,72155209181364236,72155209181364237,72155209414344705,72155209181364234,72159268805738497}'::bigint[])))
  • Rows Removed by Index Recheck: 27534
  • Filter: (plan_status = ANY ('{7,0,1,3}'::integer[]))
  • Heap Blocks: exact=17663
8. 3.086 1,577.456 ↓ 0.0 0 1

BitmapAnd (cost=558,309.35..558,309.35 rows=658,945 width=0) (actual time=1,577.456..1,577.456 rows=0 loops=1)

9. 311.935 311.935 ↑ 1.3 1,652,031 1

Bitmap Index Scan on pest_measurement_start_time_project_index (cost=0.00..160,779.58 rows=2,157,316 width=0) (actual time=311.935..311.935 rows=1,652,031 loops=1)

  • Index Cond: ((start_time >= '2019-04-08 00:00:00'::timestamp without time zone) AND (project_id = '72154491368833025'::bigint))
10. 1,262.435 1,262.435 ↑ 1.0 22,965,150 1

Bitmap Index Scan on pest_measurement_owner_loc_id_idx (cost=0.00..397,201.64 rows=23,319,155 width=0) (actual time=1,262.435..1,262.435 rows=22,965,150 loops=1)

  • Index Cond: (owner_loc_id = ANY ('{72155209181364238,72155209181364235,72155209181364236,72155209181364237,72155209414344705,72155209181364234,72159268805738497}'::bigint[]))
11. 0.258 3.369 ↑ 1.0 539 3

Hash (cost=152.57..152.57 rows=540 width=8) (actual time=1.122..1.123 rows=539 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
12. 0.768 3.111 ↑ 1.0 539 3

Bitmap Heap Scan on entity_type pest (cost=12.47..152.57 rows=540 width=8) (actual time=0.795..1.037 rows=539 loops=3)

  • Recheck Cond: (main_type = 5)
  • Filter: (id <> '72159269354733569'::bigint)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=61
13. 2.343 2.343 ↑ 1.0 540 3

Bitmap Index Scan on entity_main_type (cost=0.00..12.33 rows=540 width=0) (actual time=0.781..0.781 rows=540 loops=3)

  • Index Cond: (main_type = 5)
14. 6.555 14.562 ↑ 1.0 11,849 3

Hash (cost=362.49..362.49 rows=11,849 width=16) (actual time=4.854..4.854 rows=11,849 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 684kB
15. 8.007 8.007 ↑ 1.0 11,849 3

Seq Scan on location plot (cost=0.00..362.49 rows=11,849 width=16) (actual time=0.015..2.669 rows=11,849 loops=3)

16. 0.054 0.207 ↑ 1.0 89 3

Hash (cost=5.86..5.86 rows=89 width=8) (actual time=0.068..0.069 rows=89 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
17. 0.153 0.153 ↑ 1.0 89 3

Seq Scan on location_type plot_type (cost=0.00..5.86 rows=89 width=8) (actual time=0.018..0.051 rows=89 loops=3)

  • Filter: (main_type = ANY ('{12,3}'::integer[]))
  • Rows Removed by Filter: 60