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

From: Tony Shelver <tshelver(at)gmail(dot)com>
Date: Mon, 11 Mar 2024 at 12:24
Subject: Re: Performance (and general) considerations between views and
functions
To: Wiwwo Staff <wiwwo(at)wiwwo(dot)com>

On Mon, 11 Mar 2024 at 12:08, Wiwwo Staff <wiwwo(at)wiwwo(dot)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....