explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 07xS : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #5bNE; plan #4xHo; plan #PNcB; plan #S7dv; plan #Jdb; plan #kF4O; plan #STh7; plan #2Hnm

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.030 176,264.785 ↑ 1.0 1 1

Limit (cost=3,876.90..3,876.90 rows=1 width=850) (actual time=176,264.756..176,264.785 rows=1 loops=1)

2. 95.053 176,264.755 ↑ 1.0 1 1

Sort (cost=3,876.90..3,876.90 rows=1 width=850) (actual time=176,264.755..176,264.755 rows=1 loops=1)

  • Sort Key: t01.year, t01.serial, t08.year, t08.serial
  • Sort Method: top-N heapsort Memory: 26kB
3. 56.620 176,169.702 ↓ 18,804.0 18,804 1

Nested Loop Left Join (cost=1,987.50..3,876.89 rows=1 width=850) (actual time=196.716..176,169.702 rows=18,804 loops=1)

  • Join Filter: (((t06.aggregator_key)::text = (t01.aggregator_key)::text) AND ((t06.generator_key)::text = (t01.generator_key)::text) AND ((t06.pricing_key)::text = (t05.article_key)::text))
4. 4,532.965 141,400.898 ↓ 18,804.0 18,804 1

Nested Loop Left Join (cost=1,987.09..3,866.97 rows=1 width=949) (actual time=194.307..141,400.898 rows=18,804 loops=1)

  • Join Filter: (((t17.aggregator_key)::text = (t01.aggregator_key)::text) AND ((t17.generator_key)::text = (t07.generator_key)::text) AND ((t17.pricing_key)::text = (t16.article_key)::text))
  • Rows Removed by Join Filter: 53598962
5. 500.426 97,586.377 ↓ 18,804.0 18,804 1

Nested Loop Left Join (cost=1,986.67..3,857.01 rows=1 width=1,015) (actual time=192.527..97,586.377 rows=18,804 loops=1)

  • Join Filter: (((t15.aggregator_key)::text = (t01.aggregator_key)::text) AND ((t15.generator_key)::text = (t07.generator_key)::text) AND ((t15.pricing_key)::text = (t14.article_key)::text))
  • Rows Removed by Join Filter: 4778627
6. 85.630 60,700.211 ↓ 18,804.0 18,804 1

Nested Loop Left Join (cost=1,986.26..3,847.09 rows=1 width=1,048) (actual time=189.169..60,700.211 rows=18,804 loops=1)

  • Join Filter: ((rtl.work_order_id = t01.id) AND (rtl.sample_id = t08.id) AND (rtl.sample_parameter_id = t09.id))
  • Rows Removed by Join Filter: 37608
7. 212.865 60,595.777 ↓ 18,804.0 18,804 1

Nested Loop (cost=1,986.26..3,846.04 rows=1 width=1,060) (actual time=189.160..60,595.777 rows=18,804 loops=1)

  • Join Filter: (t09.functional_structure_id = t11.id)
  • Rows Removed by Join Filter: 721799
8. 747.959 60,288.892 ↓ 18,804.0 18,804 1

Nested Loop (cost=1,986.26..3,842.26 rows=1 width=1,068) (actual time=189.140..60,288.892 rows=18,804 loops=1)

  • Join Filter: (t09.functional_structure_id = t12.functional_structure_id)
  • Rows Removed by Join Filter: 1466712
9. 0.292 0.292 ↑ 1.0 79 1

Index Only Scan using pk_functional_structures_departments on functional_structures_departments t12 (cost=0.14..13.33 rows=79 width=4) (actual time=0.018..0.292 rows=79 loops=1)

  • Heap Fetches: 79
10. 155.452 59,540.641 ↓ 18,804.0 18,804 79

Materialize (cost=1,986.12..3,827.75 rows=1 width=1,064) (actual time=1.326..753.679 rows=18,804 loops=79)

11. 33.007 59,385.189 ↓ 18,804.0 18,804 1

Nested Loop Left Join (cost=1,986.12..3,827.74 rows=1 width=1,064) (actual time=104.757..59,385.189 rows=18,804 loops=1)

12. 28.778 834.134 ↓ 18,804.0 18,804 1

Nested Loop Left Join (cost=1,986.12..3,530.10 rows=1 width=1,098) (actual time=102.522..834.134 rows=18,804 loops=1)

