explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jSm8

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 94,167.581 ↓ 0.0 0 1

WindowAgg (cost=1,182.51..1,182.91 rows=1 width=869) (actual time=94,167.581..94,167.581 rows=0 loops=1)

2. 0.027 94,167.576 ↓ 0.0 0 1

Sort (cost=1,182.51..1,182.52 rows=1 width=510) (actual time=94,167.576..94,167.576 rows=0 loops=1)

  • Sort Key: snapshot_scheduled_inspection.project_id, (_st_distance(location.geo_point, '0101000020E61000000000002098CF4240000000609AE94B40'::geography, '0'::double precision, true))
  • Sort Method: quicksort Memory: 25kB
3. 0.001 94,167.549 ↓ 0.0 0 1

Nested Loop (cost=55.14..1,182.50 rows=1 width=510) (actual time=94,167.549..94,167.549 rows=0 loops=1)

4. 0.000 94,167.548 ↓ 0.0 0 1

Nested Loop (cost=54.71..1,174.02 rows=1 width=381) (actual time=94,167.548..94,167.548 rows=0 loops=1)

5. 0.000 94,165.907 ↓ 1,775.0 1,775 1

Nested Loop (cost=54.57..1,173.76 rows=1 width=316) (actual time=85,682.671..94,165.907 rows=1,775 loops=1)

6. 1.395 94,164.337 ↓ 1,775.0 1,775 1

Nested Loop (cost=54.43..1,173.58 rows=1 width=284) (actual time=85,682.649..94,164.337 rows=1,775 loops=1)

7. 0.642 94,159.392 ↓ 1,775.0 1,775 1

Nested Loop (cost=54.01..1,166.49 rows=1 width=155) (actual time=85,682.608..94,159.392 rows=1,775 loops=1)

8. 48,655.166 94,156.975 ↓ 1,775.0 1,775 1

Nested Loop (cost=53.59..1,158.51 rows=1 width=97) (actual time=85,682.593..94,156.975 rows=1,775 loops=1)

  • Join Filter: (snapshot_scheduled_inspection.location_id = location.id)
  • Rows Removed by Join Filter: 323,305,600
9. 1,058.429 1,058.429 ↓ 182,145.0 182,145 1

Index Scan using location_gix on location (cost=0.29..8.68 rows=1 width=36) (actual time=0.731..1,058.429 rows=182,145 loops=1)

  • Index Cond: (geo_point && '0101000020E61000000000002098CF4240000000609AE94B40'::geography)
  • Filter: (('0101000020E61000000000002098CF4240000000609AE94B40'::geography && _st_expand(geo_point, '36110'::double precision)) AND _st_dwithin(geo_point, '0101000020E61000000000002098CF4240000000609AE94B40'::geography, '36110'::double precision, true))
  • Rows Removed by Filter: 84,610
10. 28,596.765 44,443.380 ↓ 2.9 1,775 182,145

Bitmap Heap Scan on snapshot_scheduled_inspection (cost=53.30..1,142.28 rows=604 width=69) (actual time=0.091..0.244 rows=1,775 loops=182,145)

  • Recheck Cond: ((actor_id = '-1'::integer) AND (project_id = ANY ('{159378,159393,159408,159423,159438,100183,156497,156514,156531,156603}'::integer[])) AND (visible_id = 1) AND (actuality = 0))
  • Heap Blocks: exact=14,207,310
11. 15,846.615 15,846.615 ↓ 2.9 1,775 182,145

Bitmap Index Scan on snapshot_scheduled_inspection_actor_id_project_id_visible_id_ac (cost=0.00..53.15 rows=604 width=0) (actual time=0.087..0.087 rows=1,775 loops=182,145)

  • Index Cond: ((actor_id = '-1'::integer) AND (project_id = ANY ('{159378,159393,159408,159423,159438,100183,156497,156514,156531,156603}'::integer[])) AND (visible_id = 1) AND (actuality = 0))
12. 1.775 1.775 ↑ 1.0 1 1,775

Index Scan using control_object_pkey on control_object (cost=0.42..7.98 rows=1 width=62) (actual time=0.001..0.001 rows=1 loops=1,775)

  • Index Cond: (id = snapshot_scheduled_inspection.control_object_id)
13. 3.550 3.550 ↑ 1.0 1 1,775

Index Scan using control_subject_pkey on control_subject (cost=0.42..7.09 rows=1 width=133) (actual time=0.002..0.002 rows=1 loops=1,775)

  • Index Cond: (id = snapshot_scheduled_inspection.control_subject_id)
14. 1.775 1.775 ↑ 1.0 1 1,775

Index Scan using planned_work_status_pkey on planned_work_status (cost=0.14..0.17 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=1,775)

  • Index Cond: (id = snapshot_scheduled_inspection.planned_work_status_id)
15. 1.775 1.775 ↓ 0.0 0 1,775

Index Scan using stage_pkey on stage (cost=0.14..0.26 rows=1 width=73) (actual time=0.001..0.001 rows=0 loops=1,775)

  • Index Cond: (id = snapshot_scheduled_inspection.stage_id)
  • Filter: ((kind = 0) AND (status_stage_id = 2) AND ((platform)::text = 'mb'::text))
  • Rows Removed by Filter: 1
16. 0.000 0.000 ↓ 0.0 0

Index Scan using execution_pkey on execution (cost=0.43..8.23 rows=1 width=125) (never executed)

  • Index Cond: (id = snapshot_scheduled_inspection.execution_id)
Planning time : 4.153 ms
Execution time : 94,167.920 ms