explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LbKt

Settings
# exclusive inclusive rows x rows loops node
1. 0.025 12,470.061 ↑ 1.5 2 1

Nested Loop (cost=186,371.03..186,395.15 rows=3 width=74) (actual time=12,470.037..12,470.061 rows=2 loops=1)

  • -> Index Scan using organic_sense_keyword_pkey on organic_sense_keyword (cost=0.00..7.61 rows=1 width=36) (actual time=0.007..0.008 rows=1 loops=
2.          

CTE keyword_position

3. 0.230 12,469.968 ↑ 3.0 2 1

Nested Loop Semi Join (cost=181,054.18..186,370.83 rows=6 width=16) (actual time=12,451.962..12,469.968 rows=2 loops=1)

  • Join Filter: ("position".time_stamp = "ANY_subquery".max)
  • Rows Removed by Join Filter: 261
4. 0.758 141.070 ↓ 1.4 132 1

Nested Loop (cost=36.04..5,341.11 rows=95 width=24) (actual time=2.649..141.070 rows=132 loops=1)

  • Join Filter: ("position".competitors_id = public.organic_sense_domain.id)
  • Rows Removed by Join Filter: 1364
5. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on organic_sense_domain (cost=0.00..1.25 rows=1 width=4) (actual time=0.003..0.007 rows=1 loops=1)

  • Filter: ((owner_id = 2) AND ((domain_type)::text = 'client'::text))
  • Rows Removed by Filter: 15
6. 139.725 140.305 ↓ 1.0 1,496 1

Nested Loop (cost=36.04..5,322.03 rows=1,426 width=24) (actual time=2.587..140.305 rows=1,496 loops=1)

  • -> Bitmap Heap Scan on organic_sense_keywordrank "position" (cost=36.04..5220.24 rows=1426 width=24) (actual time=2.426..138.
7. 0.580 0.580 ↑ 1.0 1 1

Seq Scan on organic_sense_keyword (cost=0.00..87.53 rows=1 width=4) (actual time=0.157..0.580 rows=1 loops=1)

  • Filter: ((owner_id = 2) AND ((name)::text = 'sugar reduction market forecast'::text))
  • Rows Removed by Filter: 3561
  • Recheck Cond: (keyword_id = public.organic_sense_keyword.id)
  • -> Bitmap Index Scan on organic_sense_keywordrank_keyword_id_c4de664a (cost=0.00..35.68 rows=1426 width=0) (actual time
  • Index Cond: (keyword_id = public.organic_sense_keyword.id)
8. 0.187 12,328.668 ↑ 4.0 2 132

Materialize (cost=181,018.14..181,018.34 rows=8 width=8) (actual time=93.398..93.399 rows=2 loops=132)

9. 0.005 12,328.481 ↑ 4.0 2 1

Subquery Scan on ANY_subquery (cost=181,018.14..181,018.30 rows=8 width=8) (actual time=12,328.477..12,328.481 rows=2 loops=1)

10. 12,328.476 12,328.476 ↑ 4.0 2 1

HashAggregate (cost=181,018.14..181,018.22 rows=8 width=12) (actual time=12,328.475..12,328.476 rows=2 loops=1)

  • -> Seq Scan on organic_sense_keywordrank "position" (cost=0.00..177899.10 rows=623808 width=12) (actual time=720.793..1
  • Filter: ((date >= '2018-12-25'::date) AND (date <= '2018-12-26'::date))
  • Rows Removed by Filter: 4494210
11. 0.044 12,470.036 ↑ 1.5 2 1

Hash Join (cost=0.20..1.47 rows=3 width=42) (actual time=12,470.017..12,470.036 rows=2 loops=1)

  • Hash Cond: (public.organic_sense_domain.id = keyword_position.competitors_id)
  • Index Cond: (id = keyword_position.keyword_id)
  • Filter: (owner_id = 2)
12. 0.011 0.011 ↓ 1.9 15 1

Seq Scan on organic_sense_domain (cost=0.00..1.21 rows=8 width=30) (actual time=0.008..0.011 rows=15 loops=1)

  • Filter: (owner_id = 2)
  • Rows Removed by Filter: 1
13. 0.005 12,469.981 ↑ 3.0 2 1

Hash (cost=0.12..0.12 rows=6 width=16) (actual time=12,469.981..12,469.981 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
14. 12,469.976 12,469.976 ↑ 3.0 2 1

CTE Scan on keyword_position (cost=0.00..0.12 rows=6 width=16) (actual time=12,451.966..12,469.976 rows=2 loops=1)