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
