Observing this performance issue that can't be explained with
EXPLAIN in a PostgreSQL 9.6 database.
There is currently a table
users, which has a cast on it to output a record to
SELECT u::jsonb FROM users u;).
The database has grown in complexity and schemas, which a new schema (think module) has been added that adds a few things to the user accounts (via joins in the schema table).
To make the cast to
JSONb consistant across the app, the
users cast was refashioned to use a cast in a new schema called
catalogue. This way casting
users will get additional properties from the
catalogue schema in a transparent way to the user. This was done so that the API Server doesn't need to know to use this/that schema, and I can leave my (reused) User API Controller the same.
The new [refashioned] cast returns the cast from
users, so basically the
public's cast function on
users call a [SQL] function with the simple SQL command..
CREATE OR REPLACE FUNCTION catalogue.cast_user_jsonb_override(public.user) RETURNS JSONb STABLE LANGUAGE sql AS $$ SELECT u::jsonb FROM catalogue.users u WHERE username = $1.username $$;
This way the casting
JSONb will utilise the above cast without the user knowing.
However, for some reason calling the cast for
users is slow, while calling the cast for
users is fast.
EXPLAIN ANALYSE VERBOSE SELECT u::jsonb FROM catalogue.users u; .... Planning time: 0.046 ms Execution time: 40.086 ms
EXPLAIN ANALYSE VERBOSE SELECT u::jsonb FROM catalogue.users u; .... Planning time: 0.094 ms Execution time: 1384.414 ms
EXPLAIN ANALYSE VERBOSE SELECT catalogue.cast_users_jsonb_override(u) FROM public.users u; .... Planning time: 0.101 ms Execution time: 1325.343 ms
If anything, the catalogue cast should be the slower one as is performs joins and the likes.
The [override] cast function is
STABLE to help with performance, but it doesn't seem to do much.
Any ideas as to why calling a function that executes a basic
SELECT is slow, while calling the SQL itself is fast? . I haven't tested it in any other postgresql version, and there isn't any priority to do an upgrade to v10 to test as there is currently a deadline on the release.