I have a query like this:
select * from slow_view where id = 1 union all .... union all select * from slow_view where id = 1000 -- 50 ids, exec time: 10.615 sec,
This would run in a very acceptable time if it was replaced with:
select * from slow_view where id in (1, ...., 1000) -- 50 ids, exec time: 1.3 sec,
with slow_view_cached as ( select * from slow_view) select * from slow_view_cached where id = 1 union all .... union all select * from slow_view_cached where id = 1000 -- 50 ids, exec time: 1.83 sec,
But unfortunately it is generated by an ORM and I don't have a chance to modify it. It's quite bad as an ORM, I don't think I can do much to improve its behavior.
Is there something I can do to my query or to my view to make it "cached" for all the subqueries in this case? I know I could use a materialized view but it doesn't fit my case very well.
AFAIK in Postgres it's not possible to define indexes on a view, so
id is not indexed or unique.
Is there anything else I can try?
I'm using Postgresql 9.5 (but if you have a solution for PG 9.6 or even PG 10 it would be acceptable, since I plan to update very soon).
The code is a bit too complex to post here. In essence this query is generated in the following case. slow_view is an association of a Model m. What I do is basically
m.find().populate(slow_view, criteria); The documentation is here.
criteria is excluded from the query for the sake of simplicity, id is the foreign key from _slow_view_ to Model.
I could call a stored procedure from this ORM, but it would mean altering the application a bit too much.