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!
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 |
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 |