[CQLPG-6] CQL2PgJSON: replace regexp by LIKE for <> and == Created: 24/Aug/17  Updated: 21/Nov/17  Resolved: 21/Nov/17

Status: Closed
Project: cql2pgjson
Components: None
Affects versions: None
Fix versions: 1.3.0

Type: New Feature Priority: P3
Reporter: Julian Ladisch Assignee: Julian Ladisch
Resolution: Done Votes: 0
Labels: sprint22, sprint23, sprint25, sprint26
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original estimate: Not Specified

Issue links:
Relates
relates to MODINVSTOR-18 PostgreSQL performance with large ite... Closed
relates to CQLPG-10 Use unaccent instead of regexp Closed
relates to CQLPG-11 Performance tests for indexes Closed
relates to CQLPG-9 Use trigram matching (pg_trgm) Closed
Sprint:

 Description   

CQL2PgJSON always uses a regexp expression to implement <> and ==.
The regexp requires a full table scan. It should be replaced by a LIKE query if possible so that an index can be used. Examples:
groupId==/respectCase/respectAccents 'd0faefc6-68c0-4612-8ee2-8aeaf058349d'
userId==/respectCase/respectAccents mill*
Using LIKE with index reduces the query time from 1.2 seconds to 0.02 in a 1.2 million dataset.



 Comments   
Comment by Julian Ladisch [ 16/Nov/17 ]

The code is complete and awaits review: https://github.com/folio-org/cql2pgjson-java/pull/21

Generated at Thu Feb 08 23:08:26 UTC 2024 using Jira 1001.0.0-SNAPSHOT#100246-sha1:7a5c50119eb0633d306e14180817ddef5e80c75d.