13. 32.819 767.748 ↓ 18,804.0 18,804 1

Nested Loop (cost=1,985.84..3,529.81 rows=1 width=1,069) (actual time=102.511..767.748 rows=18,804 loops=1)

  • Join Filter: (t09.sample_id = t08.id)
  • Rows Removed by Join Filter: 43937
14. 89.635 546.706 ↓ 7,842.6 62,741 1

Nested Loop (cost=1,985.56..3,527.10 rows=8 width=1,069) (actual time=102.493..546.706 rows=62,741 loops=1)

15. 124.457 268.848 ↓ 309.1 62,741 1

Hash Join (cost=1,985.28..3,464.37 rows=203 width=1,010) (actual time=102.470..268.848 rows=62,741 loops=1)

  • Hash Cond: ((t09.work_order_id = t01.id) AND (t09.parameter_id = t13.analytical_parameter_id))
16. 41.998 41.998 ↑ 1.0 35,333 1

Seq Scan on sample_parameters t09 (cost=0.00..1,206.64 rows=36,057 width=20) (actual time=0.012..41.998 rows=35,333 loops=1)

  • Filter: (sample_type_id = 1)
  • Rows Removed by Filter: 1311
17. 24.999 102.393 ↓ 5.0 20,506 1

Hash (cost=1,923.54..1,923.54 rows=4,116 width=998) (actual time=102.393..102.393 rows=20,506 loops=1)

  • Buckets: 32768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 9347kB
18. 16.565 77.394 ↓ 5.0 20,506 1

Nested Loop (cost=838.78..1,923.54 rows=4,116 width=998) (actual time=13.243..77.394 rows=20,506 loops=1)

19. 3.262 43.690 ↓ 5.4 5,713 1

Hash Join (cost=838.50..1,256.31 rows=1,066 width=986) (actual time=13.222..43.690 rows=5,713 loops=1)

  • Hash Cond: (t01.store_id = t02.id)
20. 2.449 39.139 ↓ 5.4 5,713 1

Hash Left Join (cost=614.26..1,029.28 rows=1,066 width=990) (actual time=11.896..39.139 rows=5,713 loops=1)

  • Hash Cond: (t01.id = t04.lab_serviceable_id)
21. 15.736 36.571 ↓ 5.4 5,713 1

Hash Join (cost=601.31..1,010.98 rows=1,066 width=957) (actual time=11.757..36.571 rows=5,713 loops=1)

  • Hash Cond: (t01.customer_id = entities.id)
  • Join Filter: (((NOT entities.liability_notice) OR (entities.liability_notice AND t01.liability_notice)) AND ((NOT entities.monthly_billing) OR (entities.monthly_billing AND ((date_part('day'::text, now()) >= '15'::double precision) OR ((date_trunc('day'::text, t01.received_at))::date <= ((date_trunc('month'::text, now()))::date - 1))))) AND (((entities.bill_when)::text = 'record'::text) OR (((entities.bill_when)::text = 'report_issued'::text) AND (alternatives: SubPlan 1 or hashed SubPlan 2)) OR (((entities.bill_when)::text = 'determined_results'::text) AND (SubPlan 3))))
  • Rows Removed by Join Filter: 4115
22. 8.821 16.716 ↑ 1.0 11,181 1

Hash Join (cost=385.34..765.64 rows=11,190 width=136) (actual time=6.563..16.716 rows=11,181 loops=1)

  • Hash Cond: (t07.work_order_id = t01.id)
23. 1.400 1.400 ↑ 1.0 11,181 1

Seq Scan on sample_registrations t07 (cost=0.00..350.90 rows=11,190 width=41) (actual time=0.011..1.400 rows=11,181 loops=1)

24. 2.066 6.495 ↑ 1.0 6,104 1

