explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4WCd

Settings
# exclusive inclusive rows x rows loops node
1. 0.035 10,807.481 ↓ 0.0 0 1

Sort (cost=551,006.21..551,006.21 rows=1 width=563) (actual time=10,807.481..10,807.481 rows=0 loops=1)

  • Sort Key: filtered.expected_arrival_time_utc
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=75,008 read=2,574, local hit=1, temp read=562 written=563
2.          

CTE constants

3. 0.102 0.102 ↑ 1.0 1 1

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

4. 0.001 10,807.446 ↓ 0.0 0 1

WindowAgg (cost=14,077.45..551,006.18 rows=1 width=563) (actual time=10,807.446..10,807.446 rows=0 loops=1)

  • Buffers: shared hit=75,005 read=2,574, local hit=1, temp read=562 written=563
5. 0.234 10,807.445 ↓ 0.0 0 1

Subquery Scan on filtered (cost=14,077.45..551,006.16 rows=1 width=555) (actual time=10,807.445..10,807.445 rows=0 loops=1)

  • Filter: (filtered.sla_breach AND (filtered.client_id = 7))
  • Buffers: shared hit=75,005 read=2,574, local hit=1, temp read=562 written=563
6. 0.569 10,807.211 ↓ 10.0 459 1

Subquery Scan on a (cost=14,077.45..551,005.59 rows=46 width=565) (actual time=7,448.562..10,807.211 rows=459 loops=1)

  • Buffers: shared hit=75,005 read=2,574, local hit=1, temp read=562 written=563
7. 1,262.287 10,806.642 ↓ 10.0 459 1

Nested Loop (cost=14,077.45..551,004.9 rows=46 width=664) (actual time=7,448.559..10,806.642 rows=459 loops=1)

  • Buffers: shared hit=75,005 read=2,574, local hit=1, temp read=562 written=563
8. 0.591 8,642.939 ↓ 11.5 529 1

Nested Loop (cost=14,068.87..550,584.23 rows=46 width=653) (actual time=7,392.482..8,642.939 rows=529 loops=1)

  • Buffers: shared hit=63,798 read=1,920, local hit=1, temp read=562 written=563
9. 0.784 8,180.002 ↓ 11.5 529 1

Nested Loop (cost=2,172.05..3,329.34 rows=46 width=535) (actual time=7,388.302..8,180.002 rows=529 loops=1)

  • Buffers: shared hit=55,027 read=1,663, local hit=1, temp read=562 written=563
10. 1.226 8,127.129 ↓ 11.7 537 1

Nested Loop (cost=2,155.17..2,551.13 rows=46 width=535) (actual time=7,384.827..8,127.129 rows=537 loops=1)

  • Buffers: shared hit=53,420 read=1,619, local hit=1, temp read=562 written=563
11. 1.773 7,363.331 ↓ 13.3 613 1

Unique (cost=2,146.44..2,148.05 rows=46 width=535) (actual time=7,357.995..7,363.331 rows=613 loops=1)

  • Buffers: shared hit=50,829 read=1,058, local hit=1, temp read=562 written=563
12.          

Initplan (for Unique)

13. 0.002 0.002 ↑ 1.0 1 1

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

14. 0.105 0.105 ↑ 1.0 1 1

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

15. 0.002 0.002 ↑ 1.0 1 1

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

16. 19.666 7,361.449 ↓ 58.6 2,696 1

