explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GTvn

Settings
# exclusive inclusive rows x rows loops node
1. 16.480 115,986.786 ↓ 501.0 501 1

Sort (cost=205,632.64..205,632.64 rows=1 width=1,199) (actual time=115,986.684..115,986.786 rows=501 loops=1)

  • Sort Key: d0.inserted_at, d0.seq
  • Sort Method: top-N heapsort Memory: 291kB
2. 6,822.121 115,970.306 ↓ 8,565.0 8,565 1

Nested Loop Left Join (cost=112,549.38..205,632.63 rows=1 width=1,199) (actual time=330.981..115,970.306 rows=8,565 loops=1)

  • Join Filter: (d0_1.id = d0.id)
  • Rows Removed by Join Filter: 27,080,731
3. 0.000 12.955 ↓ 8,565.0 8,565 1

Gather (cost=1,001.55..94,084.57 rows=1 width=756) (actual time=6.414..12.955 rows=8,565 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 3.561 187.010 ↓ 2,855.0 2,855 3 / 3

Nested Loop (cost=1.55..93,084.47 rows=1 width=756) (actual time=3.454..187.010 rows=2,855 loops=3)

5. 5.014 170.441 ↓ 2,168.0 2,168 3 / 3

Nested Loop Left Join (cost=1.13..93,077.83 rows=1 width=756) (actual time=3.437..170.441 rows=2,168 loops=3)

  • Filter: ((CASE WHEN d0.archived THEN 'archived'::character varying WHEN i4.processing_error THEN 'error'::character varying WHEN (NOT i4.uploaded) THEN 'uploading'::character varying WHEN (i4.uploaded AND (i4.thumbnail_key IS NULL)) THEN 'processing'::character varying WHEN ((d3.status)::text = 'created'::text) THEN 'uploading'::character varying WHEN ((d3.status)::text = ANY ('{uploaded,extracting}'::text[])) THEN 'processing'::character varying ELSE d0.status EN...
  • Rows Removed by Filter: 196
6. 2.867 165.427 ↓ 78.8 2,364 3 / 3

Nested Loop Left Join (cost=0.85..93,064.14 rows=30 width=714) (actual time=3.425..165.427 rows=2,364 loops=3)

7. 3.846 153.103 ↓ 78.8 2,364 3 / 3

Nested Loop Left Join (cost=0.43..93,047.11 rows=30 width=611) (actual time=3.410..153.103 rows=2,364 loops=3)

  • Filter: (CASE WHEN ((d0.dataset_image_id IS NOT NULL) AND (d2.dataset_video_id IS NULL)) THEN 'image'::text WHEN ((d0.dataset_image_id IS NOT NULL) AND (d2.dataset_video_id IS NOT NULL)) THEN 'video_frame'::text WHEN (d0.dataset_image_id IS NULL) THEN 'video'::text ELSE NULL::text END = ANY ('{image,video}'::text[]))
8. 139.800 139.800 ↑ 1.3 2,364 3 / 3

Parallel Seq Scan on dataset_items d0 (cost=0.00..82,862.86 rows=2,957 width=603) (actual time=3.378..139.800 rows=2,364 loops=3)

  • Filter: (dataset_id = 901)
  • Rows Removed by Filter: 579,348
9. 9.457 9.457 ↑ 1.0 1 7,093 / 3

Index Scan using dataset_images_pkey on dataset_images d2 (cost=0.43..3.43 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=7,093)

  • Index Cond: (id = d0.dataset_image_id)
10. 9.457 9.457 ↑ 1.0 1 7,093 / 3

Index Scan using images_pkey on images i4 (cost=0.43..0.57 rows=1 width=119) (actual time=0.004..0.004 rows=1 loops=7,093)

  • Index Cond: (d2.image_id = id)
11. 0.000 0.000 ↓ 0.0 0 7,093 / 3

Index Scan using dataset_videos_pkey on dataset_videos d3 (cost=0.27..0.43 rows=1 width=50) (actual time=0.000..0.000 rows=0 loops=7,093)

  • Index Cond: (id = d0.dataset_video_id)
12. 13.008 13.008 ↑ 1.0 1 6,504 / 3

Index Scan using simon_workflow_stages__search_migration on workflow_stages w6 (cost=0.43..6.63 rows=1 width=8) (actual time=0.004..0.006 rows=1 loops=6,504)

  • Index Cond: (workflow_id = d0.current_workflow_id)
  • Filter: (assignee_id = ANY ('{41}'::integer[]))
  • Rows Removed by Filter: 2
13. 100,904.265 109,135.230 ↓ 632.6 3,163 8,565

GroupAggregate (cost=111,547.83..111,547.94 rows=5 width=40) (actual time=0.067..12.742 rows=3,163 loops=8,565)

  • Group Key: d0_1.id
14. 7,952.075 8,230.965 ↓ 694.6 3,473 8,565

Sort (cost=111,547.83..111,547.84 rows=5 width=30) (actual time=0.034..0.961 rows=3,473 loops=8,565)

  • Sort Key: d0_1.id
  • Sort Method: quicksort Memory: 1,371kB
15. 7.856 278.890 ↓ 2,476.0 12,380 1

Gather (cost=1,307.21..111,547.77 rows=5 width=30) (actual time=24.251..278.890 rows=12,380 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
16. 6.548 271.034 ↓ 2,063.5 4,127 3 / 3

Hash Join (cost=307.21..110,547.27 rows=2 width=30) (actual time=17.609..271.034 rows=4,127 loops=3)

  • Hash Cond: (a3.annotation_class_id = a4.id)
17. 9.827 251.819 ↓ 3.9 8,442 3 / 3

Nested Loop (cost=1.28..110,235.61 rows=2,186 width=16) (actual time=4.870..251.819 rows=8,442 loops=3)

18. 5.153 224.568 ↓ 11.4 2,178 3 / 3

Nested Loop (cost=0.85..97,170.84 rows=191 width=16) (actual time=4.852..224.568 rows=2,178 loops=3)

19. 4.060 210.703 ↑ 1.4 2,178 3 / 3

Nested Loop (cost=0.42..91,357.91 rows=2,957 width=32) (actual time=4.837..210.703 rows=2,178 loops=3)

20. 199.550 199.550 ↑ 1.3 2,364 3 / 3

Parallel Seq Scan on dataset_items d0_1 (cost=0.00..82,862.86 rows=2,957 width=16) (actual time=4.824..199.550 rows=2,364 loops=3)

  • Filter: (dataset_id = 901)
  • Rows Removed by Filter: 579,348
21. 7.093 7.093 ↑ 1.0 1 7,093 / 3

Index Scan using workflows_pkey on workflows w1 (cost=0.42..2.87 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=7,093)

  • Index Cond: (id = d0_1.current_workflow_id)
22. 8.712 8.712 ↑ 1.0 1 6,534 / 3

Index Scan using simon_workflow_stages__search_migration on workflow_stages w2 (cost=0.43..1.96 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=6,534)

  • Index Cond: ((workflow_id = w1.id) AND (workflow_stage_template_id = w1.current_workflow_stage_template_id))
23. 17.424 17.424 ↑ 96.2 4 6,534 / 3

Index Scan using simon__annotations__workflow_stage_id on annotations a3 (cost=0.43..64.55 rows=385 width=16) (actual time=0.004..0.008 rows=4 loops=6,534)

  • Index Cond: (workflow_stage_id = w2.id)
24. 1.637 12.667 ↓ 362.6 2,901 3 / 3

Hash (cost=305.82..305.82 rows=8 width=22) (actual time=12.667..12.667 rows=2,901 loops=3)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 197kB
25. 11.030 11.030 ↓ 362.6 2,901 3 / 3

Seq Scan on annotation_classes a4 (cost=0.00..305.82 rows=8 width=22) (actual time=0.013..11.030 rows=2,901 loops=3)

  • Filter: (metadata ? 'tag'::text)
  • Rows Removed by Filter: 5,510
Planning time : 3.261 ms
Execution time : 115,987.484 ms