Hash (cost=309.04..309.04 rows=6,104 width=99) (actual time=6.494..6.495 rows=6,104 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 875kB
25. 4.429 4.429 ↑ 1.0 6,104 1

Seq Scan on work_orders t01 (cost=0.00..309.04 rows=6,104 width=99) (actual time=0.011..4.429 rows=6,104 loops=1)

  • Filter: (billed_at IS NULL)
26. 0.841 2.776 ↓ 2.4 2,209 1

Hash (cost=204.41..204.41 rows=925 width=834) (actual time=2.776..2.776 rows=2,209 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 685kB
27. 1.935 1.935 ↓ 2.4 2,209 1

Seq Scan on entities (cost=0.00..204.41 rows=925 width=834) (actual time=0.011..1.935 rows=2,209 loops=1)

  • Filter: (billable AND (NOT manual_billing) AND ((NOT monthly_billing) OR monthly_billing) AND (((bill_when)::text = 'record'::text) OR ((bill_when)::text = 'report_issued'::text) OR ((bill_when)::text = 'determined_results'::text)))
  • Rows Removed by Filter: 1757
28.          

SubPlan (for Hash Join)

29. 0.000 0.000 ↓ 0.0 0

Index Only Scan using index_analytical_reports_on_work_order_id_and_preview on analytical_reports (cost=0.29..8.30 rows=1 width=0) (never executed)

  • Index Cond: ((work_order_id = t01.id) AND (preview = false))
  • Filter: (NOT preview)
  • Heap Fetches: 0
30. 1.084 1.343 ↓ 1.0 4,238 1

Bitmap Heap Scan on analytical_reports analytical_reports_1 (cost=78.33..456.69 rows=4,236 width=4) (actual time=0.299..1.343 rows=4,238 loops=1)

  • Recheck Cond: (NOT preview)
  • Heap Blocks: exact=309
31. 0.259 0.259 ↓ 1.0 4,241 1

Bitmap Index Scan on index_analytical_reports_on_preview (cost=0.00..77.27 rows=4,236 width=0) (actual time=0.259..0.259 rows=4,241 loops=1)

32. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.87..30.16 rows=3 width=0) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.57..27.90 rows=4 width=4) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_samples_sample_registration_id on samples hdr_samples (cost=0.29..8.30 rows=1 width=4) (never executed)

  • Index Cond: (registration_id = t07.id)
35. 0.000 0.000 ↓ 0.0 0

Index Scan using index_sample_parameters_on_sample_id_and_position on sample_parameters hdr_sample_parameters (cost=0.29..19.56 rows=4 width=8) (never executed)

  • Index Cond: (sample_id = hdr_samples.id)
36. 0.000 0.000 ↓ 0.0 0

Index Scan using index_analytical_results_sample_parameter_id on analytical_results (cost=0.29..0.56 rows=1 width=4) (never executed)

  • Index Cond: (sample_parameter_id = hdr_sample_parameters.id)
  • Filter: (value_id IS NOT NULL)
37. 0.008 0.119 ↑ 1.0 9 1

