Re: Query planner not using indexes with JOIN query and OR clause

>
> Try refactoring to:
>
> select ai.position, i.filename as image_filename, p.filename as
> panorama_filename
> from album_items ai
> left join image2 i on i.imageid = ai.image_id
> left join panoramas p on p.id = ai.panorama_id
> where i.filename in ('pano360--v471.jpg', 'pano360-2--v474.jpg')
> union all select ai.position, i.filename as image_filename, p.filename
> as panorama_filename
> from album_items ai
> left join image2 i on i.imageid = ai.image_id
> left join panoramas p on p.id = ai.panorama_id
> where p.filename in ('pano360--v471', 'pano360-2--v474')
>
> ...and see if that helps. Dealing with 'or' conditions is a general
> weakness of the planner that has gotten better over time but in some
> cases you have to boil it to 'union all'.
>

Yes, this definitely helps and the query performance goes back to normal,
thanks. It makes the code a bit more complicated, so not ideal, but
definitely works!

Thanks for the help. I don't how much you know about PostgreSQL internals
(I don't!), but what optimization would need to be in place for PostgreSQL
to be smarter about this query?

-Ben