Fwd: Performance (and general) considerations between views and functions

Поиск
Список
Период
Сортировка
От Tony Shelver
Тема Fwd: Performance (and general) considerations between views and functions
Дата
Msg-id CAG0dhZDuxPyc8EUSNxRkLcOT6L8i3qXN=eVqFUovSv9CaeZahA@mail.gmail.com
обсуждение исходный текст
Список pgadmin-support
From: Tony Shelver <tshelver@gmail.com>
Date: Mon, 11 Mar 2024 at 12:24
Subject: Re: Performance (and general) considerations between views and functions
To: Wiwwo Staff <wiwwo@wiwwo.com>

On Mon, 11 Mar 2024 at 12:08, Wiwwo Staff <wiwwo@wiwwo.com> wrote:
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!
 

We use functions mostly, rather than views.  But our use case is also quite specific to the kind of processing we are doing.
In our case, we are processing time series and geographical data (vehicle and asset tracking application). 

Looking at commercial and open-source systems in this space, most use the ubiquitous ORM-driven data access methodology.  This leads to 'interesting' solutions when running large reports across longer periods of time.  Usually the application limits the date range, or they pre-cache the reports in to a separate file / table, or they limit the accuracy of the reports.  None of this is ideal, but the alternative is having to return hundreds of thousands of rows to the front end application for processing there.

We use functions to iterate through the database and extract just the data we need in reportable format, pulling just records that suit the criteria.  Along with the use of PostGIS (which can use indexing to find records based on geographical locations within an area, or within specific distances from each other and so on), which also limits the amount of data returned to the application, our custom reporting is much faster and more accurate than that provided by the telematics software packages we also use.

This also allows us to share one heavy-lifting extraction function across multiple reports and also reporting platforms, which reduces complexity and maintenance in the applications.

However, this needs a deep understanding of the data in the system, the application requirements and also a good knowledge of pgsql and function design / development.

May be overkill for most apps....



В списке pgadmin-support по дате отправления:

Предыдущее
От: Shaheed Haque
Дата:
Сообщение: Re: Exception trying to install pgadmin4 version 8.4
Следующее
От: "Nkundiwacu, Benit"
Дата:
Сообщение: RE: [External] Re: PG ADMIN INSTALLATION