explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 78Qb

Settings
# exclusive inclusive rows x rows loops node
1. 145.849 8,838.510 ↑ 1.8 875,224 1

Hash Left Join (cost=501,519.75..2,936,031.55 rows=1,592,625 width=53) (actual time=3,830.421..8,838.510 rows=875,224 loops=1)

  • Hash Cond: (ft_security_data.m_data_field_id = m_data_field.id)
2.          

CTE timestamps

3. 0.023 0.023 ↑ 1.0 61 1

Values Scan on "*VALUES*" (cost=0.00..0.76 rows=61 width=8) (actual time=0.002..0.023 rows=61 loops=1)

4.          

CTE unfiltered_revision_days

5. 10.452 3,490.469 ↑ 1.2 30,226 1

WindowAgg (cost=332,512.67..333,234.91 rows=36,112 width=24) (actual time=3,477.818..3,490.469 rows=30,226 loops=1)

6. 13.099 3,480.017 ↑ 1.2 30,226 1

Sort (cost=332,512.67..332,602.95 rows=36,112 width=16) (actual time=3,477.808..3,480.017 rows=30,226 loops=1)

  • Sort Key: timestamps.ts, its_universe_component_1."timestamp" DESC
  • Sort Method: quicksort Memory: 2,185kB
7. 6.959 3,466.918 ↑ 1.2 30,226 1

Nested Loop (cost=328,112.73..329,778.95 rows=36,112 width=16) (actual time=3,458.837..3,466.918 rows=30,226 loops=1)

  • Join Filter: (its_universe_component_1."timestamp" <= timestamps.ts)
  • Rows Removed by Join Filter: 57,980
8. 0.039 0.039 ↑ 1.0 61 1

CTE Scan on timestamps (cost=0.00..1.22 rows=61 width=8) (actual time=0.003..0.039 rows=61 loops=1)

9. 3.888 3,459.920 ↑ 1.2 1,446 61

Materialize (cost=328,112.73..328,157.13 rows=1,776 width=8) (actual time=56.653..56.720 rows=1,446 loops=61)

10. 1,874.305 3,456.032 ↑ 1.2 1,446 1

HashAggregate (cost=328,112.73..328,130.49 rows=1,776 width=8) (actual time=3,455.856..3,456.032 rows=1,446 loops=1)

  • Group Key: its_universe_component_1."timestamp
11. 1,581.727 1,581.727 ↓ 1.0 15,636,110 1

Index Only Scan using its_universe_component_idx_universe_covering_2 on its_universe_component its_universe_component_1 (cost=0.56..289,045.04 rows=15,627,079 width=8) (actual time=0.018..1,581.727 rows=15,636,110 loops=1)

  • Index Cond: (m_universe_id = 179)
  • Heap Fetches: 0
12.          

CTE revision_days

13. 3,498.412 3,498.412 ↑ 4.2 43 1

CTE Scan on unfiltered_revision_days (cost=0.00..812.52 rows=181 width=16) (actual time=3,477.821..3,498.412 rows=43 loops=1)

  • Filter: (distance = 1)
  • Rows Removed by Filter: 30,183
14. 4,426.325 8,692.576 ↑ 1.8 875,224 1

Hash Right Join (cost=167,465.45..2,597,705.29 rows=1,592,625 width=36) (actual time=3,830.328..8,692.576 rows=875,224 loops=1)

  • Hash Cond: (ft_security_data.security_id = its_universe_component.m_security_id)
  • Join Filter: (ft_security_data.from_to @> revision_days."timestamp")
  • Rows Removed by Join Filter: 38,030,092
15. 443.162 443.162 ↑ 1.0 1,612,291 1

Index Only Scan using ft_security_data_idx_id_covering on ft_security_data (cost=0.57..152,653.72 rows=1,661,363 width=41) (actual time=0.022..443.162 rows=1,612,291 loops=1)

  • Index Cond: (m_data_field_id = ANY ('{153,154}'::integer[]))
  • Heap Fetches: 1,612,291
16. 96.737 3,823.089 ↑ 3.4 462,501 1

Hash (cost=138,225.07..138,225.07 rows=1,592,625 width=24) (actual time=3,823.089..3,823.089 rows=462,501 loops=1)

  • Buckets: 2,097,152 Batches: 2 Memory Usage: 29,569kB
17. 48.422 3,726.352 ↑ 3.4 462,501 1

Nested Loop (cost=0.44..138,225.07 rows=1,592,625 width=24) (actual time=3,478.222..3,726.352 rows=462,501 loops=1)

18. 3,498.448 3,498.448 ↑ 4.2 43 1

CTE Scan on revision_days (cost=0.00..3.62 rows=181 width=16) (actual time=3,477.823..3,498.448 rows=43 loops=1)

19. 179.482 179.482 ↓ 1.2 10,756 43

Index Scan using its_universe_component_idx_timestamp on its_universe_component (cost=0.44..675.66 rows=8,799 width=16) (actual time=0.837..4.174 rows=10,756 loops=43)

  • Index Cond: ("timestamp" = revision_days."timestamp")
  • Filter: (m_universe_id = 179)
  • Rows Removed by Filter: 107
20. 0.035 0.085 ↑ 1.0 194 1

Hash (cost=3.68..3.68 rows=194 width=25) (actual time=0.085..0.085 rows=194 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
21. 0.050 0.050 ↑ 1.0 194 1

Index Only Scan using m_data_field_idx_id_covering on m_data_field (cost=0.27..3.68 rows=194 width=25) (actual time=0.010..0.050 rows=194 loops=1)

  • Heap Fetches: 141