1

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.

1

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.

| improve this answer | |
  • This is always an option of course. Just a bit messy from an architectural point of view. – heapOverflow Sep 27 '17 at 7:29
0

Community wiki answer:

Postgres will perform a step called view-resolution before executing the queries that contain a view name instead of a table name. This step will copy the select statement definition of the view into your executed select statement. If you have an index on your ID column in the source table, this index will be used. Otherwise if ID is a stand-in for multiple columns from multiple source tables, you can create a materialized view with an appropriate index on it.

If ID is a single column in the source table it is unlikely that a materialized view will yield a speed-up of your query, because the first and second queries are structurally different. In the first case Postgres makes 1000 (if your example actually had that many different ID values) single queries and concatenates all results in a single result cursor. In the second query Postgres is able to see the big picture and can better choose a strategy for selecting from an index (e.g. a range scan).

-1

You say you're using m.find().populate(slow_view, id: ids); It sounds to me like this is not what you want, but instead, from the docs you linked,

m.find().populate(
  slow_view,
  where: {
    id: [1,2,3]
  }
);
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.