Performance (and general) considerations between views and functions

From: Wiwwo Staff <wiwwo(at)wiwwo(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Performance (and general) considerations between views and functions
Date: 2024-03-11 10:07:35
Message-ID: CAD1W9HXCGchhJNcNKGzW4N7an4dzOcfXLO4Q3DF1mCX0KN_Log@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-support pgsql-general

Hi Community!
I am in the process of re-writing SQLs in app code to a more DB side
approach.

My first idea was to write views, and let the app query those views; i'd
need to rewrite some queries to expose the "parameters" (where condition
fields) of those queries in the select part, to expose them and allow app
to filter on them. Now a huge issue.

But, i might also create functions (language sql) returning "RETURNS
TABLE", and pass the where conditions as parameter; i would have to bite
the bullet and write all the "RETURNS TABLE (... ... ...)" definitions, but
meh. :-D

My question would be: in terms of performance, the select on the function
looks way faster, but I am afraid PG optimized does not really
"understands" the complexity of the query.
For example, the explain on the view understands that the query returns
always 1 row; the explain on the proc. does not.
On the other side, functions can be tuned with many many "hints" (COST,
ROWS, etc...)

In my shoes, what would you do?

Thanks a ton!

Responses

Browse pgadmin-support by date

  From Date Subject
Next Message Tony Shelver 2024-03-11 10:27:18 Fwd: Performance (and general) considerations between views and functions
Previous Message Shaheed Haque 2024-03-11 09:24:41 Re: Exception trying to install pgadmin4 version 8.4

Browse pgsql-general by date

  From Date Subject
Next Message Nick Renders 2024-03-11 10:11:09 Re: could not open file "global/pg_filenode.map": Operation not permitted
Previous Message Jan Kohnert 2024-03-11 09:16:33 Implementing product-aggregate