No index available for ANSI join term

SQL++
1 
SELECT *
FROM bucket parent
LEFT JOIN bucket reply
ON parent.quoted_id == reply.id 
WHERE parent.obj_type == 0
AND parent.sender_jid == 'noobmaster69'
AND (parent.quoted_id == reply.id OR parent.quoted_id == '')
AND parent.timestamp > 1571180243
AND parent.timestamp < 1572148029;

i create index for almost every key, but i still got :

No index available for ANSI join term reply

2

it’s working when i add

AND reply.obj_type == 0

but it’s mean, i only get data if the parent has a reply

3

create index ix1 on bucket(id);

Following predicate will not make it left outer join

AND (parent.quoted_id == reply.id OR parent.quoted_id == ‘’)

1 Like
4

so i need to create index for id from reply.id without any WHERE, thankyou <3