graphile-contrib / postgraphile-plugin-connection-filter

Filtering on PostGraphile connections
MIT License
286 stars 32 forks source link

V5 logical operator filter performance optimization #212

Open adamni21 opened 4 months ago

adamni21 commented 4 months ago

This vastly improves performance for the and/or filters for tables with many filter fields. (When the filters are defined via a variable, the performance for inlined filters stays roughly the same.)

For example a table with 256 fields, a query with 16 elements for the or-filter, each of the elements doing using 1 operator on 1 field (eg. {"field0": {"equalTo": "test"}})

It gets the execution time of establishOperationPlan from \~25s down to 10\~15ms for the example provided. Time measurements from chrome dev tools' performance tab, and after 10\~15 request so JIT could do it's job.

query test($filter: LargeTableFilter, $orderBy: [LargeTablesOrderBy!]) {
  allLargeTables(filter: $filter, orderBy: $orderBy) {
    nodes {
      field0
    }
  }
}

with variables

{
  "filter": {
    "or": [
      {
        "field0": {
          "equalTo": "test"
        }
      },
      {
        "field1": {
          "equalTo": "test"
        }
      },
      {
        "field2": {
          "equalTo": "test"
        }
      },
      {
        "field3": {
          "equalTo": "test"
        }
      },
      {
        "field4": {
          "equalTo": "test"
        }
      },
      {
        "field5": {
          "equalTo": "test"
        }
      },
      {
        "field6": {
          "equalTo": "test"
        }
      },
      {
        "field7": {
          "equalTo": "test"
        }
      },
      {
        "field8": {
          "equalTo": "test"
        }
      },
      {
        "field9": {
          "equalTo": "test"
        }
      },
      {
        "field10": {
          "equalTo": "test"
        }
      },
      {
        "field11": {
          "equalTo": "test"
        }
      },
      {
        "field12": {
          "equalTo": "test"
        }
      },
      {
        "field13": {
          "equalTo": "test"
        }
      },
      {
        "field14": {
          "equalTo": "test"
        }
      },
     {
        "field15": {
          "equalTo": "test"
        }
      }
    ]
  }
}