explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aar2 : Optimization for: 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; plan #07xS

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.929 4,653.080 ↓ 100.0 100 1

Sort (cost=6,800.94..6,800.94 rows=1 width=50,612) (actual time=4,653.076..4,653.080 rows=100 loops=1)

  • Sort Key: q."t01_Year", q."t01_Serial", q."t08_Year", q."t08_Serial
  • Sort Method: quicksort Memory: 294kB
2.          

CTE q

3. 0.101 4,649.260 ↓ 100.0 100 1

Limit (cost=2,248.96..6,800.91 rows=1 width=10,114) (actual time=138.184..4,649.260 rows=100 loops=1)

4. 1.001 4,649.159 ↓ 100.0 100 1

Nested Loop Left Join (cost=2,248.96..6,800.91 rows=1 width=10,114) (actual time=138.183..4,649.159 rows=100 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))
  • Filter: ((t15.id IS NOT NULL) OR (t17.id IS NOT NULL) OR (t06.id IS NOT NULL))
  • Rows Removed by Filter: 2
5. 20.470 4,461.090 ↓ 102.0 102 1

Nested Loop Left Join (cost=2,248.54..6,790.99 rows=1 width=8,734) (actual time=137.908..4,461.090 rows=102 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: 228364
6. 5.107 4,240.394 ↓ 102.0 102 1

Nested Loop Left Join (cost=2,248.13..6,781.02 rows=1 width=7,354) (actual time=137.864..4,240.394 rows=102 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: 44989
7. 0.852 3,994.465 ↓ 102.0 102 1

Nested Loop Left Join (cost=2,247.71..6,771.11 rows=1 width=5,974) (actual time=137.261..3,994.465 rows=102 loops=1)

  • Join Filter: (t10.id = rtl.responsability_term_id)
  • Rows Removed by Join Filter: 204
8. 1.045 3,993.409 ↓ 102.0 102 1

Nested Loop Left Join (cost=2,247.71..6,770.06 rows=1 width=5,969) (actual time=137.237..3,993.409 rows=102 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: 204
9. 1.796 3,992.160 ↓ 102.0 102 1

Nested Loop (cost=2,247.71..6,769.01 rows=1 width=5,965) (actual time=137.215..3,992.160 rows=102 loops=1)

  • Join Filter: (t09.functional_structure_id = t11.id)
  • Rows Removed by Join Filter: 5916
10. 1.114 3,989.650 ↓ 102.0 102 1

Nested Loop (cost=2,247.71..6,765.23 rows=1 width=5,961) (actual time=137.175..3,989.650 rows=102 loops=1)

  • Join Filter: (t09.functional_structure_id = t12.functional_structure_id)
  • Rows Removed by Join Filter: 1164
11. 0.021 0.021 ↑ 79.0 1 1

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

  • Heap Fetches: 1
12. 16.850 3,988.515 ↓ 1,266.0 1,266 1

Materialize (cost=2,247.57..6,750.72 rows=1 width=5,953) (actual time=37.125..3,988.515 rows=1,266 loops=1)

13. 4.904 3,971.665 ↓ 1,266.0 1,266 1

Nested Loop Left Join (cost=2,247.57..6,750.72 rows=1 width=5,953) (actual time=37.104..3,971.665 rows=1,266 loops=1)

14. 5.015 166.229 ↓ 1,266.0 1,266 1

Nested Loop (cost=2,247.57..6,453.07 rows=1 width=5,883) (actual time=34.537..166.229 rows=1,266 loops=1)

15. 41.940 154.884 ↓ 1,266.0 1,266 1

Nested Loop Left Join (cost=2,247.29..6,452.76 rows=1 width=5,509) (actual time=34.520..154.884 rows=1,266 loops=1)

  • Join Filter: (t14.id = t13.analytical_parameter_list_id)
  • Rows Removed by Join Filter: 356145
16. 6.154 74.964 ↓ 1,266.0 1,266 1

Nested Loop (cost=2,247.29..6,433.35 rows=1 width=5,284) (actual time=34.422..74.964 rows=1,266 loops=1)

  • Join Filter: (t01.id = t09.work_order_id)
17. 4.691 61.172 ↑ 2.8 1,273 1

Hash Join (cost=2,247.00..4,043.91 rows=3,531 width=4,632) (actual time=34.403..61.172 rows=1,273 loops=1)

  • Hash Cond: (t13.sample_registration_id = t08.registration_id)
18. 3.988 51.076 ↑ 3.2 1,275 1

Nested Loop (cost=1,803.02..3,540.33 rows=4,116 width=4,480) (actual time=28.881..51.076 rows=1,275 loops=1)

19. 1.337 42.183 ↑ 3.3 327 1

Hash Left Join (cost=1,802.73..2,873.11 rows=1,066 width=3,337) (actual time=28.860..42.183 rows=327 loops=1)

  • Hash Cond: (t01.patient_id = t03.id)
20. 1.907 38.672 ↑ 3.3 327 1

Hash Join (cost=1,556.87..2,624.44 rows=1,066 width=3,305) (actual time=26.631..38.672 rows=327 loops=1)

  • Hash Cond: (t01.store_id = t02.id)
21. 1.254 34.955 ↑ 3.3 327 1

Hash Left Join (cost=1,332.63..2,397.41 rows=1,066 width=3,265) (actual time=24.789..34.955 rows=327 loops=1)

  • Hash Cond: (t01.id = t04.lab_serviceable_id)
22. 6.565 33.578 ↑ 3.3 327 1

Hash Join (cost=1,319.68..2,379.10 rows=1,066 width=2,950) (actual time=24.645..33.578 rows=327 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: 185
23. 2.450 20.952 ↑ 20.2 553 1

Merge Join (cost=1,103.71..2,133.76 rows=11,190 width=2,148) (actual time=16.970..20.952 rows=553 loops=1)

  • Merge Cond: (t01.id = t07.work_order_id)
24. 1.139 1.139 ↑ 20.3 301 1

Index Scan using work_orders_pkey on work_orders t01 (cost=0.28..850.06 rows=6,104 width=1,635) (actual time=0.022..1.139 rows=301 loops=1)

  • Filter: (billed_at IS NULL)
25. 10.627 17.363 ↑ 20.2 553 1

Sort (cost=1,103.42..1,131.40 rows=11,190 width=513) (actual time=16.936..17.363 rows=553 loops=1)

  • Sort Key: t07.work_order_id
  • Sort Method: quicksort Memory: 3575kB
26. 6.736 6.736 ↑ 1.0 11,181 1

Seq Scan on sample_registrations t07 (cost=0.00..350.90 rows=11,190 width=513) (actual time=0.013..6.736 rows=11,181 loops=1)

27. 1.727 4.267 ↓ 2.4 2,209 1

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

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

Seq Scan on entities (cost=0.00..204.41 rows=925 width=802) (actual time=0.013..2.540 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
29.          

SubPlan (for Hash Join)

30. 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
31. 1.585 1.794 ↓ 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.249..1.794 rows=4,238 loops=1)

  • Recheck Cond: (NOT preview)
  • Heap Blocks: exact=309
32. 0.209 0.209 ↓ 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.209..0.209 rows=4,241 loops=1)

33. 0.000 0.000 ↓ 0.0 0

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

34. 0.000 0.000 ↓ 0.0 0

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

35. 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)
36. 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)
37. 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)
38. 0.013 0.123 ↑ 1.0 9 1

