explain.depesz.com

A tool for finding a real cause for slow queries.

Result: OAS

options
# exclusive inclusive rows x rows loops node
1. 0.001 662.247 ↓ 0.0 0 1
→

GroupAggregate (cost=30215.76..30215.78 rows=1 width=16) (actual time=662.247..662.247 rows=0 loops=1)

2. 0.015 662.246 ↓ 0.0 0 1
→

Sort (cost=30215.76..30215.76 rows=1 width=16) (actual time=662.246..662.246 rows=0 loops=1)

  • Sort Key: tgm1.position_in_group, tgmt.test_target_id
  • Sort Method: quicksort Memory: 25kB
3. 0.001 662.231 ↓ 0.0 0 1
→

Nested Loop (cost=21866.57..30215.75 rows=1 width=16) (actual time=662.231..662.231 rows=0 loops=1)

  • Join Filter: (tg1.id <> tg.id)
4. 0.798 662.230 ↓ 0.0 0 1
→

Nested Loop (cost=21866.57..30209.46 rows=1 width=36) (actual time=662.230..662.230 rows=0 loops=1)

5. 4.124 529.132 ↓ 2450.0 2450 1
→

Nested Loop (cost=21866.57..30203.14 rows=1 width=32) (actual time=461.660..529.132 rows=2450 loops=1)

6. 12.116 510.839 ↓ 49.2 4723 1
→

Hash Join (cost=21866.57..29611.00 rows=96 width=40) (actual time=459.482..510.839 rows=4723 loops=1)

  • Hash Cond: ((sfb.sentinel_flock_id = sfb1.sentinel_flock_id) AND (sfb.agency_id = sfb1.agency_id))
  • Join Filter: ((sfb1.id <> sfb.id) AND (sfb.collection_date <= sfb1.collection_date))
7. 1.808 466.985 ↓ 2.6 1628 1
→

Hash Join (cost=20769.83..28441.94 rows=618 width=60) (actual time=427.710..466.985 rows=1628 loops=1)

  • Hash Cond: (sbb.sentinel_flock_bleed_id = sfb.id)
8. 0.760 439.635 ↓ 2.4 1628 1
→

Nested Loop (cost=19741.33..27399.61 rows=680 width=40) (actual time=402.137..439.635 rows=1628 loops=1)

9. 1.154 432.363 ↓ 2.1 1628 1
→

Hash Join (cost=19741.33..23758.76 rows=775 width=32) (actual time=402.107..432.363 rows=1628 loops=1)

  • Hash Cond: (tgm.test_group_id = tg.id)
10. 16.422 430.085 ↑ 1.6 1628 1
→

Hash Join (cost=19665.56..23665.26 rows=2661 width=28) (actual time=400.959..430.085 rows=1628 loops=1)

  • Hash Cond: (tgmt.test_group_member_id = tgm.id)
11. 37.629 366.756 ↓ 1.6 15353 1
→

Hash Join (cost=14286.10..18163.76 rows=9542 width=12) (actual time=316.217..366.756 rows=15353 loops=1)

  • Hash Cond: (tgmtr.test_group_member_target_id = tgmt.id)
12. 13.198 13.198 ↑ 1.0 18473 1
→

Index Scan using test_group_member_target_result_test_status_id_btree on test_group_member_target_result tgmtr (cost=0.00..3549.81 rows=18595 width=8) (actual time=0.041..13.198 rows=18473 loops=1)

  • Index Cond: (test_status_id = 3)
  • Filter: (deactive_date IS NULL)
13. 133.022 315.929 ↓ 1.2 314427 1
→

