Indexes for columns

CrateDB
1

Hi all,

I understand that with a column with INDEX OFF that it is a result set only, but surely if you have a query that uses indexed columns AND a column that is not indexed, it would use the indexes first and then scan the result set to apply the non indexed query parameter ?

I’ve tried this but it does not seem to work, even with a CTE. It just complains about the non indexed column.

Many thanks
David.

2

I assume you are referring to something like this?

CREATE TABLE a (a TEXT INDEX OFF, b TEXT);

SELECT * FROM a
WHERE a = 'Hello' AND b = 'World';
--SQLParseException[Cannot search on field [a] since it is not indexed.]

a workaround might be something like this:

SELECT * FROM a
WHERE ''||a = 'Hello' AND b = 'World';

or

SELECT * FROM a
WHERE a||null = 'Hello' AND b = 'World';

but I also need to check if there might be a better option

1 Like
3

Not helpful now, but still worth mentioning: we’ve fixed that behaviour with Allow columns without doc-values or unindexed to be queryable by jeeminso · Pull Request #14856 · crate/crate · GitHub and such allow queries against non-indexed columns in future releases (or using the current nightly) in the way you described it (first use indices then apply a generic filter on the result set).

1 Like
4

Thank you, this is great.

In my case I’m just querying on double precision fields, so I used
AND 0||f = 49.9423

as a similar method for numeric fields.