explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gDiV

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 4,814.924 ↓ 0.0 0 1

WindowAgg (cost=10,507.02..5,613,297.63 rows=2 width=164) (actual time=4,814.924..4,814.924 rows=0 loops=1)

  • Buffers: shared hit=14,471
2.          

CTE constants

3. 0.009 0.009 ↑ 1.0 1 1

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

4. 0.041 4,814.923 ↓ 0.0 0 1

Subquery Scan on filtered (cost=10,507..5,613,297.59 rows=2 width=156) (actual time=4,814.922..4,814.923 rows=0 loops=1)

  • Filter: (filtered.sla_breach AND (filtered.client_id = 7))
  • Buffers: shared hit=14,471
5. 0.247 4,814.882 ↑ 2.4 285 1

Nested Loop (cost=10,507..5,613,288.97 rows=689 width=166) (actual time=4,769.65..4,814.882 rows=285 loops=1)

  • Buffers: shared hit=14,471
6. 0.180 4,782.589 ↑ 2.1 327 1

Nested Loop (cost=10,498.41..5,607,167.21 rows=689 width=234) (actual time=4,769.432..4,782.589 rows=327 loops=1)

  • Buffers: shared hit=5,994
7. 0.670 4,770.310 ↑ 2.1 327 1

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

  • Buffers: shared hit=1,226
8.          

Initplan (for Unique)

9. 0.011 0.011 ↑ 1.0 1 1

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

10. 4.091 4,769.629 ↓ 3.1 2,113 1

Sort (cost=2,363.76..2,365.48 rows=689 width=132) (actual time=4,769.353..4,769.629 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,746.337 4,765.538 ↓ 3.1 2,113 1

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

  • Buffers: shared hit=1,226
12. 3.395 18.234 ↓ 1.1 3,827 1

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

  • Buffers: shared hit=1,127
13. 14.653 14.653 ↑ 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.012..14.653 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.059 0.186 ↓ 1.1 297 1

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

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

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

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

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

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

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

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

Limit (cost=8,134.63..8,134.64 rows=1 width=118) (actual time=0.035..0.037 rows=1 loops=327)

  • Buffers: shared hit=4,768
20. 0.654 11.118 ↑ 742.0 1 327

HashAggregate (cost=8,134.63..8,142.05 rows=742 width=118) (actual time=0.034..0.034 rows=1 loops=327)

  • Group Key: pp.external_provider
  • Buffers: shared hit=4,768
21. 0.000 10.464 ↑ 742.0 1 327

Append (cost=106.28..8,132.78 rows=742 width=118) (actual time=0.026..0.032 rows=1 loops=327)

  • Buffers: shared hit=4,768
22. 0.327 8.175 ↑ 1.0 1 327

Subquery Scan on pp (cost=106.28..106.3 rows=1 width=10) (actual time=0.024..0.025 rows=1 loops=327)

  • Buffers: shared hit=3,249
23. 0.000 7.848 ↑ 1.0 1 327

Limit (cost=106.28..106.28 rows=1 width=14) (actual time=0.024..0.024 rows=1 loops=327)

  • Buffers: shared hit=3,249
24.          

Initplan (for Limit)

25. 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.002..0.017 rows=1 loops=1)

26. 0.981 7.848 ↑ 1.0 1 327

Sort (cost=106.26..106.27 rows=1 width=14) (actual time=0.024..0.024 rows=1 loops=327)

  • Sort Key: prices.id DESC
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=3,249
27. 6.867 6.867 ↓ 5.0 5 327

Index Scan using ix_prices_active on prices prices (cost=0.7..106.25 rows=1 width=14) (actual time=0.018..0.021 rows=5 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=3,249
28. 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
29.          

Initplan (for Nested Loop)

30. 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)

31. 0.327 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
32. 1.635 1.635 ↓ 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.005..0.005 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
33. 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
34. 0.327 32.046 ↑ 1.0 1 327

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

  • Filter: (NOT h.holiday)
  • Buffers: shared hit=8,477
35. 29.775 31.719 ↑ 1.0 1 327

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

  • Buffers: shared hit=8,477
36.          

Initplan (for Result)

37. 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 : 1.952 ms
Execution time : 4,815.143 ms