Filtering over database views is much slower than direct query

Question

I noticed a big difference in query plan when doing regular query versus creating database view and then querying the view.

case 1 basic query:

SELECT <somequery> WHERE <some-filter> <some-group-by>

case 2 database view:

CREATE VIEW myview AS SELECT <some-query> <some-group-by>;
SELECT FROM myview WHERE <some-filter>;

I have noticed that in the case 2 postgres will join/aggregate everything possible, and only then it applies the filter. In case 1 it doesn't touch rows filtered out with where clause. So case 2 is a lot slower.

Are there any tricks to work around this while keeping the database view?

Answer 1

Your View has to re-create the dataset to filter from every time you perform the SELECT FROM.

The easiest way is to change the view to a materialized view. If your data is not changing every 2 minutes, a materialized view will save the select to be used, where your filter can then work on the "saved" dataset. The second thing you can do is add Indexes on the View.

Example Here: https://hashrocket.com/blog/posts/materialized-view-strategies-using-postgresql

create materialized view matview.account_balances as
select
  name,
  coalesce(
    sum(amount) filter (where post_time <= current_timestamp),
    0
  ) as balance
from accounts
  left join transactions using(name)
group by name;

create index on matview.account_balances (name);
create index on matview.account_balances (balance);

This is the simplest way to reduce the runtime of your query. Hope this helps.