explain.depesz.com

A tool for finding a real cause for slow queries.

Result: 5Z3

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

GroupAggregate (cost=15,785.17..15,785.20 rows=1 width=16) (actual time=10,143.444..10,143.444 rows=0 loops=1)

2. 0.056 10,143.442 ↓ 0.0 0 1

Sort (cost=15,785.17..15,785.18 rows=1 width=16) (actual time=10,143.442..10,143.442 rows=0 loops=1)

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

Nested Loop (cost=11,019.92..15,785.16 rows=1 width=16) (actual time=10,143.386..10,143.386 rows=0 loops=1)

  • Join Filter: (tg1.id <> tg.id)
4. 2.048 10,143.385 ↓ 0.0 0 1

Nested Loop (cost=11,019.92..15,778.88 rows=1 width=36) (actual time=10,143.385..10,143.385 rows=0 loops=1)

5. 212.782 10,004.137 ↓ 2,450.0 2,450 1

Nested Loop (cost=11,019.92..15,772.55 rows=1 width=32) (actual time=3,551.036..10,004.137 rows=2,450 loops=1)

6. 176.117 8,828.394 ↓ 320,987.0 320,987 1

Nested Loop (cost=11,019.92..15,772.14 rows=1 width=40) (actual time=3,525.161..8,828.394 rows=320,987 loops=1)

7. 135.840 7,374.205 ↓ 319,518.0 319,518 1

Nested Loop (cost=11,019.92..15,771.34 rows=1 width=28) (actual time=3,525.114..7,374.205 rows=319,518 loops=1)

8. 207.539 5,795.613 ↓ 180.3 360,688 1

Hash Join (cost=11,019.92..14,609.79 rows=2,001 width=36) (actual time=3,525.096..5,795.613 rows=360,688 loops=1)

  • Hash Cond: (tgm.test_group_id = tg.id)
9. 2,041.438 5,586.825 ↓ 55.1 380,517 1

Hash Join (cost=10,945.62..14,489.59 rows=6,902 width=32) (actual time=3,523.816..5,586.825 rows=380,517 loops=1)

  • Hash Cond: (tgm.record_id = sbb.id)
10. 21.951 31.180 ↑ 1.0 51,759 1

Bitmap Heap Scan on test_group_member tgm (cost=1,459.23..4,735.76 rows=52,914 width=24) (actual time=9.456..31.180 rows=51,759 loops=1)

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

Bitmap Index Scan on test_group_member_table_name_record_id_btree (cost=0.00..1,446.00 rows=53,162 width=0) (actual time=9.229..9.229 rows=51,899 loops=1)

  • Index Cond: ((table_name)::text = 'sentinel_band_bleed'::text)
12. 1,241.659 3,514.207 ↓ 54.0 1,941,368 1

Hash (cost=9,037.17..9,037.17 rows=35,937 width=24) (actual time=3,514.207..3,514.207 rows=1,941,368 loops=1)

  • Buckets: 4096 Batches: 4 (originally 1) Memory Usage: 32769kB
13. 746.477 2,272.548 ↓ 54.0 1,941,368 1

Nested Loop (cost=1,100.34..9,037.17 rows=35,937 width=24) (actual time=29.271..2,272.548 rows=1,941,368 loops=1)

14. 292.045 338.871 ↓ 53.0 237,440 1

Hash Join (cost=1,100.34..4,943.56 rows=4,477 width=16) (actual time=29.229..338.871 rows=237,440 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))
15. 17.680 17.680 ↓ 1.0 28,386 1

Seq Scan on sentinel_flock_bleed sfb (cost=0.00..688.21 rows=27,475 width=28) (actual time=0.022..17.680 rows=28,386 loops=1)

  • Filter: ((deactive_date IS NULL) AND (read_only_bit IS TRUE) AND (worksheet_bit IS FALSE))
16. 11.979 29.146 ↓ 1.0 28,386 1

Hash (cost=688.21..688.21 rows=27,475 width=28) (actual time=29.146..29.146 rows=28,386 loops=1)

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

Seq Scan on sentinel_flock_bleed sfb1 (cost=0.00..688.21 rows=27,475 width=28) (actual time=0.004..17.167 rows=28,386 loops=1)

  • Filter: ((deactive_date IS NULL) AND (read_only_bit IS TRUE) AND (worksheet_bit IS FALSE))
18. 1,187.200 1,187.200 ↑ 1.1 8 237,440

Index Scan using sentinel_band_bleed_sentinel_flock_bleed_id_key on sentinel_band_bleed sbb (cost=0.00..0.80 rows=9 width=24) (actual time=0.003..0.005 rows=8 loops=237,440)

  • Index Cond: ((sbb.sentinel_flock_bleed_id = sfb.id) AND (sbb.deactive_date IS NULL))
19. 0.323 1.249 ↓ 1.1 841 1

Hash (cost=65.16..65.16 rows=732 width=12) (actual time=1.249..1.249 rows=841 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 37kB
20. 0.926 0.926 ↓ 1.1 841 1

Index Scan using test_group_agency_id_btree on test_group tg (cost=0.00..65.16 rows=732 width=12) (actual time=0.049..0.926 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))
21. 1,442.752 1,442.752 ↑ 1.0 1 360,688

Index Scan using sentinel_band_bleed_sentinel_flock_bleed_id_key on sentinel_band_bleed sbb1 (cost=0.00..0.57 rows=1 width=24) (actual time=0.003..0.004 rows=1 loops=360,688)

  • Index Cond: ((sbb1.sentinel_flock_bleed_id = sfb1.id) AND (sbb1.sentinel_band_id = sbb.sentinel_band_id) AND (sbb1.deactive_date IS NULL))
22. 1,278.072 1,278.072 ↑ 1.0 1 319,518

Index Scan using test_group_member_target_test_group_member_id_key on test_group_member_target tgmt (cost=0.00..0.79 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=319,518)

  • Index Cond: ((tgmt.test_group_member_id = tgm.id) AND (tgmt.deactive_date IS NULL))
  • Filter: (tgmt.test_target_id = ANY ('{4,6,7}'::integer[]))
23. 962.961 962.961 ↓ 0.0 0 320,987

Index Scan using test_group_member_target_result_test_group_member_target_id_key on test_group_member_target_result tgmtr (cost=0.00..0.40 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=320,987)

  • Index Cond: ((tgmtr.test_group_member_target_id = tgmt.id) AND (tgmtr.deactive_date IS NULL))
  • Filter: (tgmtr.test_status_id = 3)
24. 137.200 137.200 ↓ 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.056..0.056 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))
25. 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))