[BUG] JSQLParser 5.1 : PostgreSQL : Parsing takes too long and times out

[tomershay]

Parser takes a long time to parse this simple query, which causes a timeout (my default timeout is set for 20 seconds). Tested with latest version.

SELECT
  1
FROM
  public.tbl
WHERE
  fieldd ->> 'att1' = 1
  OR fieldd ->> 'att1' = 1
  OR fieldd ->> 'att1' = 1
  OR fieldd::jsonb -> 'att2' @> 1
  OR fieldd::jsonb -> 'att2' @> 1
  OR fieldd::jsonb -> 'att2' @> 1
  OR fieldd::jsonb -> 'att2' @> 1
  OR fieldd::jsonb -> 'att2' @> 1
  OR fieldd::jsonb -> 'att2' @> 1
  OR fieldd::jsonb -> 'att2' @> 1
  OR fieldd::jsonb -> 'att2' @> 1
  OR fieldd::jsonb -> 'att2' @> 1
  OR fieldd::jsonb -> 'att2' @> 1
  OR fieldd::jsonb -> 'att2' @> 1
ORDER BY
  att ASC
LIMIT
  1

[manticore-projects]

Greetings @tomershay, thank you for your report. It is always good to read from you.

May I ask you for some help for speeding up a resolution: can you step by step trim this statement down, removing expressions until we hit a sweet spot of parsing "fast".

Once we understand which Expression triggers this amplification of parsing time, I can dive into the Grammar.

Thanks and cheers

[tomershay]

Thanks for the quick reply @manticore-projects, appreciate it.

It seems we can use this query as a baseline, as the parser returns rather instantly with it:

SELECT
  1
FROM
  public.tbl
WHERE
  fieldd ->> 'att1' = 1
  OR fieldd ->> 'att1' = 1
  OR fieldd ->> 'att1' = 1
  OR fieldd::jsonb -> 'att2' @> 1
  OR fieldd::jsonb -> 'att2' @> 1
  OR fieldd::jsonb -> 'att2' @> 1
  OR fieldd::jsonb -> 'att2' @> 1
  OR fieldd::jsonb -> 'att2' @> 1
  OR fieldd::jsonb -> 'att2' @> 1
ORDER BY
  att ASC
LIMIT
  1

Then, for each OR fieldd::jsonb -> 'att2' @> 1 added to the WHERE clause, it seems the parser takes 1-4 seconds extra, so when you add multiple such expressions, it goes above a reasonable timeout.

[manticore-projects]

Great, I will look into this.

[manticore-projects]

Less than 1 second now.

[tomershay]

Thank you for taking the time to test this. I'll try to locate why it behaves differently on our end. Thanks!