Hash (cost=11100.51..11100.51 rows=254847 width=20) (actual time=315.929..315.929 rows=314427 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 17196kB
14. 182.907 182.907 ↓ 1.2 314427 1
→

Seq Scan on test_group_member_target tgmt (cost=0.00..11100.51 rows=254847 width=20) (actual time=0.019..182.907 rows=314427 loops=1)

  • Filter: ((deactive_date IS NULL) AND (test_target_id = ANY ('{4,6,7}'::integer[])))
15. 18.715 46.907 ↑ 1.0 51759 1
→

Hash (cost=4729.21..4729.21 rows=52020 width=24) (actual time=46.907..46.907 rows=51759 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 2831kB
16. 20.572 28.192 ↑ 1.0 51759 1
→

Bitmap Heap Scan on test_group_member tgm (cost=1464.14..4729.21 rows=52020 width=24) (actual time=7.851..28.192 rows=51759 loops=1)

  • Recheck Cond: ((table_name)::text = 'sentinel_band_bleed'::text)
  • Filter: (deactive_date IS NULL)
17. 7.620 7.620 ↑ 1.0 51899 1
→

Bitmap Index Scan on test_group_member_table_name_record_id_btree (cost=0.00..1451.13 rows=52246 width=0) (actual time=7.620..7.620 rows=51899 loops=1)

  • Index Cond: ((table_name)::text = 'sentinel_band_bleed'::text)
18. 0.368 1.124 ↓ 1.1 841 1
→

Hash (cost=66.59..66.59 rows=735 width=12) (actual time=1.124..1.124 rows=841 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 37kB
19. 0.756 0.756 ↓ 1.1 841 1
→

Index Scan using test_group_agency_id_btree on test_group tg (cost=0.00..66.59 rows=735 width=12) (actual time=0.032..0.756 rows=841 loops=1)

  • Index Cond: (agency_id = 77)
  • Filter: ((run_date IS NOT NULL) AND (run_by IS NOT NULL) AND (run_date_complete IS NOT NULL) AND (deactive_date IS NULL))
20. 6.512 6.512 ↑ 1.0 1 1628
→

Index Scan using pk_sentinel_band_bleed on sentinel_band_bleed sbb (cost=0.00..4.69 rows=1 width=24) (actual time=0.003..0.004 rows=1 loops=1628)

  • Index Cond: (sbb.id = tgm.record_id)
  • Filter: (sbb.deactive_date IS NULL)
21. 10.752 25.542 ↓ 1.0 28386 1
→

Hash (cost=687.30..687.30 rows=27296 width=28) (actual time=25.542..25.542 rows=28386 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1664kB
22. 14.790 14.790 ↓ 1.0 28386 1
→

Seq Scan on sentinel_flock_bleed sfb (cost=0.00..687.30 rows=27296 width=28) (actual time=0.010..14.790 rows=28386 loops=1)

  • Filter: ((deactive_date IS NULL) AND (read_only_bit IS TRUE) AND (worksheet_bit IS FALSE))
23. 13.698 31.738 ↓ 1.0 28386 1
→

Hash (cost=687.30..687.30 rows=27296 width=28) (actual time=31.738..31.738 rows=28386 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1664kB
24. 18.040 18.040 ↓ 1.0 28386 1
→

Seq Scan on sentinel_flock_bleed sfb1 (cost=0.00..687.30 rows=27296 width=28) (actual time=0.026..18.040 rows=28386 loops=1)

  • Filter: ((deactive_date IS NULL) AND (read_only_bit IS TRUE) AND (worksheet_bit IS FALSE))
25. 14.169 14.169 ↑ 1.0 1 4723
→

Index Scan using sentinel_band_bleed_sentinel_flock_bleed_id_key on sentinel_band_bleed sbb1 (cost=0.00..6.15 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=4723)

  • Index Cond: ((sbb1.sentinel_flock_bleed_id = sfb1.id) AND (sbb1.sentinel_band_id = sbb.sentinel_band_id) AND (sbb1.deactive_date IS NULL))
26. 132.300 132.300 ↓ 0.0 0 2450
→

Index Scan using test_group_member_table_name_record_id_btree on test_group_member tgm1 (cost=0.00..6.31 rows=1 width=20) (actual time=0.054..0.054 rows=0 loops=2450)

  • Index Cond: (((tgm1.table_name)::text = 'sentinel_band_bleed'::text) AND (tgm1.record_id = sbb1.id))
  • Filter: ((tgm1.deactive_date IS NULL) AND (tgm1.name IS NULL) AND (tgm1.test_group_id = 2687))
27. 0.000 0.000 ↓ 0.0 0
→

Index Scan using test_group_pkey on test_group tg1 (cost=0.00..6.27 rows=1 width=12) (actual time=.. rows= loops=0)

  • Index Cond: (tg1.id = 2687)
  • Filter: ((tg1.deactive_date IS NULL) AND (tg1.agency_id = 77))