Slow query when using parameters

CrateDB
1

I am currently struggling with a strange behavior with Crate using Python client. Let me explain with an example:

this is the query (may be is not the most optimized one):

SELECT
    dr.id as id, dr.name as name, dr.description as description,
    c.code as category_id, dr.unit as unit, c.cat_id as category_id_int,
    c.name as category_name, f.code as filter_id, f.name as filter_name,
    dr.isnumeric as is_numeric, array_agg(a.alarm_id) as alarms,
    dr.gateway_config, dr.type 
FROM project_demo.desc_real dr 
    LEFT JOIN project.categories c on dr.category_id = c.cat_id 
    LEFT JOIN project.var_filters f on dr.filter_id = f.filter_id 
    LEFT JOIN project.alarm_variable a ON a.var_id = dr.id AND a.var_type = 'real' 
WHERE dr.id = ANY(?) 
GROUP BY dr.id, dr.name, dr.description, c.code, dr.unit, c.cat_id, c.name, f.code, f.name, dr.isnumeric, dr.gateway_config, dr.type 
ORDER BY dr.id ASC;

The code example used is:

var_ids = [231,232]
start=time.time()
cursor.execute(sql, (var_ids,))
elapsed = time.time() - start
print(elapsed)

Using this method the elapsed time is 119.877 seconds and CPU usage is 100% during the query.

If changing the WHERE clause to use directly an array in the query:

SELECT
    dr.id as id, dr.name as name, dr.description as description,
    c.code as category_id, dr.unit as unit, c.cat_id as category_id_int,
    c.name as category_name, f.code as filter_id, f.name as filter_name,
    dr.isnumeric as is_numeric, array_agg(a.alarm_id) as alarms,
    dr.gateway_config, dr.type 
FROM project_demo.desc_real dr 
    LEFT JOIN project.categories c on dr.category_id = c.cat_id 
    LEFT JOIN project.var_filters f on dr.filter_id = f.filter_id 
    LEFT JOIN project.alarm_variable a ON a.var_id = dr.id AND a.var_type = 'real' 
WHERE dr.id = ANY([231,232])
GROUP BY dr.id, dr.name, dr.description, c.code, dr.unit, c.cat_id, c.name, f.code, f.name, dr.isnumeric, dr.gateway_config, dr.type 
ORDER BY dr.id ASC;

start=time.time()
cursor.execute(sql)
elapsed = time.time() - start
print(elapsed)

In this case the elapsed time is 0.467seconds.

I have seen this behavior with other (complex) queries also. Any suggestion on this?

Best regards,

2

Hi,
Could you check how the query looks like in sys.jobs_log in the slow case?

Would it be an option for you to use %s instead of ? in the query and then do

var_ids = [231,232]
start=time.time()
cursor.execute((sql % (var_ids,)))
elapsed = time.time() - start
print(elapsed)

?

3

Hi,

The result stmt in sys.jobs is the following (C&P):

SELECT dr.id as id, dr.name as name, dr.description as description, c.code as category_id, dr.unit as unit, c.cat_id as category_id_int, c.name as category_name, f.code as filter_id, f.name as filter_name, dr.isnumeric as is_numeric, array_agg(a.alarm_id) as alarms, dr.gateway_config, dr.type FROM project_demo.desc_real dr LEFT JOIN project.categories c on dr.category_id = c.cat_id LEFT JOIN project.var_filters f on dr.filter_id = f.filter_id LEFT JOIN project.alarm_variable a ON a.var_id = dr.id and a.var_type = 'real' WHERE dr.id = ANY(?) GROUP BY dr.id, dr.name, dr.description, c.code, dr.unit, c.cat_id, c.name, f.code, f.name, dr.isnumeric, dr.gateway_config, dr.type ORDER BY dr.id ASC;

Using the % operator is the temporary solution I use now (exactly f-strings). This method works, but using parameters makes the query slow.

Best regards,

4

Understood, could you share which Python driver you are using to connect to CrateDB and the version of CrateDB you are on?

5

Sorry, I forgot to mention.

OS: linux
Crate: 5.1.1
Python client: 0.27.1
Python: 3.8.15

Running on one node cluster under Kubernetes x64 using official image from Docker Hub.

I have been doing more test using Curl and HTTP endpoint. The results are the same, so it does not look like Python’s library problem.

6

Hi, apologies for the delay coming back to you, I mocked the tables in your query, populated them with random data, and tried to reproduce this, but I am not seeing any difference in the running times when using the parameterized query in my test environment.
Could you check SELECT * FROM pg_stats to see if there are statistics in your environment?(these are collected daily by default and flushed when the instance is restarted and they influence the decisions of the optimizer)
If you are seeing the issue with statistics in place, maybe you could try restarting the pod (kubectl get pod ____ -n _____ -o yaml | kubectl replace --force -f -) to see if we have the same behaviour without statistics?
This could also be related with the data types at play, would it be possible for you to share your table definitions?