Why wont postgresql use index for the query?

Question

I have this small query

select t.* from table1 t
left join table2 t2 on t2.related_uuid = t.uuid and t2.inventoried is true
where t2.org like 'org%' or t.org like 'org%'

Both table1 and table2 have few thousand rows (< 5000). But this query takes ~6 seconds to run. Given that table2.inventoried is true is common in many queries, i thought maybe i should add a partial index over inventoried column with condition inventoried is true could improve performance. Table2 is 90% inventoried is false and 10% inventoried is true. But when looking at explain it does not use the index.

Additionally Table2 contains a column type which can have values: table1, table3, table4. So i could also go with index over it and add an additional condition to the join where t2.type = 'table1'. But this also goes unused.

Index creation queries

create index idx on table2(inventoried) where inventoried is true
create index idx2 on table2(type) where type = 'table1' 

I think im doing something wrong, but i just cant quite put my finger on it.

EDIT: Execution plan without indexes

"Hash Right Join  (cost=227.83..361.03 rows=3024 width=331) (actual time=0.912..2.242 rows=982 loops=1)"
"  Hash Cond: (t2.related_uuid = t.uuid)"
"  Filter: ((t2.org ~ 'St%'::text) OR (t.org ~ 'St%'::text))"
"  Rows Removed by Filter: 2168"
"  Buffers: shared hit=274"
"  ->  Seq Scan on table2 t2  (cost=0.00..129.85 rows=1276 width=58) (actual time=0.007..0.300 rows=1265 loops=1)"
"        Filter: (inventoried IS TRUE)"
"        Rows Removed by Filter: 11"
"        Buffers: shared hit=117"
"  ->  Hash  (cost=188.48..188.48 rows=3148 width=331) (actual time=0.893..0.893 rows=3150 loops=1)"
"        Buckets: 4096  Batches: 1  Memory Usage: 1104kB"
"        Buffers: shared hit=157"
"        ->  Seq Scan on table1 t  (cost=0.00..188.48 rows=3148 width=331) (actual time=0.004..0.423 rows=3150 loops=1)"
"              Buffers: shared hit=157"
"Planning Time: 0.153 ms"
"Execution Time: 2.315 ms"

Answer 1

It is going to be hard to get a usable index for an OR which draws from two different tables. You could instead write it as a union:

select t.* from table1 t where t.org like 'org%'
union [all?]
select t.* from table1 t join table2 t2 on t2.related_uuid = t.uuid where  t2.inventoried is true and t2.org like 'org%'

But, will this deal with duplicate rows in a manner you find acceptable? Only you can answer that.