Inefficient query generated on vault queries with custom paging

Description

On postgres, when running vault queries with custom paging this is the generated sql:

1 2 3 4 5 6 7 8 9 select count(vaultschem1_.recorded_timestamp) as col_0_0_ from vault_states vaultschem0_ cross join vault_states vaultschem1_ where (vaultschem0_.output_index, vaultschem0_.transaction_id)=(vaultschem1_.output_index, vaultschem1_.transaction_id) and 1=1 and vaultschem0_.state_status=$1 and (vaultschem0_.contract_state_class_name in ($2)) limit $3

The `vault_states` table is joined with itself for no reason.
This has a significant performance impact when the `vault_states` is large.

Status

Assignee

Unassigned

Reporter

Tudor Malene

Priority

High

Labels

None

Severity

Critical

Fix versions

None

Ported to...

None

Feature Team

Select team

Affects versions

Corda 3.4