Thread: views: performance implication

We hope to use views as a way to give customers odbc based ad-hoc query
access to our database while enforcing security. The reason is that we do
not want to put data into separate tables by customer, but rather use a
customer ID as part of any query criteria on any table.

So the question is: are there any negative performance implications of
doing so (other than the obvious of having more data in a table than is of
interest to the querying customer)? Back in the old days, views were a
performance no-no in Informix, so I want to be sure we aren't setting a big
trap for ourselves.

Thanks in advance.

John Moore wrote:
> We hope to use views as a way to give customers odbc based ad-hoc query
> access to our database while enforcing security. The reason is that we do
> not want to put data into separate tables by customer, but rather use a
> customer ID as part of any query criteria on any table.
>
> So the question is: are there any negative performance implications of
> doing so (other than the obvious of having more data in a table than is of
> interest to the querying customer)? Back in the old days, views were a
> performance no-no in Informix, so I want to be sure we aren't setting a big
> trap for ourselves.

Views are rewritten as rules for every query issued, so there is little
performance impact except for the complexity of your view (GROUP BY,
aggregates, etc.)

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026