Postgresql – How to optimize a query where a IN clause is replaced with multiples UNION ALL

ormperformancepostgresqlpostgresql-9.5query-performanceview

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,

or

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.

Best Answer

if slow_view don't have too many records, you can consider fetching all the records to application and apply the filter in the application side result set. This approach helped me in a specific case to reduce the database load.

Related Question