explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kEKX

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 3,774.617 ↓ 0.0 0 1

WindowAgg (cost=10,505.28..5,613,285.58 rows=2 width=164) (actual time=3,774.616..3,774.617 rows=0 loops=1)

  • Buffers: shared hit=12,503
2.          

CTE constants

3. 0.010 0.010 ↑ 1.0 1 1

Result (cost=0..0.02 rows=1 width=64) (actual time=0.01..0.01 rows=1 loops=1)

4. 0.039 3,774.615 ↓ 0.0 0 1

Subquery Scan on filtered (cost=10,505.26..5,613,285.53 rows=2 width=156) (actual time=3,774.615..3,774.615 rows=0 loops=1)

  • Filter: (filtered.sla_breach AND (filtered.client_id = 7))
  • Buffers: shared hit=12,503
5. 0.434 3,774.576 ↑ 2.5 276 1

Nested Loop (cost=10,505.26..5,613,276.92 rows=689 width=166) (actual time=3,733.775..3,774.576 rows=276 loops=1)

  • Buffers: shared hit=12,503
6. 0.297 3,744.094 ↑ 2.2 313 1

Nested Loop (cost=10,496.67..5,607,155.15 rows=689 width=116) (actual time=3,733.559..3,744.094 rows=313 loops=1)

  • Buffers: shared hit=4,563
7. 0.649 3,734.407 ↑ 2.2 313 1

Unique (cost=2,362.06..2,377.56 rows=689 width=132) (actual time=3,733.497..3,734.407 rows=313 loops=1)

  • Buffers: shared hit=1,226
8.          

Initplan (for Unique)

9. 0.012 0.012 ↑ 1.0 1 1

CTE Scan on constants constants (cost=0..0.02 rows=1 width=32) (actual time=0.011..0.012 rows=1 loops=1)

10. 3.820 3,733.746 ↓ 3.1 2,113 1

Sort (cost=2,362.04..2,363.76 rows=689 width=132) (actual time=3,733.496..3,733.746 rows=2,113 loops=1)

  • Sort Key: ticker_sla.ticker, ticker_dependencies.allow_source_holidays, index_master.publication_pattern, (get_t_date_by_offset((to_timestamp($1, 'YYYYMMDD'::text))::timestamp without time zone, (0 + CASE WHEN (upper((index_master.publication_pattern)::text) ~~ '%T-1%'::text) THEN '-1'::integer ELSE 0 END), index_master.working_week)), ticker_sla.expected_arrival_time, ticker_sla.expected_arrival_offset, index_master.client_id, index_master.working_week
  • Sort Method: quicksort Memory: 394kB
  • Buffers: shared hit=1,226
11. 3,710.255 3,729.926 ↓ 3.1 2,113 1

Hash Join (cost=267.11..2,329.56 rows=689 width=132) (actual time=2.1..3,729.926 rows=2,113 loops=1)

  • Buffers: shared hit=1,226
12. 3.126 18.060 ↓ 1.1 3,827 1

Hash Join (cost=29.79..1,902.03 rows=3,558 width=35) (actual time=0.408..18.06 rows=3,827 loops=1)

  • Buffers: shared hit=1,127
13. 14.599 14.599 ↑ 1.3 15,122 1

Seq Scan on ticker_dependencies ticker_dependencies (cost=0..1,820.9 rows=19,504 width=21) (actual time=0.016..14.599 rows=15,122 loops=1)

  • Filter: ((NOT ticker_dependencies.configuration_only) AND ticker_dependencies.active AND ((ticker_dependencies.from_date)::text <= '20190101'::text))
  • Buffers: shared hit=1,115
14. 0.102 0.335 ↓ 1.1 297 1

Hash (cost=26.27..26.27 rows=282 width=24) (actual time=0.335..0.335 rows=297 loops=1)

  • Buffers: shared hit=12
15. 0.233 0.233 ↓ 1.1 297 1

Seq Scan on ticker_sla ticker_sla (cost=0..26.27 rows=282 width=24) (actual time=0.007..0.233 rows=297 loops=1)

  • Filter: ((ticker_sla.client_id = 1) AND (ticker_sla.pricetype = 0))
  • Buffers: shared hit=12
16. 0.545 1.611 ↑ 1.0 1,588 1

Hash (cost=217.47..217.47 rows=1,588 width=20) (actual time=1.611..1.611 rows=1,588 loops=1)

  • Buffers: shared hit=99
17. 0.963 1.066 ↑ 1.0 1,588 1

Bitmap Heap Scan on index_master index_master (cost=48.59..217.47 rows=1,588 width=20) (actual time=0.123..1.066 rows=1,588 loops=1)

  • Filter: index_master.display_in_calculation_page
  • Heap Blocks: exact=88
  • Buffers: shared hit=99
18. 0.103 0.103 ↑ 1.0 1,588 1

