explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KP3L

Settings
# exclusive inclusive rows x rows loops node
1. 4.390 800.640 ↑ 2.0 182 1

Sort (cost=36,841.76..36,842.67 rows=365 width=4,050) (actual time=800.389..800.640 rows=182 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: 375kB
2. 4.592 796.250 ↑ 2.0 182 1

Group (cost=36,700.30..36,826.23 rows=365 width=4,050) (actual time=791.411..796.250 rows=182 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.609 791.658 ↑ 2.0 182 1

Sort (cost=36,700.30..36,701.21 rows=365 width=3,978) (actual time=791.363..791.658 rows=182 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: 369kB
4. 1.122 790.049 ↑ 2.0 182 1

Nested Loop Left Join (cost=509.48..36,684.77 rows=365 width=3,978) (actual time=26.147..790.049 rows=182 loops=1)

5. 0.998 787.107 ↑ 2.0 182 1

Hash Left Join (cost=509.07..35,916.98 rows=365 width=3,986) (actual time=26.136..787.107 rows=182 loops=1)

  • Hash Cond: ((g_challenge.j_collective_account_id)::text = ((gc.j_row_id)::text || '_CollectiveAccount'::text))
6. 1.383 783.073 ↑ 2.0 182 1

Nested Loop (cost=439.60..35,710.38 rows=365 width=3,962) (actual time=23.082..783.073 rows=182 loops=1)

  • Join Filter: (g_challenge.j_row_id = tps.j_item_id)
7. 1.391 779.534 ↑ 2.1 196 1

Nested Loop Anti Join (cost=439.31..35,489.05 rows=406 width=3,947) (actual time=23.065..779.534 rows=196 loops=1)

8. 636.750 774.131 ↑ 2.7 236 1

Hash Join (cost=439.02..35,069.17 rows=633 width=3,947) (actual time=21.589..774.131 rows=236 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: 3434
9. 46.449 116.426 ↓ 2.0 3,678 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.081..116.426 rows=3,678 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: 5
10.          

SubPlan (forIndex 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.019..0.019 rows=1 loops=3,683)

12. 9.447 20.955 ↑ 1.0 5,268 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 353kB
13. 11.508 11.508 ↑ 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.030..11.508 rows=5,268 loops=1)

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

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

  • Index Cond: (j_item_id = g_challenge.j_row_id)
  • Filter: ((j_value)::text = 'bfr_8236'::text)
  • Rows Removed by Filter: 6
15. 2.156 2.156 ↑ 1.0 1 196

Index Scan using ig_challenge_catset_i on g_challenge_catset tps (cost=0.29..0.53 rows=1 width=15) (actual time=0.009..0.011 rows=1 loops=196)

  • 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.633 3.036 ↓ 1.0 739 1

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

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

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

18. 1.820 1.820 ↓ 0.0 0 182

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.009..0.010 rows=0 loops=182)

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