sql - Bad SQLite query performance with outer joins -

i have sqlite database part of ios app works fine part small changes query can result in taking 1000x longer complete. here's 2 tables have involved:

create table "journey_item" ("id" serial not null primary key, "position" integer not null, "last_update" bigint not null, "rank" double precision not null, "skipped" boolean not null, "item_id" integer not null, "journey_id" integer not null);  create table "content_items" ("id" serial not null primary key, "full_id" varchar(32) not null, "title" varchar(508), "timestamp" bigint not null, "item_size" integer not null, "http_link" varchar(254), "local_url" varchar(254), "creator_id" integer not null, "from_id" integer,"location_id" integer); 

tables have indexes on primary , foreign keys.

and here 2 queries give example of problem

select * content_items ci  inner join journey_item ji on ji.item_id = ci.id ji.journey_id = 1  select * content_items ci  left outer join journey_item ji on ji.item_id = ci.id ji.journey_id = 1 

the first query takes 167 ms complete while second takes 3.5 minutes , don't know why outer join make such huge difference.

edit: without part second query takes 267 ms

the 2 queries should have same result set (the where clause turns left join inner join)`. however, sqlite doesn't recognize this.

if have index on journey_item(journey_id, item_id), used inner join version. however, second version scanning first table join. index on journey_item(item_id) help, still not match performance of first query.