Skip to main content

On behalf of Celanese

Hi.

I would like to report a potential issue with cursor-based pagination when sorting in descending order and request clarification on the undocumented forceCursorsDespitePerformanceHazard flag.

We are executing a query against the view OEEEvent, which is defined with a cursorable BTREE index on the startDateTime property, as shown in the simplified schema below:

 

type OEEEvent
@container(
  indexes: :
    {
      fields: i"startDateTime"]
      identifier: "idx_oeev_startDateTime"
      indexType: BTREE
      cursorable: true
    }
  ]
) {
  startDateTime: Timestamp
  endDateTime: Timestamp
  totalDuration: Float
}
 

We are querying with the following time range filter:

 

"startDateTime": {
  "gte": "2024-12-31T00:00:00.000-03:00",
  "lt":  "2025-05-01T00:00:00.000-03:00"
}
 

The result set includes 840 records.

When applying a page size of 500 and using the following ascending sort configuration:

 

{
  "property":  
    "INO-COR-ALL-DML",
    "OEEEvent/472bdbcb9cd6f1",
    "startDateTime"
  ],
  "direction": "ascending"
}
 

The query returns two pages, as expected.

However, when changing the sort direction to descending:

 

{
  "property": e
    "INO-COR-ALL-DML",
    "OEEEvent/472bdbcb9cd6f1",
    "startDateTime"
  ],
  "direction": "descending"
}

 

Only one page is returned, which appears to be incorrect and suggests a bug in how pagination is handled when sorting in descending order on a cursorable index.

To further investigate, I reproduced the query in the Fusion UI GraphQL playground. Interestingly, the issue did not occur there. Upon examining the DMS output, I noticed the presence of a flag named forceCursorsDespitePerformanceHazard, which seems to resolve the issue.

However, this flag does not appear to be documented. Since this query is critical in Celanese’s production environment, we would appreciate clarification on the following:

  1. What is the purpose of the forceCursorsDespitePerformanceHazard flag?

  2. Under what conditions should it be used?

  3. Is the inconsistent behavior between ascending and descending sorts expected?

  4. Is there a recommended or permanent fix planned for this issue?

Thank you in advance for your support.

Best regards,
Lucas Alves

Hi Lucas, 

Thanks for reaching out! It’s a good question and I understand the confusion.

First some background on the `forceCursorsDespitePerformanceHazard` flag. This flag lets you force a cursor being returned despite the custom sort not being backed by a cursorable index. The service powering the graphql layer has always emitted cursors for arbitrary sort/filter combinations. It did this by having its own cursoring implementation which was, until recently, broken in several ways, both wrt. performance and correctness. The only way a query with a custom sort can be performant is if the amount of data is trivial, or the sort is backed by a cursorable index. The cursoring implementation has now been moved to a service further down in our stack, and we have exposed the `forceCursorsDespitePerformanceHazard` flag in order to keep the graphql layer backwards compatible.

This means that `forceCursorsDespitePerformanceHazard` should in practice _never_ be used. Instead, you should use cursorable indexes. Since you are getting a cursor when direction=ascending, this indicates that you have a cursorable index backing the sort. However, when you flip the direction to descending, you also need to flip the `nullsFirst` flag in order to keep using the cursorable index. This topic is covered in detail here: https://docs.cognite.com/cdf/dm/dm_guides/dm_performance_considerations/#pagination-cursorable-indexes

Let me know if you want to dive deeper into any of this!

Hi Erlend.

Thank you for the detailed explanation.

I’ve tested the query with the nullsFirst flag enabled, and I can confirm that the results now align with expectations.

Appreciate your support.

 

Reply


Terms & ConditionsCookie Settings