explain.depesz.com

A tool for finding a real cause for slow queries.

Result: 5Z3

options
# exclusive inclusive rows x rows loops node
1. 0.002 10143.444 ↓ 0.0 0 1
→

GroupAggregate (cost=15785.17..15785.20 rows=1 width=16) (actual time=10143.444..10143.444 rows=0 loops=1)

2. 0.056 10143.442 ↓ 0.0 0 1
→

Sort (cost=15785.17..15785.18 rows=1 width=16) (actual time=10143.442..10143.442 rows=0 loops=1)

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

Nested Loop (cost=11019.92..15785.16 rows=1 width=16) (actual time=10143.386..10143.386 rows=0 loops=1)

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

Nested Loop (cost=11019.92..15778.88 rows=1 width=36) (actual time=10143.385..10143.385 rows=0 loops=1)

5. 212.782 10004.137 ↓ 2450.0 2450 1
→

Nested Loop (cost=11019.92..15772.55 rows=1 width=32) (actual time=3551.036..10004.137 rows=2450 loops=1)

6. 176.117 8828.394 ↓ 320987.0 320987 1
→

Nested Loop (cost=11019.92..15772.14 rows=1 width=40) (actual time=3525.161..8828.394 rows=320987 loops=1)

7. 135.840 7374.205 ↓ 319518.0 319518 1
→

Nested Loop (cost=11019.92..15771.34 rows=1 width=28) (actual time=3525.114..7374.205 rows=319518 loops=1)

8. 207.539 5795.613 ↓ 180.3 360688 1
→

Hash Join (cost=11019.92..14609.79 rows=2001 width=36) (actual time=3525.096..5795.613 rows=360688 loops=1)

  • Hash Cond: (tgm.test_group_id = tg.id)
9. 2041.438 5586.825 ↓ 55.1 380517 1
→

Hash Join (cost=10945.62..14489.59 rows=6902 width=32) (actual time=3523.816..5586.825 rows=380517 loops=1)

  • Hash Cond: (tgm.record_id = sbb.id)
10. 21.951 31.180 ↑ 1.0 51759 1
→

Bitmap Heap Scan on test_group_member tgm (cost=1459.23..4735.76 rows=52914 width=24) (actual time=9.456..31.180 rows=51759 loops=1)

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

Bitmap Index Scan on test_group_member_table_name_record_id_btree (cost=0.00..1446.00 rows=53162 width=0) (actual time=9.229..9.229 rows=51899 loops=1)

  • Index Cond: ((table_name)::text = 'sentinel_band_bleed'::text)
12. 1241.659 3514.207 ↓ 54.0 1941368 1
→

Hash (cost=9037.17..9037.17 rows=35937 width=24) (actual time=3514.207..3514.207 rows=1941368 loops=1)

  • Buckets: 4096 Batches: 4 (originally 1) Memory Usage: 32769kB
13. 746.477 2272.548 ↓ 54.0 1941368 1
→

Nested Loop (cost=1100.34..9037.17 rows=35937 width=24) (actual time=29.271..2272.548 rows=1941368 loops=1)

14. 292.045 338.871 ↓ 53.0 237440 1
→

Hash Join (cost=1100.34..4943.56 rows=4477 width=16) (actual time=29.229..338.871 rows=237440 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 28386 1
→

Seq Scan on sentinel_flock_bleed sfb (cost=0.00..688.21 rows=27475 width=28) (actual time=0.022..17.680 rows=28386 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 28386 1
→

Hash (cost=688.21..688.21 rows=27475 width=28) (actual time=29.146..29.146 rows=28386 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1664kB
17. 17.167 17.167 ↓ 1.0 28386 1
→

Seq Scan on sentinel_flock_bleed sfb1 (cost=0.00..688.21 rows=27475 width=28) (actual time=0.004..17.167 rows=28386 loops=1)

  • Filter: ((deactive_date IS NULL) AND (read_only_bit IS TRUE) AND (worksheet_bit IS FALSE))
18. 1187.200 1187.200 ↑ 1.1 8 237440
→

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=237440)

  • 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. 1442.752 1442.752 ↑ 1.0 1 360688
→

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=360688)

  • 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. 1278.072 1278.072 ↑ 1.0 1 319518
→

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=319518)

  • 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 320987
→

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=320987)

  • 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 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.056..0.056 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))
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))