explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rfBl

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 4,885.841 ↓ 0.0 0 1

WindowAgg (cost=10,507..5,613,287.3 rows=2 width=164) (actual time=4,885.841..4,885.841 rows=0 loops=1)

  • Buffers: shared hit=13,166
2.          

CTE constants

3. 0.007 0.007 ↑ 1.0 1 1

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

4. 0.043 4,885.839 ↓ 0.0 0 1

Subquery Scan on filtered (cost=10,506.98..5,613,287.25 rows=2 width=156) (actual time=4,885.839..4,885.839 rows=0 loops=1)

  • Filter: (filtered.sla_breach AND (filtered.client_id = 7))
  • Buffers: shared hit=13,166
5. 0.444 4,885.796 ↑ 2.4 285 1

Nested Loop (cost=10,506.98..5,613,278.64 rows=689 width=166) (actual time=4,843.157..4,885.796 rows=285 loops=1)

  • Buffers: shared hit=13,166
6. 0.356 4,853.960 ↑ 2.1 327 1

Nested Loop (cost=10,498.4..5,607,156.87 rows=689 width=234) (actual time=4,842.941..4,853.96 rows=327 loops=1)

  • Buffers: shared hit=4,689
7. 0.658 4,843.794 ↑ 2.1 327 1

Unique (cost=2,363.78..2,379.28 rows=689 width=132) (actual time=4,842.881..4,843.794 rows=327 loops=1)

  • Buffers: shared hit=1,226
8.          

Initplan (for Unique)

9. 0.010 0.010 ↑ 1.0 1 1

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

10. 4.005 4,843.126 ↓ 3.1 2,113 1

Sort (cost=2,363.76..2,365.48 rows=689 width=132) (actual time=4,842.88..4,843.126 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, (('-1'::integer * COALESCE(ticker_dependencies.min_offset, 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. 4,818.943 4,839.121 ↓ 3.1 2,113 1

Hash Join (cost=267.11..2,331.28 rows=689 width=132) (actual time=11.859..4,839.121 rows=2,113 loops=1)

  • Buffers: shared hit=1,226
12. 3.492 18.630 ↓ 1.1 3,827 1

Hash Join (cost=29.79..1,902.03 rows=3,558 width=39) (actual time=0.409..18.63 rows=3,827 loops=1)

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

Seq Scan on ticker_dependencies ticker_dependencies (cost=0..1,820.9 rows=19,504 width=25) (actual time=0.016..14.801 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.097 0.337 ↓ 1.1 297 1

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

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

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

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

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

  • Buffers: shared hit=99
17. 0.913 1.013 ↑ 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.121..1.013 rows=1,588 loops=1)

  • Filter: index_master.display_in_calculation_page
  • Heap Blocks: exact=88
  • Buffers: shared hit=99
18. 0.100 0.100 ↑ 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.1..0.1 rows=1,588 loops=1)

  • Index Cond: (index_master.display_in_calculation_page = true)
  • Buffers: shared hit=11
19. 0.654 9.810 ↑ 1.0 1 327

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

  • Buffers: shared hit=3,463
20. 0.654 9.156 ↑ 742.0 1 327

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

  • Group Key: prices.entered_by
  • Buffers: shared hit=3,463
21. 0.000 8.502 ↑ 742.0 1 327

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

  • Buffers: shared hit=3,463
22. 0.637 6.213 ↑ 1.0 1 327

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

  • Buffers: shared hit=1,944
23.          

Initplan (for Limit)

24. 0.017 0.017 ↑ 1.0 1 1

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

25. 5.559 5.559 ↑ 1.0 1 327

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=327)

  • 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,944
26. 0.098 2.289 ↓ 0.0 0 327

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

  • Buffers: shared hit=1,519
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.654 1.962 ↓ 0.0 0 327

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=327)

  • Heap Blocks: exact=107
  • Buffers: shared hit=1,091
30. 1.308 1.308 ↓ 0.0 0 327

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

  • 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=984
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 31.392 ↑ 1.0 1 327

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

  • Filter: (NOT h.holiday)
  • Buffers: shared hit=8,477
33. 29.448 31.392 ↑ 1.0 1 327

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

  • Buffers: shared hit=8,477
34.          

Initplan (for Result)

35. 1.944 1.944 ↓ 0.0 0 243

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=243)

  • 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: 9
  • Buffers: shared hit=985
Planning time : 3.036 ms
Execution time : 4,886.101 ms