Does the number of conditions in WHERE clause, affect the query execution time?

I’ve notices that queries in the form of

SELECT name, title
FROM mydb
WHERE ANY relation IN relationship SATISFIES = “id-2”
AND (ANY relation IN …
AND (ANY ANY relation IN … END)
are quicker than a query with a simple condition like the following

SELECT name, title
FROM mydb
WHERE ANY relation IN relationship SATISFIES = “id-2” END

These queries are executed in a small dataset, with the first to be much quicker.
Why is that happening? Is the number of conditions a factor affecting the query performance?

@iwanna_ster ,
Which is one faster. you mentioned first one quicker and later first to be slower?

Your queries using ANY clause,

  1. depends on relation array size. If the matching condition is at the end of the array it needs to evaluate until matching condition.
  2. If you have multiple ANY clause each one has to evaluate.

If it using array index and multiple ANY using same array, you are asking match more than one element in different position of the array. Depends on what is it can use IntersectScan. some times it can take time.

If using EE execute queries in Query Work bench and goto plan Text which gives profile info (timings, #ItemsIn/ItemsOut for each operator and see where took time). Checkout links in Filter taking too long - #3 by pccb

The first one with nested ANY…-IN… END expressions is much quicker that the second one.

I’'m using Query Workbench.

You have AND , it might used different index and that indexscan might produced less results and terminated early. Take look at execution plan text and will give details.

1 Like

I’ve checked the indexes. In my database i use only a primary key for all the queries, and the results are correct.

Hi again! I collected some information from Plan Text of my queries and i want to analyze them.

First of all, i have no indexes on my database, just a primary index. So, using Plan Tree information i want to figure out why some queries are slower than others. Trying to do this i realized that i can’t undestand or find enough information about what is a ‘Sequence’ operator, an ‘InitialProject’ operator, a ‘Fetch’ operator, a ‘Stream’ or a ‘Final Project’ operator.

As about times, i’ve read the Filter taking too long - #3 by pccb question defining execTime, kernTime and servTime, but still i cannot understand the operators in Plan Text in order to find out why some queries are slower than others, clearly.

@iwanna_ster As an aside, if you have more than one type of document in your database, it might be helpful to check out creating secondary indexes. Every document in the bucket is in the primary index but you can create secondary indexes that contain only a subset of documents, which can greatly speed up processing for queries that fundamentally only apply to one type of document.

See CREATE INDEX documentation here:

I know about that but it is not necessary because i have only one type of documents. The thing is that i cannot understand the operators provided by Plan Text in order to compare and discuss the results of different queries.