explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sGxT

Settings
# exclusive inclusive rows x rows loops node
1. 3.804 42,121.711 ↓ 2,000.0 2,000 1

Nested Loop (cost=4,024.46..4,027.02 rows=1 width=53) (actual time=37.328..42,121.711 rows=2,000 loops=1)

2.          

CTE timestamps

3. 0.002 0.002 ↑ 1.0 2 1

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

4.          

CTE unfiltered_revision_days

5. 0.057 19.352 ↓ 113.0 113 1

WindowAgg (cost=0.65..0.67 rows=1 width=24) (actual time=19.288..19.352 rows=113 loops=1)

6. 0.049 19.295 ↓ 113.0 113 1

Sort (cost=0.65..0.65 rows=1 width=16) (actual time=19.280..19.295 rows=113 loops=1)

  • Sort Key: timestamps.ts, its_universe_component_07_1."timestamp" DESC
  • Sort Method: quicksort Memory: 30kB
7. 0.036 19.246 ↓ 113.0 113 1

Nested Loop (cost=0.55..0.64 rows=1 width=16) (actual time=19.208..19.246 rows=113 loops=1)

  • Join Filter: (its_universe_component_07_1."timestamp" <= timestamps.ts)
  • Rows Removed by Join Filter: 3
8. 6.279 19.210 ↓ 58.0 58 1

HashAggregate (cost=0.55..0.56 rows=1 width=8) (actual time=19.203..19.210 rows=58 loops=1)

  • Group Key: its_universe_component_07_1."timestamp
9. 3.797 12.931 ↓ 58,002.0 58,002 1

Append (cost=0.43..0.55 rows=1 width=8) (actual time=0.019..12.931 rows=58,002 loops=1)

10. 9.134 9.134 ↓ 58,002.0 58,002 1

Index Only Scan using its_universe_component_07_m_universe_id_timestamp_m_securit_idx on its_universe_component_07 its_universe_component_07_1 (cost=0.43..0.55 rows=1 width=8) (actual time=0.018..9.134 rows=58,002 loops=1)

  • Index Cond: (m_universe_id = 2,161)
  • Heap Fetches: 58,002
11. 0.000 0.000 ↑ 1.0 2 58

CTE Scan on timestamps (cost=0.00..0.04 rows=2 width=8) (actual time=0.000..0.000 rows=2 loops=58)

12.          

CTE revision_days

13. 19.385 19.385 ↓ 2.0 2 1

CTE Scan on unfiltered_revision_days (cost=0.00..0.02 rows=1 width=16) (actual time=19.289..19.385 rows=2 loops=1)

  • Filter: (distance = 1)
  • Rows Removed by Filter: 111
14. 55.540 42,109.907 ↓ 2,000.0 2,000 1

Nested Loop (cost=4,023.59..4,025.93 rows=1 width=36) (actual time=37.299..42,109.907 rows=2,000 loops=1)

  • Join Filter: (ft_security_data.from_to @> revision_days."timestamp")
  • Rows Removed by Join Filter: 32,000
15. 7.740 56.367 ↓ 2,000.0 2,000 1

Nested Loop (cost=0.43..0.58 rows=1 width=24) (actual time=19.827..56.367 rows=2,000 loops=1)

  • Join Filter: (revision_days.revision_date = its_universe_component_07."timestamp")
  • Rows Removed by Join Filter: 114,004
16. 19.389 19.389 ↓ 2.0 2 1

CTE Scan on revision_days (cost=0.00..0.02 rows=1 width=16) (actual time=19.290..19.389 rows=2 loops=1)

17. 8.310 29.238 ↓ 58,002.0 58,002 2

Append (cost=0.43..0.55 rows=1 width=16) (actual time=0.014..14.619 rows=58,002 loops=2)

18. 20.928 20.928 ↓ 58,002.0 58,002 2

Index Only Scan using its_universe_component_07_m_universe_id_timestamp_m_securit_idx on its_universe_component_07 (cost=0.43..0.55 rows=1 width=16) (actual time=0.013..10.464 rows=58,002 loops=2)

  • Index Cond: (m_universe_id = 2,161)
  • Heap Fetches: 116,004
19. 116.000 41,998.000 ↑ 1.0 17 2,000

Bitmap Heap Scan on ft_security_data (cost=4,023.17..4,025.14 rows=17 width=41) (actual time=20.991..20.999 rows=17 loops=2,000)

  • Recheck Cond: ((security_id = its_universe_component_07.m_security_id) AND (m_data_field_id = 153))
  • Heap Blocks: exact=2,394
20. 178.000 41,882.000 ↓ 0.0 0 2,000

BitmapAnd (cost=4,023.17..4,023.17 rows=17 width=0) (actual time=20.941..20.941 rows=0 loops=2,000)

21. 9,698.000 9,698.000 ↑ 1.1 54,741 2,000

Bitmap Index Scan on ft_security_data_idx_secid_covering (cost=0.00..493.95 rows=59,276 width=0) (actual time=4.849..4.849 rows=54,741 loops=2,000)

  • Index Cond: (security_id = its_universe_component_07.m_security_id)
22. 32,006.000 32,006.000 ↓ 1.0 419,209 2,000

Bitmap Index Scan on ft_security_data_idx_id_covering (cost=0.00..3,427.08 rows=407,521 width=0) (actual time=16.003..16.003 rows=419,209 loops=2,000)

  • Index Cond: (m_data_field_id = 153)
23. 8.000 8.000 ↑ 1.0 1 2,000

Index Scan using m_data_field_pkey on m_data_field (cost=0.15..0.36 rows=1 width=25) (actual time=0.004..0.004 rows=1 loops=2,000)

  • Index Cond: (id = 153)