explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xLRY

Settings
# exclusive inclusive rows x rows loops node
1. 3.565 510.918 ↓ 12.9 3,560 1

Sort (cost=75,488.04..75,488.73 rows=277 width=568) (actual time=509.615..510.918 rows=3,560 loops=1)

  • Sort Key: rank_differece.difference
  • Sort Method: quicksort Memory: 587kB
2.          

CTE domain_id

3. 0.002 0.018 ↑ 1.0 1 1

Limit (cost=0.00..1.24 rows=1 width=4) (actual time=0.018..0.018 rows=1 loops=1)

4. 0.016 0.016 ↑ 1.0 1 1

Seq Scan on organic_sense_domain (cost=0.00..1.24 rows=1 width=4) (actual time=0.016..0.016 rows=1 loops=1)

  • Filter: ((owner_id = 2) AND ((domain_type)::text = 'client'::text))
5.          

CTE present_max_time_stamp

6. 0.003 454.656 ↑ 1.0 1 1

Limit (cost=10,710.30..34,584.75 rows=1 width=12) (actual time=454.655..454.656 rows=1 loops=1)

7. 454.653 454.653 ↑ 5.0 1 1

GroupAggregate (cost=10,710.30..130,082.53 rows=5 width=12) (actual time=454.653..454.653 rows=1 loops=1)

  • -> Bitmap Heap Scan on organic_sense_keywordrank "position" (cost=10710.30..127800.43 rows=456410 width=12) (actual time=38.173..25
  • Recheck Cond: (date = '2018-12-26'::date)
  • -> Bitmap Index Scan on os_date_time_stamp_index (cost=0.00..10596.20 rows=456410 width=0) (actual time=36.549..36.549 rows=4
  • Index Cond: (date = '2018-12-26'::date)
8.          

CTE past_max_time_stamp

9. 0.001 0.045 ↓ 0.0 0 1

Limit (cost=0.00..7.56 rows=1 width=12) (actual time=0.045..0.045 rows=0 loops=1)

10. 0.044 0.044 ↓ 0.0 0 1

GroupAggregate (cost=0.00..7.56 rows=1 width=12) (actual time=0.044..0.044 rows=0 loops=1)

  • -> Index Only Scan using os_date_time_stamp_index on organic_sense_keywordrank "position" (cost=0.00..7.55 rows=1 width=12) (actual
  • Index Cond: (date = '2018-05-01'::date)
  • Heap Fetches: 0
11.          

CTE present_keyword_rank

12. 2.833 484.828 ↓ 1.4 3,560 1

Nested Loop (cost=11,454.06..20,331.50 rows=2,501 width=101) (actual time=472.883..484.828 rows=3,560 loops=1)

13. 454.661 454.661 ↑ 1.0 1 1

CTE Scan on present_max_time_stamp (cost=0.00..0.02 rows=1 width=8) (actual time=454.659..454.661 rows=1 loops=1)

14. 27.313 27.334 ↓ 1.4 3,560 1

Nested Loop (cost=11,454.06..20,306.47 rows=2,501 width=109) (actual time=18.219..27.334 rows=3,560 loops=1)

  • -> Bitmap Heap Scan on organic_sense_keywordrank (cost=11454.06..20281.44 rows=2501 width=113) (actual time=18.194..24.276 rows=356
15. 0.021 0.021 ↑ 1.0 1 1

CTE Scan on domain_id (cost=0.00..0.02 rows=1 width=4) (actual time=0.020..0.021 rows=1 loops=1)

  • Recheck Cond: ((competitors_id = domain_id.id) AND (time_stamp = present_max_time_stamp.max))
  • -> Bitmap Index Scan on organic_sense_keywordrank_index (cost=0.00..11453.44 rows=2501 width=0) (actual time=18.064..18.064 r
  • Index Cond: ((competitors_id = domain_id.id) AND (time_stamp = present_max_time_stamp.max))
16.          

CTE rank_differece

17. 3.533 492.478 ↓ 1.4 3,560 1

Hash Left Join (cost=20,337.77..20,442.87 rows=2,501 width=16) (actual time=472.961..492.478 rows=3,560 loops=1)

  • Hash Cond: (present.keyword_id = public.organic_sense_keywordrank.keyword_id)
18. 488.891 488.891 ↓ 1.4 3,560 1

CTE Scan on present_keyword_rank present (cost=0.00..50.02 rows=2,501 width=12) (actual time=472.886..488.891 rows=3,560 loops=1)

19. 0.001 0.054 ↓ 0.0 0 1

Hash (cost=20,306.50..20,306.50 rows=2,501 width=8) (actual time=0.054..0.054 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
20. 0.001 0.053 ↓ 0.0 0 1

Nested Loop (cost=11,454.06..20,306.50 rows=2,501 width=8) (actual time=0.053..0.053 rows=0 loops=1)

21. 0.002 0.052 ↓ 0.0 0 1

Nested Loop (cost=0.00..0.05 rows=1 width=12) (actual time=0.052..0.052 rows=0 loops=1)

22. 0.004 0.004 ↑ 1.0 1 1

CTE Scan on domain_id (cost=0.00..0.02 rows=1 width=4) (actual time=0.002..0.004 rows=1 loops=1)

23. 0.046 0.046 ↓ 0.0 0 1

CTE Scan on past_max_time_stamp past_mtp (cost=0.00..0.02 rows=1 width=8) (actual time=0.046..0.046 rows=0 loops=1)

24. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on organic_sense_keywordrank (cost=11,454.06..20,281.44 rows=2,501 width=20) (never executed)

  • Recheck Cond: ((competitors_id = domain_id.id) AND (time_stamp = past_mtp.max))
25. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on organic_sense_keywordrank_index (cost=0.00..11,453.44 rows=2,501 width=0) (never executed)

  • Index Cond: ((competitors_id = domain_id.id) AND (time_stamp = past_mtp.max))
26. 4.115 507.353 ↓ 12.9 3,560 1

Hash Join (cost=46.71..108.88 rows=277 width=568) (actual time=480.118..507.353 rows=3,560 loops=1)

  • Hash Cond: (rank_differece.keyword_id = organic_sense_keyword.id)
27. 496.098 496.098 ↓ 1.4 3,560 1

CTE Scan on rank_differece (cost=0.00..50.02 rows=2,501 width=16) (actual time=472.964..496.098 rows=3,560 loops=1)

28. 2.158 7.140 ↓ 9.0 3,561 1

Hash (cost=41.77..41.77 rows=395 width=556) (actual time=7.140..7.140 rows=3,561 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 302kB
29. 4.968 4.982 ↓ 9.0 3,561 1

Nested Loop (cost=0.00..41.77 rows=395 width=556) (actual time=0.037..4.982 rows=3,561 loops=1)

  • -> Index Scan using organic_sense_keyword_group_id_791daf8a on organic_sense_keyword (cost=0.00..34.74 rows=592 width=40) (actu
30. 0.014 0.014 ↓ 8.0 8 1

Seq Scan on organic_sense_group (cost=0.00..1.11 rows=1 width=520) (actual time=0.009..0.014 rows=8 loops=1)

  • Filter: (owner_id = 2)
  • Rows Removed by Filter: 1
  • Index Cond: (group_id = organic_sense_group.id)
  • Filter: (owner_id = 2)