DateOnly() function causes long-running query
This article addresses the Adobe Campaign issue where using the DateOnly() function within a workflow causes long-running queries. To improve query performance, the internal Ops team needs to add a custom index.
Description
Environment
- Adobe Campaign
- Adobe Campaign Classic
Issue/Symptoms
If the error below is encountered, check if the DateOnly() function is being used in the workflow queries/enrichments.
Error:
PGS-220000 PostgreSQL error: ERROR: canceling statement due to user request CONTEXT: PL/pgSQL function truncdate(timestamp with time zone,integer,text) line 2 at statement block .
This error can manifest due to unoptimized query planning (i.e. lack of index causes full table scans).
Resolution
If the warning above or long-running queries are encountered as a result of any Query or Enrichment activities within the workflows that are using the DateOnly() function, please contact Adobe Support. Support will reach out to the internal Ops team to add a custom index on the table where this function is being used. The added index should significantly improve the query performance.
If Adobe does not host the instance, then it will be necessary to reach out to the DBA (Database Administrator) to add the index.
Samples:
> create index nmsbroadlogrcp_eventTZ on nmsbroadlogrcp (truncdate(tsevent,86400,'America/New_York'::text));
create index nmsappsubscriptionrcp_eventTZ on nmsappsubscriptionrcp(truncdate(tsevent,86400,'Europe/London'::text));