explain.depesz.com

PostgreSQL's explain analyze made readable

Result: C1mG

Settings
# exclusive inclusive rows x rows loops node
1. 4.869 494.364 ↑ 2.3 156 1

Sort (cost=36,841.76..36,842.67 rows=365 width=4,050) (actual time=494.046..494.364 rows=156 loops=1)

  • Sort Key: (CASE WHEN (COALESCE(btrim(g_challenge.j_geolocation), ''::text) = ''::text) THEN '100000'::numeric ELSE distance_point(g_challenge.j_geolocation, '47.218371'::text, ' -1.553621000000021'::text) END), ((((CASE WHEN (g_challenge.j_start_date < n (...)
  • Sort Method: quicksort Memory: 327kB
2. 3.937 489.495 ↑ 2.3 156 1

Group (cost=36,700.30..36,826.23 rows=365 width=4,050) (actual time=485.266..489.495 rows=156 loops=1)

  • Group Key: typenp.j_value, g_challenge.j_row_id, typenp.j_item_id, tps.j_item_id, tps.j_value, gc.j_row_id, gcmp.j_row_id
3. 1.790 485.558 ↑ 2.3 156 1

Sort (cost=36,700.30..36,701.21 rows=365 width=3,978) (actual time=485.215..485.558 rows=156 loops=1)

  • Sort Key: typenp.j_value, g_challenge.j_row_id, tps.j_value, gc.j_row_id, gcmp.j_row_id
  • Sort Method: quicksort Memory: 323kB
4. 1.227 483.768 ↑ 2.3 156 1

Nested Loop Left Join (cost=509.48..36,684.77 rows=365 width=3,978) (actual time=38.860..483.768 rows=156 loops=1)

5. 0.977 480.669 ↑ 2.3 156 1

Hash Left Join (cost=509.07..35,916.98 rows=365 width=3,986) (actual time=38.819..480.669 rows=156 loops=1)

  • Hash Cond: ((g_challenge.j_collective_account_id)::text = ((gc.j_row_id)::text || '_CollectiveAccount'::text))
6. 1.299 476.512 ↑ 2.3 156 1

Nested Loop (cost=439.60..35,710.38 rows=365 width=3,962) (actual time=35.615..476.512 rows=156 loops=1)

  • Join Filter: (g_challenge.j_row_id = tps.j_item_id)
7. 1.313 472.847 ↑ 2.4 169 1

Nested Loop Anti Join (cost=439.31..35,489.05 rows=406 width=3,947) (actual time=35.596..472.847 rows=169 loops=1)

8. 334.799 467.814 ↑ 3.4 186 1

Hash Join (cost=439.02..35,069.17 rows=633 width=3,947) (actual time=29.958..467.814 rows=186 loops=1)

  • Hash Cond: (g_challenge.j_row_id = typenp.j_item_id)
  • Join Filter: ((distance_point(g_challenge.j_geolocation, '47.218371'::text, ' -1.553621000000021'::text) < '20'::numeric) OR ((typenp.j_value)::text = ANY ('{bfr_8227,jbm_5232}'::text[])))
  • Rows Removed by Join Filter: 1592
9. 41.774 111.751 ↑ 1.0 1,786 1

Index Scan using ig_challenge_pstatus on g_challenge (cost=0.28..34,071.42 rows=1,841 width=3,932) (actual time=0.150..111.751 rows=1,786 loops=1)

  • Index Cond: (j_pstatus = 0)
  • Filter: (((j_start_date >= '2015-08-05 00:00:00'::timestamp without time zone) OR ((j_start_date <= '0201-08-05 00:00:00'::timestamp without time zone) AND (j_end_date >= '2021-08-05 00:00:00'::timestamp wi (...)
  • Rows Removed by Filter: 1897
10.          

SubPlan (for Index Scan)

11. 69.977 69.977 ↑ 1,000.0 1 3,683

Function Scan on generate_series alldays (cost=0.00..15.00 rows=1,000 width=1) (actual time=0.017..0.019 rows=1 loops=3,683)

12. 9.507 21.264 ↑ 1.0 5,268 1

Hash (cost=372.31..372.31 rows=5,314 width=15) (actual time=21.262..21.264 rows=5,268 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 353kB
13. 11.757 11.757 ↑ 1.0 5,268 1

Index Scan using ig_challenge_catset on g_challenge_catset typenp (cost=0.29..372.31 rows=5,314 width=15) (actual time=0.029..11.757 rows=5,268 loops=1)

  • Index Cond: ((j_value)::text = ANY ('{jbm_5233,jbm_5232,bfr_8227}'::text[]))
14. 3.720 3.720 ↓ 0.0 0 186

Index Scan using ig_challenge_catset_i on g_challenge_catset (cost=0.29..0.66 rows=1 width=8) (actual time=0.020..0.020 rows=0 loops=186)

  • Index Cond: (j_item_id = g_challenge.j_row_id)
  • Filter: ((j_value)::text = 'bfr_8236'::text)
  • Rows Removed by Filter: 7
15. 2.366 2.366 ↑ 1.0 1 169

Index Scan using ig_challenge_catset_i on g_challenge_catset tps (cost=0.29..0.53 rows=1 width=15) (actual time=0.012..0.014 rows=1 loops=169)

  • Index Cond: (j_item_id = typenp.j_item_id)
  • Filter: ((j_value)::text = ANY ('{aga_7777,aga_7778,aga_7776}'::text[]))
  • Rows Removed by Filter: 7
16. 1.669 3.180 ↓ 1.0 739 1

Hash (cost=60.32..60.32 rows=732 width=24) (actual time=3.178..3.180 rows=739 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 49kB
17. 1.511 1.511 ↓ 1.0 739 1

Seq Scan on g_collectiveaccount gc (cost=0.00..60.32 rows=732 width=24) (actual time=0.013..1.511 rows=739 loops=1)

18. 1.872 1.872 ↓ 0.0 0 156

Index Scan using ig_custom_member_profile_membe on g_custom_member_profile gcmp (cost=0.41..2.09 rows=1 width=24) (actual time=0.011..0.012 rows=0 loops=156)

  • Index Cond: ((gc.j_author_id)::text = (j_member_id)::text)
Planning time : 4.559 ms
Execution time : 495.393 ms