EXPLAIN PLAN FOR
SELECT
coalesce(coalesce(c.wz, m.wz), s.wz) as wz,s.wz as wzs,c.wz as wzc,m.wz as wzm,s.end_customer_wz as wze,coalesce(s.code, m.code) as code,s.code as s_code,s.sku_code as sku_code,m.code as m_code,s.component_quantity,s.service_quantity,s.service_discount,coalesce(s.free_tier,0) as free_tier,m.dimension1,s.type,m.unit,s.ed_izm,coalesce(s.service_quantity*(100.-s.service_discount)/100.*s.price*(100.-s.component_discount)/100., 0.) as discounted_price,s.serviceStart,s.serviceEnd,s.line_key,s.price,s.component_discount,m.raw_metric,m.metric,c.contragent,c.contragent_uid,c.responsible,c.responsible_email,c.responsible_status,s.contract,s.contract_uid,s.additional_agreement,s.additional_agreement_uid,s.deal,s.deal_uid,s.abstract_service,s.modifier,s.component,s.user_description
FROM qUnifiedMetric m
FULL JOIN qSpec s ON (m.wz=s.wz AND m.code=s.sku_code) -- сервис или компонент, но не цена с типом тарификации
LEFT JOIN qContragent c ON (trim(coalesce(s.wz,m.wz))=trim(c.wz)) -- пробелы тут стоили нескольких часов работы
WHERE 1=1 --AND (s.type=3 OR NOT m.metric IS NULL)
AND EXISTS(select * from qSpec qs where qs.wz=m.wz AND qs.type=3)
--AND s.wz='WZ01031'
--AND s.wz='WZ01762'
ORDER BY coalesce(coalesce(c.wz, m.wz), s.wz), coalesce(s.code, m.code)
PLAN
1	isDistinctSelect=[false]
2	isGrouped=[false]
3	isAggregated=[false]
4	columns=[
5	CASEWHEN arg_left=[
6	IS NULL arg_left=[
7	CASEWHEN arg_left=[
8	IS NULL arg_left=[ COLUMN: PUBLIC.QCONTRAGENT.WZ
9	]] arg_right=[
10	arg_left=[ COLUMN: PUBLIC.QUNIFIEDMETRIC.WZ
11	] arg_right=[ COLUMN: PUBLIC.QCONTRAGENT.WZ
12	]]]] arg_right=[
13	arg_left=[ COLUMN: PUBLIC.QSPEC.WZ
14	] arg_right=[
15	CASEWHEN arg_left=[
16	IS NULL arg_left=[ COLUMN: PUBLIC.QCONTRAGENT.WZ
17	]] arg_right=[
18	arg_left=[ COLUMN: PUBLIC.QUNIFIEDMETRIC.WZ
19	] arg_right=[ COLUMN: PUBLIC.QCONTRAGENT.WZ
20	]]] nullable
21	COLUMN: PUBLIC.QSPEC.WZ AS WZS nullable
22	COLUMN: PUBLIC.QCONTRAGENT.WZ AS WZC nullable
23	COLUMN: PUBLIC.QUNIFIEDMETRIC.WZ AS WZM nullable
24	COLUMN: PUBLIC.QSPEC.END_CUSTOMER_WZ AS WZE nullable
25	
26	CASEWHEN arg_left=[
27	IS NULL arg_left=[ COLUMN: PUBLIC.QSPEC.CODE
28	]] arg_right=[
29	arg_left=[ COLUMN: PUBLIC.QUNIFIEDMETRIC.CODE
30	] arg_right=[ COLUMN: PUBLIC.QSPEC.CODE
31	] nullable
32	COLUMN: PUBLIC.QSPEC.CODE AS S_CODE nullable
33	COLUMN: PUBLIC.QSPEC.SKU_CODE AS SKU_CODE nullable
34	COLUMN: PUBLIC.QUNIFIEDMETRIC.CODE AS M_CODE nullable
35	COLUMN: PUBLIC.QSPEC.COMPONENT_QUANTITY nullable
36	COLUMN: PUBLIC.QSPEC.SERVICE_QUANTITY nullable
37	COLUMN: PUBLIC.QSPEC.SERVICE_DISCOUNT nullable
38	
39	CASEWHEN arg_left=[
40	IS NULL arg_left=[ COLUMN: PUBLIC.QSPEC.FREE_TIER
41	]] arg_right=[
42	arg_left=[
43	VALUE = 0, TYPE = INTEGER] arg_right=[ COLUMN: PUBLIC.QSPEC.FREE_TIER
44	] nullable
45	COLUMN: PUBLIC.QUNIFIEDMETRIC.DIMENSION1 nullable
46	COLUMN: PUBLIC.QSPEC.TYPE nullable
47	COLUMN: PUBLIC.QUNIFIEDMETRIC.UNIT nullable
48	COLUMN: PUBLIC.QSPEC.ED_IZM nullable
49	
50	CASEWHEN arg_left=[
51	IS NULL arg_left=[
52	DIVIDE arg_left=[
53	MULTIPLY arg_left=[
54	MULTIPLY arg_left=[
55	DIVIDE arg_left=[
56	MULTIPLY arg_left=[ COLUMN: PUBLIC.QSPEC.SERVICE_QUANTITY
57	] arg_right=[
58	SUBTRACT arg_left=[
59	VALUE = 100, TYPE = DECIMAL] arg_right=[ COLUMN: PUBLIC.QSPEC.SERVICE_DISCOUNT
60	]]] arg_right=[
61	VALUE = 100, TYPE = DECIMAL]] arg_right=[ COLUMN: PUBLIC.QSPEC.PRICE
62	]] arg_right=[
63	SUBTRACT arg_left=[
64	VALUE = 100, TYPE = DECIMAL] arg_right=[ COLUMN: PUBLIC.QSPEC.COMPONENT_DISCOUNT
65	]]] arg_right=[
66	VALUE = 100, TYPE = DECIMAL]]] arg_right=[
67	arg_left=[
68	VALUE = 0, TYPE = DECIMAL] arg_right=[
69	DIVIDE arg_left=[
70	MULTIPLY arg_left=[
71	MULTIPLY arg_left=[
72	DIVIDE arg_left=[
73	MULTIPLY arg_left=[ COLUMN: PUBLIC.QSPEC.SERVICE_QUANTITY
74	] arg_right=[
75	SUBTRACT arg_left=[
76	VALUE = 100, TYPE = DECIMAL] arg_right=[ COLUMN: PUBLIC.QSPEC.SERVICE_DISCOUNT
77	]]] arg_right=[
78	VALUE = 100, TYPE = DECIMAL]] arg_right=[ COLUMN: PUBLIC.QSPEC.PRICE
79	]] arg_right=[
80	SUBTRACT arg_left=[
81	VALUE = 100, TYPE = DECIMAL] arg_right=[ COLUMN: PUBLIC.QSPEC.COMPONENT_DISCOUNT
82	]]] arg_right=[
83	VALUE = 100, TYPE = DECIMAL]] nullable
84	COLUMN: PUBLIC.QSPEC.SERVICESTART nullable
85	COLUMN: PUBLIC.QSPEC.SERVICEEND nullable
86	COLUMN: PUBLIC.QSPEC.LINE_KEY nullable
87	COLUMN: PUBLIC.QSPEC.PRICE nullable
88	COLUMN: PUBLIC.QSPEC.COMPONENT_DISCOUNT nullable
89	COLUMN: PUBLIC.QUNIFIEDMETRIC.RAW_METRIC nullable
90	COLUMN: PUBLIC.QUNIFIEDMETRIC.METRIC nullable
91	COLUMN: PUBLIC.QCONTRAGENT.CONTRAGENT nullable
92	COLUMN: PUBLIC.QCONTRAGENT.CONTRAGENT_UID nullable
93	COLUMN: PUBLIC.QCONTRAGENT.RESPONSIBLE nullable
94	COLUMN: PUBLIC.QCONTRAGENT.RESPONSIBLE_EMAIL nullable
95	COLUMN: PUBLIC.QCONTRAGENT.RESPONSIBLE_STATUS nullable
96	COLUMN: PUBLIC.QSPEC.CONTRACT nullable
97	COLUMN: PUBLIC.QSPEC.CONTRACT_UID nullable
98	COLUMN: PUBLIC.QSPEC.ADDITIONAL_AGREEMENT nullable
99	COLUMN: PUBLIC.QSPEC.ADDITIONAL_AGREEMENT_UID nullable
100	COLUMN: PUBLIC.QSPEC.DEAL nullable
101	COLUMN: PUBLIC.QSPEC.DEAL_UID nullable
102	COLUMN: PUBLIC.QSPEC.ABSTRACT_SERVICE nullable
103	COLUMN: PUBLIC.QSPEC.MODIFIER nullable
104	COLUMN: PUBLIC.QSPEC.COMPONENT nullable
105	COLUMN: PUBLIC.QSPEC.USER_DESCRIPTION nullable
106	
107	]
108	[range variable 1
109	join type=INNER
110	table=QUNIFIEDMETRIC
111	alias=M
112	cardinality=3931
113	access=FULL SCAN
114	join condition = [index=SYS_IDX_10266
115	]
116	][range variable 2
117	join type=FULL
118	table=QSPEC
119	alias=S
120	cardinality=4819
121	access=INDEX PRED
122	join condition = [index=SYS_IDX_T_10270
123	start conditions=[
124	EQUAL arg_left=[ COLUMN: PUBLIC.QSPEC.WZ
125	] arg_right=[ COLUMN: PUBLIC.QUNIFIEDMETRIC.WZ
126	]]
127	other condition=[
128	EQUAL arg_left=[ COLUMN: PUBLIC.QSPEC.SKU_CODE
129	] arg_right=[ COLUMN: PUBLIC.QUNIFIEDMETRIC.CODE
130	]]
131	]
132	where condition = [
133	EXISTS arg_left=[
134	QUERY isDistinctSelect=[false]
135	isGrouped=[false]
136	isAggregated=[false]
137	columns=[ COLUMN: PUBLIC.QSPEC.WZ nullable
138	COLUMN: PUBLIC.QSPEC.CONTRAGENT_UID nullable
139	COLUMN: PUBLIC.QSPEC.CODE nullable
140	COLUMN: PUBLIC.QSPEC.SKU_CODE nullable
141	COLUMN: PUBLIC.QSPEC.PRICE nullable
142	COLUMN: PUBLIC.QSPEC.COMPONENT_DISCOUNT nullable
143	COLUMN: PUBLIC.QSPEC.CONTRAGENT nullable
144	COLUMN: PUBLIC.QSPEC.CONTRACT nullable
145	COLUMN: PUBLIC.QSPEC.CONTRACT_UID nullable
146	COLUMN: PUBLIC.QSPEC.DEAL nullable
147	COLUMN: PUBLIC.QSPEC.DEAL_UID nullable
148	COLUMN: PUBLIC.QSPEC.ABSTRACT_SERVICE nullable
149	COLUMN: PUBLIC.QSPEC.MODIFIER nullable
150	COLUMN: PUBLIC.QSPEC.USER_DESCRIPTION nullable
151	COLUMN: PUBLIC.QSPEC.LINE_KEY nullable
152	COLUMN: PUBLIC.QSPEC.SERVICESTART nullable
153	COLUMN: PUBLIC.QSPEC.SERVICEEND nullable
154	COLUMN: PUBLIC.QSPEC.SERVICE_DISCOUNT nullable
155	COLUMN: PUBLIC.QSPEC.SERVICE_QUANTITY nullable
156	COLUMN: PUBLIC.QSPEC.ED_IZM nullable
157	COLUMN: PUBLIC.QSPEC.COMPONENT nullable
158	COLUMN: PUBLIC.QSPEC.TYPE nullable
159	COLUMN: PUBLIC.QSPEC.COMPONENT_QUANTITY nullable
160	COLUMN: PUBLIC.QSPEC.ADDITIONAL_AGREEMENT nullable
161	COLUMN: PUBLIC.QSPEC.ADDITIONAL_AGREEMENT_UID nullable
162	COLUMN: PUBLIC.QSPEC.END_CUSTOMER_UID nullable
163	COLUMN: PUBLIC.QSPEC.END_CUSTOMER_WZ nullable
164	COLUMN: PUBLIC.QSPEC.END_CUSTOMER_NAME nullable
165	COLUMN: PUBLIC.QSPEC.FREE_TIER nullable
166	
167	]
168	[range variable 1
169	join type=INNER
170	table=QSPEC
171	alias=QS
172	cardinality=4819
173	access=INDEX PRED
174	join condition = [index=SYS_IDX_T_10270
175	start conditions=[
176	EQUAL arg_left=[ COLUMN: PUBLIC.QSPEC.WZ
177	] arg_right=[ COLUMN: PUBLIC.QUNIFIEDMETRIC.WZ
178	]
179	EQUAL arg_left=[ COLUMN: PUBLIC.QSPEC.TYPE
180	] arg_right=[
181	VALUE = 3, TYPE = INTEGER]]
182	]
183	] ]
184	] ]
185	][range variable 3
186	join type=LEFT OUTER
187	table=QCONTRAGENT
188	alias=C
189	cardinality=2912
190	access=FULL SCAN
191	join condition = [index=SYS_IDX_10267
192	other condition=[
193	EQUAL arg_left=[
194	FUNCTION =[
195	TRIM([
196	VALUE = 26, TYPE = INTEGER][
197	VALUE = ' ', TYPE = CHARACTER][
198	CASEWHEN arg_left=[
199	IS NULL arg_left=[ COLUMN: PUBLIC.QSPEC.WZ
200	]] arg_right=[
201	arg_left=[ COLUMN: PUBLIC.QUNIFIEDMETRIC.WZ
202	] arg_right=[ COLUMN: PUBLIC.QSPEC.WZ
203	]]]) returns VARCHAR]
204	] arg_right=[
205	FUNCTION =[
206	TRIM([
207	VALUE = 26, TYPE = INTEGER][
208	VALUE = ' ', TYPE = CHARACTER][ COLUMN: PUBLIC.QCONTRAGENT.WZ
209	]) returns VARCHAR]
210	]]
211	]
212	]]
213	order by=[
214	
215	CASEWHEN arg_left=[
216	IS NULL arg_left=[
217	CASEWHEN arg_left=[
218	IS NULL arg_left=[ COLUMN: PUBLIC.QCONTRAGENT.WZ
219	]] arg_right=[
220	arg_left=[ COLUMN: PUBLIC.QUNIFIEDMETRIC.WZ
221	] arg_right=[ COLUMN: PUBLIC.QCONTRAGENT.WZ
222	]]]] arg_right=[
223	arg_left=[ COLUMN: PUBLIC.QSPEC.WZ
224	] arg_right=[
225	CASEWHEN arg_left=[
226	IS NULL arg_left=[ COLUMN: PUBLIC.QCONTRAGENT.WZ
227	]] arg_right=[
228	arg_left=[ COLUMN: PUBLIC.QUNIFIEDMETRIC.WZ
229	] arg_right=[ COLUMN: PUBLIC.QCONTRAGENT.WZ
230	]]]]
231	CASEWHEN arg_left=[
232	IS NULL arg_left=[ COLUMN: PUBLIC.QSPEC.CODE
233	]] arg_right=[
234	arg_left=[ COLUMN: PUBLIC.QUNIFIEDMETRIC.CODE
235	] arg_right=[ COLUMN: PUBLIC.QSPEC.CODE
236	]]]
237	PARAMETERS=[]
238	SUBQUERIES[
239	[level=1
240	isDistinctSelect=[false]
241	isGrouped=[false]
242	isAggregated=[false]
243	columns=[ COLUMN: PUBLIC.QSPEC.WZ nullable
244	COLUMN: PUBLIC.QSPEC.CONTRAGENT_UID nullable
245	COLUMN: PUBLIC.QSPEC.CODE nullable
246	COLUMN: PUBLIC.QSPEC.SKU_CODE nullable
247	COLUMN: PUBLIC.QSPEC.PRICE nullable
248	COLUMN: PUBLIC.QSPEC.COMPONENT_DISCOUNT nullable
249	COLUMN: PUBLIC.QSPEC.CONTRAGENT nullable
250	COLUMN: PUBLIC.QSPEC.CONTRACT nullable
251	COLUMN: PUBLIC.QSPEC.CONTRACT_UID nullable
252	COLUMN: PUBLIC.QSPEC.DEAL nullable
253	COLUMN: PUBLIC.QSPEC.DEAL_UID nullable
254	COLUMN: PUBLIC.QSPEC.ABSTRACT_SERVICE nullable
255	COLUMN: PUBLIC.QSPEC.MODIFIER nullable
256	COLUMN: PUBLIC.QSPEC.USER_DESCRIPTION nullable
257	COLUMN: PUBLIC.QSPEC.LINE_KEY nullable
258	COLUMN: PUBLIC.QSPEC.SERVICESTART nullable
259	COLUMN: PUBLIC.QSPEC.SERVICEEND nullable
260	COLUMN: PUBLIC.QSPEC.SERVICE_DISCOUNT nullable
261	COLUMN: PUBLIC.QSPEC.SERVICE_QUANTITY nullable
262	COLUMN: PUBLIC.QSPEC.ED_IZM nullable
263	COLUMN: PUBLIC.QSPEC.COMPONENT nullable
264	COLUMN: PUBLIC.QSPEC.TYPE nullable
265	COLUMN: PUBLIC.QSPEC.COMPONENT_QUANTITY nullable
266	COLUMN: PUBLIC.QSPEC.ADDITIONAL_AGREEMENT nullable
267	COLUMN: PUBLIC.QSPEC.ADDITIONAL_AGREEMENT_UID nullable
268	COLUMN: PUBLIC.QSPEC.END_CUSTOMER_UID nullable
269	COLUMN: PUBLIC.QSPEC.END_CUSTOMER_WZ nullable
270	COLUMN: PUBLIC.QSPEC.END_CUSTOMER_NAME nullable
271	COLUMN: PUBLIC.QSPEC.FREE_TIER nullable
272	
273	]
274	[range variable 1
275	join type=INNER
276	table=QSPEC
277	alias=QS
278	cardinality=4819
279	access=INDEX PRED
280	join condition = [index=SYS_IDX_T_10270
281	start conditions=[
282	EQUAL arg_left=[ COLUMN: PUBLIC.QSPEC.WZ
283	] arg_right=[ COLUMN: PUBLIC.QUNIFIEDMETRIC.WZ
284	]
285	EQUAL arg_left=[ COLUMN: PUBLIC.QSPEC.TYPE
286	] arg_right=[
287	VALUE = 3, TYPE = INTEGER]]
288	]
289	] ]
290	]]

-----------------------
Here is a detailed breakdown of your HSQLDB execution plan. The query contains a hidden performance trap—specifically, an accidental implicit Inner Join conversion and a costly Full Table Scan—which we can optimize.🚨 Critical Performance Issues Found1. The FULL JOIN Was Converted into an INNER JOIN (Lines 108–109)Look closely at Line 109: join type=INNER for table QUNIFIEDMETRIC (M).Why this happened: In your WHERE clause, you have an EXISTS subquery: where qs.wz = m.wz.The impact: Because m.wz must match something in qSpec to pass the EXISTS filter, any row where M exists but S does not (the "left" part of your FULL JOIN) is immediately discarded. HSQLDB noticed this and optimized your FULL JOIN down to a standard INNER JOIN.2. Full Table Scan on QCONTRAGENT (Lines 185–191)Look at Line 190: access=FULL SCAN for table QCONTRAGENT (C).Why this happened: Your join condition uses a function: ON (trim(coalesce(s.wz, m.wz)) = trim(c.wz)).The impact: Applying trim() to a column prevents the database from using standard indexes on c.wz. For every single row combination, HSQLDB is forced to scan the entire 2,912-row table sequentially.🔍 Step-by-Step Plan BreakdownStep 1: Scan QUNIFIEDMETRIC M (Lines 108–113)Access: FULL SCANRows: 3,931The database starts here because it serves as the base for evaluating the EXISTS clause.Step 2: Probe QSPEC S via Index (Lines 116–131)Access: INDEX PRED (Index Predicate) using index SYS_IDX_T_10270.Execution: It successfully uses the index on wz to fetch corresponding records from S. The secondary condition m.code = s.sku_code is evaluated as an "other condition" (filter) afterwards.Step 3: Evaluate the EXISTS Subquery (Lines 132–184)Access: INDEX PRED on QSPEC QS.The database runs a semi-join utilizing the index to verify if a matching wz with type = 3 exists. This part is relatively efficient.Step 4: Scan and Join QCONTRAGENT C (Lines 185–202)Access: FULL SCANBecause of the trim(...) wrappers, it falls back to a nested loop full table scan to evaluate the dynamic string alignment.🛠️ Optimization RecommendationsTo speed up this query dramatically, apply these two adjustments:1. Explicitly clean the data instead of using trim() in the joinIf possible, run a one-time script to permanently clean whitespace from your database tables:sqlUPDATE qContragent SET wz = TRIM(wz);
UPDATE qSpec SET wz = TRIM(wz);
UPDATE qUnifiedMetric SET wz = TRIM(wz);
Use code with caution.Once cleaned, rewrite the join smoothly to leverage indexes:sqlLEFT JOIN qContragent c ON (coalesce(s.wz, m.wz) = c.wz)
Use code with caution.2. Downgrade FULL JOIN to LEFT JOIN or INNER JOINSince your EXISTS condition fundamentally breaks the FULL JOIN behavior anyway, changing the keyword to LEFT JOIN qSpec s or INNER JOIN qSpec s makes your code match its real execution pathway, preventing the optimizer from doing unnecessary planning logic.If you'd like to check how to implement a functional index to bypass the trim() problem, let me know which HSQLDB version you are currently running!