Bitmap Index Scan on ix_index_master_display_in_calculation_page (cost=0..48.19 rows=1,588 width=0) (actual time=0.103..0.103 rows=1,588 loops=1)

  • Index Cond: (index_master.display_in_calculation_page = true)
  • Buffers: shared hit=11
19. 0.626 9.390 ↑ 1.0 1 313

Limit (cost=8,134.62..8,134.63 rows=1 width=118) (actual time=0.029..0.03 rows=1 loops=313)

  • Buffers: shared hit=3,337
20. 0.626 8.764 ↑ 742.0 1 313

HashAggregate (cost=8,134.62..8,142.04 rows=742 width=118) (actual time=0.028..0.028 rows=1 loops=313)

  • Group Key: prices.entered_by
  • Buffers: shared hit=3,337
21. 0.313 8.138 ↑ 742.0 1 313

Append (cost=0.72..8,132.76 rows=742 width=118) (actual time=0.02..0.026 rows=1 loops=313)

  • Buffers: shared hit=3,337
22. 0.297 5.634 ↑ 1.0 1 313

Limit (cost=0.72..106.27 rows=1 width=10) (actual time=0.018..0.018 rows=1 loops=313)

  • Buffers: shared hit=1,860
23.          

Initplan (for Limit)

24. 0.016 0.016 ↑ 1.0 1 1

CTE Scan on constants constants_1 (cost=0..0.02 rows=1 width=32) (actual time=0.002..0.016 rows=1 loops=1)

25. 5.321 5.321 ↑ 1.0 1 313

Index Scan using ix_prices_active on prices prices (cost=0.7..106.25 rows=1 width=10) (actual time=0.017..0.017 rows=1 loops=313)

  • Index Cond: (((prices.ticker)::text = (ticker_sla.ticker)::text) AND ((prices.from_date)::text > $2) AND (prices.price_type = ticker_sla.pricetype) AND (prices.client_id = index_master.client_id))
  • Filter: ((prices.entered_by)::text !~~ '%@%'::text)
  • Buffers: shared hit=1,860
26. 0.084 2.191 ↓ 0.0 0 313

Nested Loop (cost=29.3..8,015.35 rows=741 width=6) (actual time=0.006..0.007 rows=0 loops=313)

  • Buffers: shared hit=1,477
27.          

Initplan (for Nested Loop)

28. 0.015 0.015 ↑ 1.0 1 1

CTE Scan on constants constants_2 (cost=0..0.02 rows=1 width=32) (actual time=0.001..0.015 rows=1 loops=1)

29. 0.313 1.878 ↓ 0.0 0 313

Bitmap Heap Scan on batch_completion_marker_label batch_completion_marker_label (cost=28.85..2,705.65 rows=822 width=4) (actual time=0.006..0.006 rows=0 loops=313)

  • Heap Blocks: exact=107
  • Buffers: shared hit=1,049
30. 1.565 1.565 ↓ 0.0 0 313

Bitmap Index Scan on ix_batch_completion_marker_label_label_name_from_date (cost=0..28.65 rows=822 width=0) (actual time=0.005..0.005 rows=0 loops=313)

  • Index Cond: (((batch_completion_marker_label.label_name)::text = (ticker_sla.ticker)::text) AND ((batch_completion_marker_label.from_date)::text > $6))
  • Buffers: shared hit=942
31. 0.214 0.214 ↑ 1.0 1 107

Index Scan using batch_completion_marker_pkey on batch_completion_marker batch_completion_marker (cost=0.42..6.46 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=107)

  • Index Cond: (batch_completion_marker.id = batch_completion_marker_label.batch_completion_marker_id)
  • Filter: batch_completion_marker.active
  • Buffers: shared hit=428
32. 0.000 30.048 ↑ 1.0 1 313

Subquery Scan on h (cost=8.58..8.86 rows=1 width=0) (actual time=0.096..0.096 rows=1 loops=313)

  • Filter: (NOT h.holiday)
  • Buffers: shared hit=7,940
33. 28.184 30.048 ↑ 1.0 1 313

Result (cost=8.58..8.85 rows=1 width=1) (actual time=0.096..0.096 rows=1 loops=313)

  • Buffers: shared hit=7,940
34.          

Initplan (for Result)

35. 1.864 1.864 ↓ 0.0 0 233

Index Only Scan using ix_i6_ticker_error_holidaylookup on i6_ticker_error i6_ticker_error (cost=0.56..8.58 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=233)

  • Index Cond: ((i6_ticker_error.ticker = ($7)::text) AND (i6_ticker_error.ticker_error_type_id = 9) AND (i6_ticker_error.from_date = ($8)::text))
  • Heap Fetches: 4
  • Buffers: shared hit=940
Planning time : 2.99 ms
Execution time : 3,774.875 ms