Docstore Collection table join is making the query execution extremely slow

[shreyas_mehta]

Team,

We have 2 Docstore/Collection tables. We need to join them and get appropriate data. We are facing following performance issues :

1. We need to create respective views as direct joining of these Docstores/collections is not allowed.

2. With large volume of data, it is taking huge time to execute the queries on joining these views.

Can someone please help us to solve this non-performant views' query execution issue ?

[shreyas_mehta]

Sample query :

WITH ATS AS
( SELECT "_id" AS "scenarioInstanceId"
FROM "DOCSTORE_SC_IN" )

SELECT AVG ( TO_DOUBLE("STEPS"."STPCT") ) AS "SC_Step_Cycle_Time"
FROM ATS
INNER JOIN "ADHOC_VIEW_STEPSVIEW" AS STEPS
ON STEPS."SIID" = ATS."scenarioInstanceId"
AND "STEPS"."STPCT" IS NOT NULL limit 20 offset 0;

------

Here, DOCSTORE_SC_IN and ADHOC_VIEW_STEPSVIEW are the views created from Docstore collections.

[shubham010]

WITH BDI AS

(SELECT DISTINCT "ATS"."BillingDocumentItem" AS billNoFromArchivableInstances FROM "instances" WHERE "ATS"."SC_End_Time" <= '2017-08-15T11:59:03.493Z'),

INS AS

(SELECT DISTINCT "ATS"."BillingDocumentItem" AS billNoFromNonArchivableInstances FROM "instances" WHERE "ATS"."SC_End_Time" > '2017-08-15T11:59:03.493Z')

SELECT * FROM BDI

LEFT JOIN INS

ON BDI.billNoFromArchivableInstances = INS.billNoFromNonArchivableInstances

WHERE INS.billNoFromNonArchivableInstances IS NULL;

Here "instances" is doc store collection. This query finds the left join without the common intersecting portion.