Are simple nested views efficient (provided no superfluous tables included?)

Question

I've been reading carefully links such as the below two:

Will Postgres push down a WHERE clause into a VIEW with a Window Function (Aggregate)?

https://dba.stackexchange.com/questions/151169/are-views-harmful-for-performance-in-postgresql

There are comments made in the above links that suggest answers to two related questions I have, but I'd like to make sure I understand.

Suppose I have a view:

create view A as
select
  x.xKey,
  x.y,
  x.z,
  y.yKey,
  y.a,
  y.b
from x
join y
  on x.xKey = y.xKey

And now I have another...

create view B as
select
  A.xKey,
  A.y,
  A.z,
  A.yKey,
  A.a,
  A.b,
  r.rKey,
  r.n
from A
join r
  on A.yKey = r.yKey

Assume a third view C that does more of the same, but all three views being plain select statements.

Two questions:

If I select from view C using filters that relate to any/all of the tables involved, are "predicates" always "pushed down" (new phrase for me, hoping I said this properly) so that view C is as efficient filter-wise as would be a larger stand-alone query built the same way?
If I select from view C but I do not utilize all tables involved in all of the joins, that is where I do pay a price that could be avoided by a hand-crafted select statement that joined fewer tables. Yes?

Thanks much in advance for thoughts.

Check the [execution plan](https://www.postgresql.org/docs/current/static/using-explain.html) — , Aug 01 '20 at 07:16
@Wellspring . . . I don't think any database will generally optimize away joins between tables just because a table is not being used. There might be some edge cases -- outer joins on primary keys -- but even so, it would surprise me. — Gordon Linoff, Aug 01 '20 at 12:11
@GordonLinoff I think you're saying that a price will very likely be paid in scenario from my 2nd question? — Wellspring, Aug 01 '20 at 15:32
@a_horse_with_no_name I don't trust myself entirely with analyzing execution plans, and thus my question, but this is good guidance. That said, I couldn't tell if you meant that the answer "depends" or is the same for all such situations. — Wellspring, Aug 01 '20 at 15:58

Wellspring · Accepted Answer · 2020-08-08T14:45:05.630

Experts are welcome to correct me on this.

I'm no master at reading EXPLAIN statements, but I can cut and paste SQL just fine and I can read a stopwatch reasonably well.

I am here to say.

Embedded views, even simple ones, can introduce some SERIOUS pain to execution speeds.

I just answered my own question by working with a view G that is (or was) something akin to (using my A, B, C analogy as posed in the OP), selecting from view F which uses E... down to A.

Selecting results for a single filtered row was taking too long--in the order of 500ms.

I reduced that down to 200ms by reducing extra tables captured along the way. No shock that I gained something there. A bit of a shock to see how much.

But then I took that 200ms down to 7ms by replacing view G's use of view B and instead using the construction of B as part of view G. It makes view G substantially more complicated to read, but staggeringly more efficient.

So much for abstracting away the common joins.

So that answers my first question with a resounding NO! In PostgreSQL, a view that uses nested views can be shockingly LESS efficient than a logically equivalent view that uses no nesting at all.

My takeaway: if performance is suffering, do NOT assume that your nested views are innocent. They can VERY likely be hurting you.

Badly.

PS Am starting to understand how to read the EXPLAIN results. The kicker is that the nested view is explained with this...

->  Hash Join  (cost=20902.62..28364.33 rows=1 width=1018)
      (actual time=878.007..1092.339 rows=0 loops=3)
        Hash Cond: (mi.mft_item_id = ri.mft_item_id)

whereas my view that eschewed usage of a nested view pulled off this...

->  Index Scan using mft_items_pkey on mft_items mi
      (cost=0.42..8.44 rows=1 width=187)
      (actual time=0.024..0.024 rows=1 loops=1)
        Index Cond: (mft_item_id = ri.mft_item_id)

PostreSQL did not use desirable indexes when I joined two views together.

Are simple nested views efficient (provided no superfluous tables included?)

1 Answers1