Hash (cost=12.84..12.84 rows=9 width=315) (actual time=0.123..0.123 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
39. 0.043 0.110 ↑ 1.0 9 1

Hash Right Join (cost=1.25..12.84 rows=9 width=315) (actual time=0.051..0.110 rows=9 loops=1)

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

Seq Scan on lab_services t05 (cost=0.00..9.40 rows=240 width=265) (actual time=0.008..0.033 rows=240 loops=1)

41. 0.009 0.034 ↑ 1.0 9 1

Hash (cost=1.14..1.14 rows=9 width=50) (actual time=0.034..0.034 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
42. 0.025 0.025 ↑ 1.0 9 1

Seq Scan on lab_service_attributions t04 (cost=0.00..1.14 rows=9 width=50) (actual time=0.020..0.025 rows=9 loops=1)

  • Filter: ((lab_serviceable_type)::text = 'WorkOrder'::text)
  • Rows Removed by Filter: 2
43. 0.876 1.810 ↑ 1.0 3,966 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 313kB
44. 0.934 0.934 ↑ 1.0 3,966 1

Seq Scan on entities t02 (cost=0.00..174.66 rows=3,966 width=40) (actual time=0.013..0.934 rows=3,966 loops=1)

45. 1.030 2.174 ↑ 1.0 5,283 1

Hash (cost=179.83..179.83 rows=5,283 width=32) (actual time=2.174..2.174 rows=5,283 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 396kB
46. 1.144 1.144 ↑ 1.0 5,283 1

Seq Scan on patients t03 (cost=0.00..179.83 rows=5,283 width=32) (actual time=0.009..1.144 rows=5,283 loops=1)

47. 4.905 4.905 ↑ 1.0 4 327

Index Scan using index_parameter_assignments_on_registration_id on sample_parameter_assignments t13 (cost=0.29..0.59 rows=4 width=1,143) (actual time=0.009..0.015 rows=4 loops=327)

  • Index Cond: (sample_registration_id = t07.id)
48. 3.331 5.405 ↑ 1.0 9,599 1

Hash (cost=323.99..323.99 rows=9,599 width=152) (actual time=5.405..5.405 rows=9,599 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1883kB
49. 2.074 2.074 ↑ 1.0 9,599 1

Seq Scan on samples t08 (cost=0.00..323.99 rows=9,599 width=152) (actual time=0.018..2.074 rows=9,599 loops=1)

50. 7.638 7.638 ↑ 1.0 1 1,273

Index Scan using index_sample_parameters_on_sample_id_and_parameter_id on sample_parameters t09 (cost=0.29..0.66 rows=1 width=652) (actual time=0.006..0.006 rows=1 loops=1,273)

  • Index Cond: ((sample_id = t08.id) AND (parameter_id = t13.analytical_parameter_id))
  • Filter: (sample_type_id = 1)
  • Rows Removed by Filter: 0
51. 37.980 37.980 ↑ 1.3 282 1,266

Seq Scan on analytical_parameter_lists t14 (cost=0.00..14.74 rows=374 width=225) (actual time=0.003..0.030 rows=282 loops=1,266)

52. 6.330 6.330 ↑ 1.0 1 1,266

Index Scan using analytical_parameters_pkey on analytical_parameters t16 (cost=0.28..0.31 rows=1 width=374) (actual time=0.005..0.005 rows=1 loops=1,266)

  • Index Cond: (id = t13.analytical_parameter_id)
53. 6.330 3,800.532 ↑ 2.0 1 1,266

Append (cost=0.00..297.62 rows=2 width=70) (actual time=1.458..3.002 rows=1 loops=1,266)

54. 0.000 0.000 ↓ 0.0 0 1,266

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

  • Filter: (((locale_code)::text = 'pt-PT'::text) AND ((key)::text = (t16.name_tkey)::text))
55. 3,794.202 3,794.202 ↑ 1.0 1 1,266

Index Scan using user_translations_pt_pt_pk on "user_translations_pt-PT" t18_1 (cost=0.41..297.61 rows=1 width=70) (actual time=1.453..2.997 rows=1 loops=1,266)

  • Index Cond: (((locale_code)::text = 'pt-PT'::text) AND ((key)::text = (t16.name_tkey)::text))
56. 0.714 0.714 ↑ 1.3 59 102

Seq Scan on functional_structures t11 (cost=0.00..2.79 rows=79 width=4) (actual time=0.004..0.007 rows=59 loops=102)

57. 0.204 0.204 ↑ 1.0 2 102

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

58. 0.204 0.204 ↑ 1.0 2 102

Seq Scan on responsability_terms t10 (cost=0.00..1.02 rows=2 width=9) (actual time=0.001..0.002 rows=2 loops=102)

59. 240.822 240.822 ↓ 441.0 441 102

Index Scan using idx_valorization_articles_customer_aggregator_type on valorization_articles t15 (cost=0.41..9.90 rows=1 width=1,380) (actual time=0.051..2.361 rows=441 loops=102)

  • 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: 2302
60. 200.226 200.226 ↓ 560.0 2,240 102

Index Scan using idx_valorization_articles_customer_aggregator_type on valorization_articles t17 (cost=0.41..9.90 rows=4 width=1,380) (actual time=0.025..1.963 rows=2,240 loops=102)

  • 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: 471
61. 187.068 187.068 ↓ 0.0 0 102

Index Scan using idx_valorization_articles_customer_aggregator_type on valorization_articles t06 (cost=0.41..9.90 rows=1 width=1,380) (actual time=1.834..1.834 rows=0 loops=102)

  • 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: 2743
62. 4,652.151 4,652.151 ↓ 100.0 100 1

CTE Scan on q (cost=0.00..0.02 rows=1 width=50,612) (actual time=138.215..4,652.151 rows=100 loops=1)

Planning time : 117.749 ms
Execution time : 4,657.399 ms