Sort (cost=2,146.38..2,146.5 rows=46 width=535) (actual time=7,357.994..7,361.449 rows=2,696 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.pricetype, ticker_sla.expected_arrival_time, ticker_sla.expected_arrival_offset, timezone_info.offsetinm_in_min, price_type_ids.price_type, index_master.client_id, index_master.working_week, clients.name, (string_agg((ticker_dependencies_1.index_ticker)::text, ', '::text))
  • Sort Method: external merge Disk: 4,496kB
  • Buffers: shared hit=50,829 read=1,058, local hit=1, temp read=562 written=563
17. 6,136.234 7,341.783 ↓ 58.6 2,696 1

Hash Join (cost=639.28..2,145.11 rows=46 width=535) (actual time=342.14..7,341.783 rows=2,696 loops=1)

  • Buffers: shared hit=50,818 read=1,058, local hit=1
18. 2.659 1,205.534 ↓ 93.0 2,696 1

Nested Loop (cost=622.08..2,114.03 rows=29 width=507) (actual time=334.154..1,205.534 rows=2,696 loops=1)

  • Buffers: shared hit=50,695 read=1,058
19. 5.260 361.723 ↓ 93.0 2,696 1

Nested Loop (cost=613.59..1,867.02 rows=29 width=475) (actual time=305.813..361.723 rows=2,696 loops=1)

  • Buffers: shared hit=13,658 read=617
20. 5.438 351.071 ↓ 93.0 2,696 1

Nested Loop (cost=613.45..1,861.34 rows=29 width=61) (actual time=304.032..351.071 rows=2,696 loops=1)

  • Buffers: shared hit=8,269 read=614
21. 3.054 334.849 ↓ 93.0 2,696 1

Hash Join (cost=613.18..1,852.42 rows=29 width=52) (actual time=304.011..334.849 rows=2,696 loops=1)

  • Buffers: shared hit=183 read=612
22. 4.530 302.591 ↓ 3.9 4,770 1

Hash Join (cost=575.05..1,804.63 rows=1,228 width=30) (actual time=274.786..302.591 rows=4,770 loops=1)

  • Buffers: shared hit=181 read=602
23. 24.861 237.421 ↓ 2.2 13,871 1

Bitmap Heap Scan on ticker_dependencies ticker_dependencies (cost=338.03..1,550.86 rows=6,374 width=29) (actual time=214.092..237.421 rows=13,871 loops=1)

  • Filter: ((NOT ticker_dependencies.configuration_only) AND ticker_dependencies.active AND ((ticker_dependencies.thru_date IS NULL) OR ((ticker_dependencies.thru_date)::text > $3)))
  • Heap Blocks: exact=648
  • Buffers: shared hit=178 read=506
24. 212.560 212.560 ↓ 2.3 15,349 1

Bitmap Index Scan on ix_ticker_dependencies_active_configuration_only_from_date_thru (cost=0..336.44 rows=6,722 width=0) (actual time=212.56..212.56 rows=15,349 loops=1)

  • Index Cond: ((ticker_dependencies.active = true) AND (ticker_dependencies.configuration_only = false) AND ((ticker_dependencies.from_date)::text <= $2))
  • Buffers: shared hit=1 read=145
25. 0.567 60.640 ↓ 1.0 1,579 1

Hash (cost=217.29..217.29 rows=1,578 width=20) (actual time=60.64..60.64 rows=1,579 loops=1)

  • Buffers: shared hit=3 read=96
26. 48.309 60.073 ↓ 1.0 1,579 1

Bitmap Heap Scan on index_master index_master (cost=48.51..217.29 rows=1,578 width=20) (actual time=13.405..60.073 rows=1,579 loops=1)

  • Filter: index_master.display_in_calculation_page
  • Heap Blocks: exact=88
  • Buffers: shared hit=3 read=96
27. 11.764 11.764 ↓ 1.0 1,582 1

Bitmap Index Scan on ix_index_master_display_in_calculation_page (cost=0..48.12 rows=1,578 width=0) (actual time=11.764..11.764 rows=1,582 loops=1)

  • Index Cond: (index_master.display_in_calculation_page = true)
  • Buffers: shared hit=1 read=10
28. 0.399 29.204 ↑ 1.0 950 1

Hash (cost=21.5..21.5 rows=950 width=32) (actual time=29.204..29.204 rows=950 loops=1)

  • Buffers: shared hit=2 read=10
29. 28.805 28.805 ↑ 1.0 950 1

Seq Scan on ticker_sla ticker_sla (cost=0..21.5 rows=950 width=32) (actual time=1.524..28.805 rows=950 loops=1)

  • Buffers: shared hit=2 read=10
30. 10.784 10.784 ↑ 1.0 1 2,696

Index Scan using price_type_ids_pkey on price_type_ids price_type_ids (cost=0.27..0.3 rows=1 width=17) (actual time=0.004..0.004 rows=1 loops=2,696)

  • Index Cond: (price_type_ids.id = ticker_dependencies.price_type)
  • Buffers: shared hit=8,086 read=2
31. 5.392 5.392 ↑ 1.0 1 2,696

Index Scan using clients_pkey on clients clients (cost=0.14..0.19 rows=1 width=422) (actual time=0.002..0.002 rows=1 loops=2,696)

  • Index Cond: (clients.id = index_master.client_id)
  • Buffers: shared hit=5,389 read=3
32. 43.132 841.152 ↑ 1.0 1 2,696

Aggregate (cost=8.49..8.5 rows=1 width=32) (actual time=0.312..0.312 rows=1 loops=2,696)

  • Buffers: shared hit=37,037 read=441
33.          

Initplan (for Aggregate)

34. 0.002 0.002 ↑ 1.0 1 1

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

35. 0.002 0.002 ↑ 1.0 1 1

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

36. 798.016 798.016 ↓ 141.0 141 2,696

Index Only Scan using ix_ticker_dependencies_3 on ticker_dependencies ticker_dependencies_1 (cost=0.41..8.44 rows=1 width=10) (actual time=0.08..0.296 rows=141 loops=2,696)

  • Index Cond: ((ticker_dependencies_1.ticker = (ticker_sla.ticker)::text) AND (ticker_dependencies_1.price_type = ticker_sla.pricetype) AND (ticker_dependencies_1.from_date <= $4) AND (ticker_dependencies_1.active = true))
  • Filter: (ticker_dependencies_1.active AND ((ticker_dependencies_1.thru_date IS NULL) OR ((ticker_dependencies_1.thru_date)::text > $5)))
  • Heap Fetches: 381,772
  • Buffers: shared hit=37,037 read=441
37. 0.004 0.015 ↑ 32.0 10 1

Hash (cost=13.2..13.2 rows=320 width=8) (actual time=0.015..0.015 rows=10 loops=1)

  • Buffers: local hit=1
38. 0.011 0.011 ↑ 32.0 10 1

Seq Scan on timezone_info timezone_info (cost=0..13.2 rows=320 width=8) (actual time=0.009..0.011 rows=10 loops=1)

  • Buffers: local hit=1
39. 1.839 762.572 ↑ 1.0 1 613

Result (cost=8.72..8.73 rows=1 width=1) (actual time=1.244..1.244 rows=1 loops=613)

  • Buffers: shared hit=2,591 read=561
40.          

Initplan (for Result)

41. 760.733 760.733 ↓ 0.0 0 613

Index Only Scan using ix_prices_active on prices prices_1 (cost=0.7..8.72 rows=1 width=0) (actual time=1.241..1.241 rows=0 loops=613)

  • Index Cond: ((prices_1.ticker = ($8)::text) AND (prices_1.from_date = ($9)::text) AND (prices_1.price_type = $10) AND (prices_1.client_id = $11))
  • Heap Fetches: 76
  • Buffers: shared hit=2,591 read=561
42. 0.537 52.089 ↑ 1.0 1 537

Result (cost=16.89..16.9 rows=1 width=1) (actual time=0.097..0.097 rows=1 loops=537)

  • Buffers: shared hit=1,607 read=44
43.          

Initplan (for Result)

44. 1.050 51.552 ↓ 0.0 0 537

Nested Loop (cost=0.85..16.89 rows=1 width=0) (actual time=0.096..0.096 rows=0 loops=537)

  • Buffers: shared hit=1,607 read=44
45. 50.478 50.478 ↓ 0.0 0 537

Index Only Scan using ix_batch_completion_marker_label_label_name_from_date on batch_completion_marker_label batch_completion_marker_label_1 (cost=0.43..8.45 rows=1 width=4) (actual time=0.094..0.094 rows=0 loops=537)

  • Index Cond: ((batch_completion_marker_label_1.label_name = ($13)::text) AND (batch_completion_marker_label_1.from_date = ($14)::text))
  • Heap Fetches: 8
  • Buffers: shared hit=1,575 read=44
46. 0.024 0.024 ↑ 1.0 1 8

Index Scan using batch_completion_marker_pkey on batch_completion_marker batch_completion_marker_1 (cost=0.42..8.44 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=8)

  • Index Cond: (batch_completion_marker_1.id = batch_completion_marker_label_1.batch_completion_marker_id)
  • Filter: batch_completion_marker_1.active
  • Buffers: shared hit=32
47. 3.703 462.346 ↑ 1.0 1 529

Limit (cost=11,896.82..11,896.83 rows=1 width=118) (actual time=0.871..0.874 rows=1 loops=529)

  • Buffers: shared hit=8,771 read=257
48. 2.645 458.643 ↑ 1,222.0 1 529

HashAggregate (cost=11,896.82..11,909.04 rows=1,222 width=118) (actual time=0.867..0.867 rows=1 loops=529)

  • Group Key: pp.external_provider
  • Buffers: shared hit=8,771 read=257
49. 0.529 455.998 ↑ 611.0 2 529

Append (cost=106..11,893.76 rows=1,222 width=118) (actual time=0.831..0.862 rows=2 loops=529)

  • Buffers: shared hit=8,771 read=257
50. 0.529 312.110 ↑ 1.0 1 529

Subquery Scan on pp (cost=106..106.01 rows=1 width=10) (actual time=0.589..0.59 rows=1 loops=529)

  • Buffers: shared hit=4,529 read=215
51. 0.484 311.581 ↑ 1.0 1 529

Limit (cost=106..106 rows=1 width=14) (actual time=0.589..0.589 rows=1 loops=529)

  • Buffers: shared hit=4,529 read=215
52.          

Initplan (for Limit)

53. 0.045 0.045 ↑ 1.0 1 1

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

54. 2.645 311.052 ↑ 1.0 1 529

Sort (cost=105.98..105.98 rows=1 width=14) (actual time=0.588..0.588 rows=1 loops=529)

  • Sort Key: prices.id DESC
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=4,529 read=215
55. 308.407 308.407 ↓ 4.0 4 529

Index Scan using ix_prices_active on prices prices (cost=0.7..105.97 rows=1 width=14) (actual time=0.232..0.583 rows=4 loops=529)

  • Index Cond: (((prices.ticker)::text = (ticker_sla.ticker)::text) AND ((prices.from_date)::text > $17) AND (prices.price_type = ticker_sla.pricetype) AND (prices.client_id = index_master.client_id))
  • Filter: ((prices.entered_by)::text !~~ '%@%'::text)
  • Buffers: shared hit=4,529 read=215
56. 1.507 143.359 ↑ 1,221.0 1 529

Nested Loop (cost=50.66..11,769.42 rows=1,221 width=6) (actual time=0.249..0.271 rows=1 loops=529)

  • Buffers: shared hit=4,242 read=42
57.          

Initplan (for Nested Loop)

58. 0.038 0.038 ↑ 1.0 1 1

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

59. 124.315 127.489 ↑ 1,345.0 1 529

Bitmap Heap Scan on batch_completion_marker_label batch_completion_marker_label (cost=50.21..4,121.6 rows=1,345 width=4) (actual time=0.24..0.241 rows=1 loops=529)

  • Heap Blocks: exact=349
  • Buffers: shared hit=1,907 read=29
60. 3.174 3.174 ↑ 1,345.0 1 529

Bitmap Index Scan on ix_batch_completion_marker_label_label_name_from_date (cost=0..49.88 rows=1,345 width=0) (actual time=0.005..0.006 rows=1 loops=529)

  • Index Cond: (((batch_completion_marker_label.label_name)::text = (ticker_sla.ticker)::text) AND ((batch_completion_marker_label.from_date)::text > $21))
  • Buffers: shared hit=1,587
61. 14.325 14.325 ↑ 1.0 1 573

Index Scan using batch_completion_marker_pkey on batch_completion_marker batch_completion_marker (cost=0.42..5.69 rows=1 width=10) (actual time=0.025..0.025 rows=1 loops=573)

  • Index Cond: (batch_completion_marker.id = batch_completion_marker_label.batch_completion_marker_id)
  • Filter: batch_completion_marker.active
  • Buffers: shared hit=2,335 read=13
62. 0.529 901.416 ↑ 1.0 1 529

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

  • Filter: (NOT h.holiday)
  • Buffers: shared hit=11,207 read=654
63. 618.819 900.887 ↑ 1.0 1 529

Result (cost=8.58..8.85 rows=1 width=1) (actual time=1.703..1.703 rows=1 loops=529)

  • Buffers: shared hit=11,207 read=654
64.          

Initplan (for Result)

65. 282.068 282.068 ↓ 0.0 0 302

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.934..0.934 rows=0 loops=302)

  • Index Cond: ((i6_ticker_error.ticker = ($22)::text) AND (i6_ticker_error.ticker_error_type_id = 9) AND (i6_ticker_error.from_date = ($23)::text))
  • Buffers: shared hit=934 read=275
Planning time : 125.814 ms
Execution time : 10,811.113 ms