Query too slow: does full scan when using subquery

Hi all!

I have a query that looks like this:

`select * from records where records."ownerId" = '9feadd5b-e70f-457d-b4d9-7d00fcf8d026';`

It runs fine: executes in a few milliseconds and uses the index I created on the ownerId column.
However if I do

select * from records where records."ownerId" = (SELECT id FROM users WHERE uid = 'neo');

It takes more than two seconds to finish. EXPLAIN gives

"",distributed,true "",vectorized,false root,"","" ├── scan,"","" │,table,records@primary │,spans,FULL SCAN │,filter,"""ownerId"" = @S1" └── subquery,"","" │,id,@S1 │,original sql,(SELECT id FROM users WHERE uid = 'neo') │,exec mode,one row └── render,"","" └── scan,"","" "",table,users@users_uid_key "",spans,"/""neo""-/""neo""/PrefixEnd"

As you can see, it does a FULL SCAN on the records table which contains 170k rows without using the index I have on the ownerId colume. (It does use the index on users.uid though)

The same query runs fine on PostgreSQL so I would expect that CockroachDB executes this query fast. Are there any workarounds that can improve the performance in this query?

Hi @neo, thanks for the report. This is a known issue, unfortunately: https://github.com/cockroachdb/cockroach/issues/23264

I cross linked this post to the issue. As a workaround, you can rewrite the query yourself as a join:

SELECT records.*
JOIN users ON records."ownerID" = users.id
WHERE users.uid='neo'