На сервере такой запрос выполняется очень долго:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
SELECT eh.ehistid,
rsc.rCount AS totalcount,
rsc.cCount AS reached,
rsc.rpndCount AS responded,
CASE
WHEN eh.tClosed IS NULL
THEN 'ACTIVE'
ELSE 'COMPLETED'
END AS status,
COUNT(ro.rOptionid) AS responseOptionsCount
FROM evthist eh
JOIN nHist nh ON eh.nHistId = nh.nHistId
LEFT JOIN reptSummaryCount rsc ON eh.ehistid = rsc.ehistid
LEFT JOIN respInfo ri ON eh.eventid = ri.eventid
LEFT JOIN RespOption ro ON ri.respInfoid = ro.respInfoid
WHERE eh.ehistid IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15)
GROUP BY eh.ehistid,
rsc.rCount,
rsc.cCount,
rsc.rpndCount,
CASE
WHEN eh.tClosed IS NULL
THEN 'ACTIVE'
ELSE 'COMPLETED'
END
Вот такой план:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
277.
278.
279.
280.
281.
282.
283.
284.
285.
286.
287.
288.
289.
290.
291.
292.
293.
294.
295.
296.
297.
298.
299.
300.
301.
302.
303.
304.
305.
306.
307.
308.
309.
310.
311.
312.
313.
314.
315.
316.
317.
318.
319.
320.
321.
322.
323.
324.
325.
326.
327.
328.
329.
330.
331.
332.
333.
334.
335.
336.
337.
338.
339.
340.
341.
342.
343.
344.
345.
346.
347.
348.
349.
350.
351.
352.
353.
354.
355.
356.
357.
358.
359.
360.
361.
362.
363.
364.
365.
366.
367.
368.
369.
370.
371.
372.
373.
374.
375.
376.
377.
378.
379.
380.
381.
382.
383.
384.
385.
386.
387.
388.
389.
390.
391.
392.
393.
394.
395.
396.
397.
398.
399.
400.
401.
402.
403.
404.
405.
406.
407.
408.
409.
410.
411.
412.
413.
414.
415.
416.
417.
418.
419.
420.
421.
422.
423.
424.
425.
426.
427.
428.
429.
430.
431.
432.
433.
434.
435.
436.
437.
438.
439.
440.
441.
442.
443.
444.
445.
446.
447.
448.
449.
450.
451.
452.
453.
454.
455.
456.
457.
458.
459.
460.
461.
462.
463.
464.
465.
466.
467.
468.
469.
470.
471.
472.
473.
474.
475.
476.
477.
478.
479.
480.
481.
482.
483.
484.
485.
486.
487.
488.
489.
490.
491.
492.
493.
494.
495.
496.
497.
498.
499.
500.
501.
502.
503.
504.
505.
506.
507.
508.
509.
510.
511.
512.
513.
514.
515.
516.
517.
518.
519.
520.
521.
522.
523.
524.
525.
526.
527.
528.
529.
530.
531.
532.
533.
534.
[
{
"Plan": {
"Node Type": "Aggregate",
"Strategy": "Sorted",
"Partial Mode": "Simple",
"Parallel Aware": false,
"Startup Cost": 160.57,
"Total Cost": 160.98,
"Plan Rows": 15,
"Plan Width": 72,
"Actual Startup Time": 0.518,
"Actual Total Time": 0.523,
"Actual Rows": 15,
"Actual Loops": 1,
"Output": [
"eh.ehistid",
"rsc.rCount",
"rsc.cCount",
"rsc.rpndCount",
"(CASE WHEN (eh.tClosed IS NULL) THEN 'ACTIVE'::text ELSE 'COMPLETED'::text END)",
"count(ro.rOptionid)"
],
"Group Key": [
"eh.ehistid",
"rsc.rCount",
"rsc.cCount",
"rsc.rpndCount",
"(CASE WHEN (eh.tClosed IS NULL) THEN 'ACTIVE'::text ELSE 'COMPLETED'::text END)"
],
"Shared Hit Blocks": 94,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 160.57,
"Total Cost": 160.6,
"Plan Rows": 15,
"Plan Width": 72,
"Actual Startup Time": 0.514,
"Actual Total Time": 0.515,
"Actual Rows": 15,
"Actual Loops": 1,
"Output": [
"eh.ehistid",
"rsc.rCount",
"rsc.cCount",
"rsc.rpndCount",
"(CASE WHEN (eh.tClosed IS NULL) THEN 'ACTIVE'::text ELSE 'COMPLETED'::text END)",
"ro.rOptionid"
],
"Sort Key": [
"eh.ehistid",
"rsc.rCount",
"rsc.cCount",
"rsc.rpndCount",
"(CASE WHEN (eh.tClosed IS NULL) THEN 'ACTIVE'::text ELSE 'COMPLETED'::text END)"
],
"Sort Method": "quicksort",
"Sort Space Used": 26,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 94,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 97.38,
"Total Cost": 160.27,
"Plan Rows": 15,
"Plan Width": 72,
"Actual Startup Time": 0.29,
"Actual Total Time": 0.507,
"Actual Rows": 15,
"Actual Loops": 1,
"Output": [
"eh.ehistid",
"rsc.rCount",
"rsc.cCount",
"rsc.rpndCount",
"CASE WHEN (eh.tClosed IS NULL) THEN 'ACTIVE'::text ELSE 'COMPLETED'::text END",
"ro.rOptionid"
],
"Inner Unique": false,
"Shared Hit Blocks": 94,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 97.24,
"Total Cost": 155.7,
"Plan Rows": 15,
"Plan Width": 48,
"Actual Startup Time": 0.266,
"Actual Total Time": 0.478,
"Actual Rows": 15,
"Actual Loops": 1,
"Output": [
"eh.ehistid",
"eh.tClosed",
"rsc.rCount",
"rsc.cCount",
"rsc.rpndCount",
"ri.respInfoid"
],
"Inner Unique": false,
"Hash Cond": "(eh.eventid = ri.eventid)",
"Shared Hit Blocks": 92,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 87.91,
"Total Cost": 146.3,
"Plan Rows": 15,
"Plan Width": 48,
"Actual Startup Time": 0.226,
"Actual Total Time": 0.436,
"Actual Rows": 15,
"Actual Loops": 1,
"Output": [
"eh.ehistid",
"eh.tClosed",
"eh.eventid",
"rsc.rCount",
"rsc.cCount",
"rsc.rpndCount"
],
"Inner Unique": false,
"Hash Cond": "(nh.nHistId = eh.nHistId)",
"Shared Hit Blocks": 86,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "nHist",
"Schema": "public",
"Alias": "nh",
"Startup Cost": 0,
"Total Cost": 52.72,
"Plan Rows": 1472,
"Plan Width": 8,
"Actual Startup Time": 0.003,
"Actual Total Time": 0.099,
"Actual Rows": 1472,
"Actual Loops": 1,
"Output": [
"nh.nHistId",
"nh.notifid",
"nh.initiatorfirstname",
"nh.initiatorlastname",
"nh.initiatoruserid",
"nh.title",
"nh.userdata",
"nh.useruniqueid",
"nh.notiftext",
"nh.timesent",
"nh.groupnotification",
"nh.triggerbydevicehistid",
"nh.organizationid",
"nh.orgnodeid",
"nh.triggerbyoptionhistid",
"nh.yosemiteid",
"nh.hidden",
"nh.incidentid",
"nh.incidenttitle",
"nh.eventsubscriptionid",
"nh.eventsubscriptiondataid"
],
"Shared Hit Blocks": 38,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 87.72,
"Total Cost": 87.72,
"Plan Rows": 15,
"Plan Width": 56,
"Actual Startup Time": 0.22,
"Actual Total Time": 0.22,
"Actual Rows": 15,
"Actual Loops": 1,
"Output": [
"eh.ehistid",
"eh.tClosed",
"eh.nHistId",
"eh.eventid",
"rsc.rCount",
"rsc.cCount",
"rsc.rpndCount"
],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 10,
"Shared Hit Blocks": 48,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Right",
"Startup Cost": 52.14,
"Total Cost": 87.72,
"Plan Rows": 15,
"Plan Width": 56,
"Actual Startup Time": 0.023,
"Actual Total Time": 0.217,
"Actual Rows": 15,
"Actual Loops": 1,
"Output": [
"eh.ehistid",
"eh.tClosed",
"eh.nHistId",
"eh.eventid",
"rsc.rCount",
"rsc.cCount",
"rsc.rpndCount"
],
"Inner Unique": true,
"Hash Cond": "(rsc.ehistid = eh.ehistid)",
"Shared Hit Blocks": 48,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "reptSummaryCount",
"Schema": "public",
"Alias": "rsc",
"Startup Cost": 0,
"Total Cost": 31.71,
"Plan Rows": 1471,
"Plan Width": 32,
"Actual Startup Time": 0.003,
"Actual Total Time": 0.081,
"Actual Rows": 1471,
"Actual Loops": 1,
"Output": [
"rsc.ehistid",
"rsc.rCount",
"rsc.cCount",
"rsc.rpndCount",
"rsc.phonetimeseconds"
],
"Shared Hit Blocks": 17,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 51.95,
"Total Cost": 51.95,
"Plan Rows": 15,
"Plan Width": 32,
"Actual Startup Time": 0.017,
"Actual Total Time": 0.017,
"Actual Rows": 15,
"Actual Loops": 1,
"Output": [
"eh.ehistid",
"eh.tClosed",
"eh.nHistId",
"eh.eventid"
],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 9,
"Shared Hit Blocks": 31,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "evthist_pkey",
"Relation Name": "evthist",
"Schema": "public",
"Alias": "eh",
"Startup Cost": 0.28,
"Total Cost": 51.95,
"Plan Rows": 15,
"Plan Width": 32,
"Actual Startup Time": 0.004,
"Actual Total Time": 0.014,
"Actual Rows": 15,
"Actual Loops": 1,
"Output": [
"eh.ehistid",
"eh.tClosed",
"eh.nHistId",
"eh.eventid"
],
"Index Cond": "(eh.ehistid = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}'::bigint[]))",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 31,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
}
]
}
]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 7.48,
"Total Cost": 7.48,
"Plan Rows": 148,
"Plan Width": 16,
"Actual Startup Time": 0.038,
"Actual Total Time": 0.038,
"Actual Rows": 148,
"Actual Loops": 1,
"Output": [
"ri.eventid",
"ri.respInfoid"
],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 15,
"Shared Hit Blocks": 6,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "respInfo",
"Schema": "public",
"Alias": "ri",
"Startup Cost": 0,
"Total Cost": 7.48,
"Plan Rows": 148,
"Plan Width": 16,
"Actual Startup Time": 0.003,
"Actual Total Time": 0.02,
"Actual Rows": 148,
"Actual Loops": 1,
"Output": [
"ri.eventid",
"ri.respInfoid"
],
"Shared Hit Blocks": 6,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "responseopt_responseinfofk_ndx",
"Relation Name": "RespOption",
"Schema": "public",
"Alias": "ro",
"Startup Cost": 0.14,
"Total Cost": 0.3,
"Plan Rows": 1,
"Plan Width": 16,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.002,
"Actual Rows": 0,
"Actual Loops": 15,
"Output": [
"ro.rOptionid",
"ro.respInfoid",
"ro.callbridgestring",
"ro.issuccess",
"ro.responsenumber",
"ro.text",
"ro.cascadeid",
"ro.followupid",
"ro.voicefileid",
"ro.messageid",
"ro.recipientnotavailable",
"ro.isinternal"
],
"Index Cond": "(ri.respInfoid = ro.respInfoid)",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
}
]
},
"Triggers": []
}
]
Что можно попробовать сделать? |
