explain.depesz.com

A tool for finding a real cause for slow queries.

Result: OAS

options
Did it help? Consider supporting us - Bitcoin address: 12v2hUztAk2LgzQ9H9LMwuU32urHMjZQnq
# exclusive inclusive rows x rows loops node
1. 0.001 662.247 ↓ 0.0 0 1

GroupAggregate (cost=30,215.76..30,215.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=30,215.76..30,215.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=21,866.57..30,215.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=21,866.57..30,209.46 rows=1 width=36) (actual time=662.230..662.230 rows=0 loops=1)

5. 4.124 529.132 ↓ 2,450.0 2,450 1

Nested Loop (cost=21,866.57..30,203.14 rows=1 width=32) (actual time=461.660..529.132 rows=2,450 loops=1)

6. 12.116 510.839 ↓ 49.2 4,723 1

Hash Join (cost=21,866.57..29,611.00 rows=96 width=40) (actual time=459.482..510.839 rows=4,723 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 1,628 1

Hash Join (cost=20,769.83..28,441.94 rows=618 width=60) (actual time=427.710..466.985 rows=1,628 loops=1)

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

Nested Loop (cost=19,741.33..27,399.61 rows=680 width=40) (actual time=402.137..439.635 rows=1,628 loops=1)

9. 1.154 432.363 ↓ 2.1 1,628 1

Hash Join (cost=19,741.33..23,758.76 rows=775 width=32) (actual time=402.107..432.363 rows=1,628 loops=1)

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

Hash Join (cost=19,665.56..23,665.26 rows=2,661 width=28) (actual time=400.959..430.085 rows=1,628 loops=1)

  • Hash Cond: (tgmt.test_group_member_id = tgm.id)
11. 37.629 366.756 ↓ 1.6 15,353 1

Hash Join (cost=14,286.10..18,163.76 rows=9,542 width=12) (actual time=316.217..366.756 rows=15,353 loops=1)

  • Hash Cond: (tgmtr.test_group_member_target_id = tgmt.id)
12. 13.198 13.198 ↑ 1.0 18,473 1

Index Scan using test_group_member_target_result_test_status_id_btree on test_group_member_target_result tgmtr (cost=0.00..3,549.81 rows=18,595 width=8) (actual time=0.041..13.198 rows=18,473 loops=1)

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

Hash (cost=11,100.51..11,100.51 rows=254,847 width=20) (actual time=315.929..315.929 rows=314,427 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 17196kB
14. 182.907 182.907 ↓ 1.2 314,427 1

Seq Scan on test_group_member_target tgmt (cost=0.00..11,100.51 rows=254,847 width=20) (actual time=0.019..182.907 rows=314,427 loops=1)

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

Hash (cost=4,729.21..4,729.21 rows=52,020 width=24) (actual time=46.907..46.907 rows=51,759 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 2831kB
16. 20.572 28.192 ↑ 1.0 51,759 1

Bitmap Heap Scan on test_group_member tgm (cost=1,464.14..4,729.21 rows=52,020 width=24) (actual time=7.851..28.192 rows=51,759 loops=1)

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

Bitmap Index Scan on test_group_member_table_name_record_id_btree (cost=0.00..1,451.13 rows=52,246 width=0) (actual time=7.620..7.620 rows=51,899 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 1,628

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=1,628)

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

Hash (cost=687.30..687.30 rows=27,296 width=28) (actual time=25.542..25.542 rows=28,386 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1664kB
22. 14.790 14.790 ↓ 1.0 28,386 1

Seq Scan on sentinel_flock_bleed sfb (cost=0.00..687.30 rows=27,296 width=28) (actual time=0.010..14.790 rows=28,386 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 28,386 1

Hash (cost=687.30..687.30 rows=27,296 width=28) (actual time=31.738..31.738 rows=28,386 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1664kB
24. 18.040 18.040 ↓ 1.0 28,386 1

Seq Scan on sentinel_flock_bleed sfb1 (cost=0.00..687.30 rows=27,296 width=28) (actual time=0.026..18.040 rows=28,386 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 4,723

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=4,723)

  • 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 2,450

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=2,450)

  • 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))