Hash (cost=12.84..12.84 rows=9 width=37) (actual time=0.119..0.119 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
38. 0.053 0.111 ↑ 1.0 9 1

Hash Right Join (cost=1.25..12.84 rows=9 width=37) (actual time=0.045..0.111 rows=9 loops=1)

  • Hash Cond: (t05.id = t04.lab_service_id)
39. 0.035 0.035 ↑ 1.0 240 1

Seq Scan on lab_services t05 (cost=0.00..9.40 rows=240 width=37) (actual time=0.007..0.035 rows=240 loops=1)

40. 0.008 0.023 ↑ 1.0 9 1

Hash (cost=1.14..1.14 rows=9 width=8) (actual time=0.023..0.023 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 0.015 0.015 ↑ 1.0 9 1

Seq Scan on lab_service_attributions t04 (cost=0.00..1.14 rows=9 width=8) (actual time=0.012..0.015 rows=9 loops=1)

  • Filter: ((lab_serviceable_type)::text = 'WorkOrder'::text)
  • Rows Removed by Filter: 2
42. 0.600 1.289 ↑ 1.0 3,966 1

Hash (cost=174.66..174.66 rows=3,966 width=4) (actual time=1.289..1.289 rows=3,966 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 172kB
43. 0.689 0.689 ↑ 1.0 3,966 1

Seq Scan on entities t02 (cost=0.00..174.66 rows=3,966 width=4) (actual time=0.010..0.689 rows=3,966 loops=1)

44. 17.139 17.139 ↑ 1.0 4 5,713

Index Scan using index_parameter_assignments_on_registration_id on sample_parameter_assignments t13 (cost=0.29..0.59 rows=4 width=12) (actual time=0.002..0.003 rows=4 loops=5,713)

  • Index Cond: (sample_registration_id = t07.id)
45. 188.223 188.223 ↑ 1.0 1 62,741

Index Scan using analytical_parameters_pkey on analytical_parameters t16 (cost=0.28..0.31 rows=1 width=71) (actual time=0.003..0.003 rows=1 loops=62,741)

  • Index Cond: (id = t13.analytical_parameter_id)
46. 188.223 188.223 ↑ 1.0 1 62,741

Index Scan using idx_samples_sample_registration_id on samples t08 (cost=0.29..0.33 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=62,741)

  • Index Cond: (registration_id = t13.sample_registration_id)
47. 37.608 37.608 ↓ 0.0 0 18,804

Index Scan using analytical_parameter_lists_pkey on analytical_parameter_lists t14 (cost=0.27..0.29 rows=1 width=37) (actual time=0.002..0.002 rows=0 loops=18,804)

  • Index Cond: (id = t13.analytical_parameter_list_id)
48. 169.236 58,518.048 ↑ 2.0 1 18,804

Append (cost=0.00..297.62 rows=2 width=35) (actual time=1.544..3.112 rows=1 loops=18,804)

49. 0.000 0.000 ↓ 0.0 0 18,804

Seq Scan on user_translations t18 (cost=0.00..0.00 rows=1 width=516) (actual time=0.000..0.000 rows=0 loops=18,804)

  • Filter: (((locale_code)::text = 'pt-PT'::text) AND ((key)::text = (t16.name_tkey)::text))
50. 58,348.812 58,348.812 ↑ 1.0 1 18,804

Index Only Scan using user_translations_pt_pt_pk on "user_translations_pt-PT" t18_1 (cost=0.41..297.61 rows=1 width=35) (actual time=1.536..3.103 rows=1 loops=18,804)

  • Index Cond: ((locale_code = 'pt-PT'::text) AND (key = (t16.name_tkey)::text))
  • Heap Fetches: 18804
51. 94.020 94.020 ↑ 2.0 39 18,804

Seq Scan on functional_structures t11 (cost=0.00..2.79 rows=79 width=4) (actual time=0.003..0.005 rows=39 loops=18,804)

52. 18.804 18.804 ↑ 1.0 2 18,804

Seq Scan on responsability_term_lines rtl (cost=0.00..1.02 rows=2 width=16) (actual time=0.001..0.001 rows=2 loops=18,804)

53. 36,385.740 36,385.740 ↓ 254.0 254 18,804

Index Scan using idx_valorization_articles_customer_aggregator_type on valorization_articles t15 (cost=0.41..9.90 rows=1 width=132) (actual time=0.038..1.935 rows=254 loops=18,804)

  • Index Cond: (((customer_key)::text = (entities.customer_key)::text) AND ((aggregator_type)::text = 'WorkOrder'::text))
  • Filter: (((customer_type)::text = 'Entity'::text) AND ((generator_type)::text = 'SampleRegistration'::text) AND ((pricing_type)::text = 'AnalyticalParameterList'::text) AND ((state)::text = 'available'::text))
  • Rows Removed by Filter: 2870
54. 39,281.556 39,281.556 ↓ 712.8 2,851 18,804

Index Scan using idx_valorization_articles_customer_aggregator_type on valorization_articles t17 (cost=0.41..9.90 rows=4 width=132) (actual time=0.022..2.089 rows=2,851 loops=18,804)

  • Index Cond: (((customer_key)::text = (entities.customer_key)::text) AND ((aggregator_type)::text = 'WorkOrder'::text))
  • Filter: (((customer_type)::text = 'Entity'::text) AND ((generator_type)::text = 'SampleRegistration'::text) AND ((pricing_type)::text = 'AnalyticalParameter'::text) AND ((state)::text = 'available'::text))
  • Rows Removed by Filter: 274
55. 34,712.184 34,712.184 ↓ 0.0 0 18,804

Index Scan using idx_valorization_articles_customer_aggregator_type on valorization_articles t06 (cost=0.41..9.90 rows=1 width=132) (actual time=1.846..1.846 rows=0 loops=18,804)

  • Index Cond: (((customer_key)::text = (entities.customer_key)::text) AND ((aggregator_type)::text = 'WorkOrder'::text))
  • Filter: (((customer_type)::text = 'Entity'::text) AND ((generator_type)::text = 'SampleRegistration'::text) AND ((pricing_type)::text = 'LabService'::text) AND ((state)::text = 'available'::text))
  • Rows Removed by Filter: 3124
Planning time : 88.672 ms
Execution time : 176,270.383 ms