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?
