explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 92KyA

Settings
# exclusive inclusive rows x rows loops node
1. 23.089 533,522.241 ↓ 4,342.0 4,342 1

GroupAggregate (cost=6,568.43..6,568.47 rows=1 width=28) (actual time=533,488.141..533,522.241 rows=4,342 loops=1)

  • Group Key: survey_sentemployeegroup.original_id
  • Filter: (count(survey_sentemployeegroup_takers.surveytaker_id) FILTER (WHERE survey_surveytaker.complete) > 0)
  • Rows Removed by Filter: 649
  • time=1.982..1.982 rows=24,475 loops=1)
2. 268.993 533,499.152 ↓ 224,512.0 224,512 1

Sort (cost=6,568.43..6,568.43 rows=1 width=9) (actual time=533,488.116..533,499.152 rows=224,512 loops=1)

  • Sort Key: survey_sentemployeegroup.original_id
  • Sort Method: quicksort Memory: 16,669kB
3. 131,404.866 533,230.159 ↓ 224,512.0 224,512 1

Nested Loop (cost=1,539.64..6,568.42 rows=1 width=9) (actual time=25.207..533,230.159 rows=224,512 loops=1)

  • Join Filter: (survey_sentemployeegroup_takers.surveytaker_id = "ANY_subquery".surveytaker_id)
  • Rows Removed by Join Filter: 1,519,581,270
4. 661.421 2,578.543 ↓ 228,141.0 228,141 1

Nested Loop (cost=1,539.07..5,685.04 rows=1 width=13) (actual time=3.125..2,578.543 rows=228,141 loops=1)

5. 95.326 320.135 ↓ 2,193.7 228,141 1

Nested Loop (cost=1,538.63..5,635.27 rows=104 width=8) (actual time=3.112..320.135 rows=228,141 loops=1)

6. 14.077 17.024 ↓ 651.4 7,165 1

Bitmap Heap Scan on survey_sentemployeegroup (cost=1,538.06..1,582.44 rows=11 width=8) (actual time=3.095..17.024 rows=7,165 loops=1)

  • Recheck Cond: ((instance_id = 25,152) AND (category_id = ANY ('{143773,143772,143771,143770,143769,142087,142066,142065,142064,142063,142062,142061,142060,142059,142058,142057,142056,142055,142054,142053,142052,142051,142050,142049,142048,142047,142046,142045,142044}'::integer[])))
  • Filter: (original_id IS NOT NULL)
  • Heap Blocks: exact=910
7. 2.230 2.947 ↓ 0.0 0 1

BitmapAnd (cost=1,538.06..1,538.06 rows=11 width=0) (actual time=2.947..2.947 rows=0 loops=1)

  • -> Bitmap Index Scan on survey_sentemployeegroup_instance_id (cost=0.00..93.68 rows=3,349 width=0) (actual
  • Index Cond: (instance_id = 25,152)
8. 0.717 0.717 ↑ 7.1 7,165 1

Bitmap Index Scan on survey_sentemployeegroup_category_id (cost=0.00..1,444.13 rows=51,192 width=0) (actual time=0.717..0.717 rows=7,165 loops=1)

  • Index Cond: (category_id = ANY ('{143773,143772,143771,143770,143769,142087,142066,142065,142064,142063,142062,142061,142060,142059,142058,142057,142056,142055,142054,142053,142052,142051,142050,142049,142048,142047,142046,142045,142044}'::integer[]))
9. 207.785 207.785 ↑ 138.2 32 7,165

Index Only Scan using survey_sentemployeeg_sentemployeegroup_id_4c60698edfdf70f4_uniq on survey_sentemployeegroup_takers (cost=0.57..324.22 rows=4,422 width=8) (actual time=0.007..0.029 rows=32 loops=7,165)

  • Index Cond: (sentemployeegroup_id = survey_sentemployeegroup.id)
  • Heap Fetches: 0
10. 1,596.987 1,596.987 ↑ 1.0 1 228,141

Index Scan using survey_surveytaker_pkey on survey_surveytaker (cost=0.43..0.48 rows=1 width=5) (actual time=0.007..0.007 rows=1 loops=228,141)

  • Index Cond: (id = survey_sentemployeegroup_takers.surveytaker_id)
  • Filter: ((complete OR (NOT skip_when_counting) OR (skip_when_counting IS NULL)) AND (instance_id = 25,152))
11. 218,330.937 399,246.750 ↓ 1.6 6,662 228,141

Subquery Scan on ANY_subquery (cost=0.57..830.71 rows=4,213 width=4) (actual time=0.000..1.750 rows=6,662 loops=228,141)

12. 180,903.621 180,915.813 ↓ 1.6 6,662 228,141

HashSetOp Intersect (cost=0.57..788.58 rows=4,213 width=8) (actual time=0.000..0.793 rows=6,662 loops=228,141)

13. 2.669 12.192 ↓ 3.2 27,155 1

Append (cost=0.57..767.43 rows=8,462 width=8) (actual time=0.030..12.192 rows=27,155 loops=1)

14. 1.879 4.889 ↓ 3.3 14,033 1

Subquery Scan on *SELECT* 1 (cost=0.57..362.56 rows=4,231 width=8) (actual time=0.029..4.889 rows=14,033 loops=1)

15. 3.010 3.010 ↓ 3.3 14,033 1

Index Only Scan using survey_sentemployeeg_sentemployeegroup_id_4c60698edfdf70f4_uniq on survey_sentemployeegroup_takers survey_sentemployeegroup_takers_1 (cost=0.57..320.25 rows=4,231 width=4) (actual time=0.028..3.010 rows=14,033 loops=1)

  • Index Cond: (sentemployeegroup_id = 42,172,570)
  • Heap Fetches: 0
16. 1.786 4.634 ↓ 3.1 13,122 1

Subquery Scan on *SELECT* 2 (cost=0.57..362.56 rows=4,231 width=8) (actual time=0.027..4.634 rows=13,122 loops=1)

17. 2.848 2.848 ↓ 3.1 13,122 1

Index Only Scan using survey_sentemployeeg_sentemployeegroup_id_4c60698edfdf70f4_uniq on survey_sentemployeegroup_takers survey_sentemployeegroup_takers_2 (cost=0.57..320.25 rows=4,231 width=4) (actual time=0.026..2.848 rows=13,122 loops=1)

  • Index Cond: (sentemployeegroup_id = 42,590,649)
  • Heap Fetches: 0
Planning time : 1.843 ms
Execution time : 533,523.707 ms