0

I am trying to squeeze out the all the performance from my PostgreSQL database, also I want to abstract my query definitions from my application layer.

To do that, I am using table-valued function. However, I noticed that functions are nonperforming worse than raw queries.

My table definition is

CREATE TABLE public.my_entity
(
    id BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,
    id_col_1 BIGINT NOT NULL,
    id_col_2 BIGINT NOT NULL,
    text_column TEXT NOT NULL,
    int_type_col INT NOT NULL,
    int_type_col_2 INT NOT NULL,
    create_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL,
    update_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL,
    CONSTRAINT pk_my_entity PRIMARY KEY (id),
    CONSTRAINT fk_my_entity_id_col_1_my_entity_2 FOREIGN KEY (id_col_1) REFERENCES public.my_entity_2(id),
    CONSTRAINT fk_my_entity_id_col_2_my_entity_3 FOREIGN KEY (id_col_2) REFERENCES public."my_entity_3"(id)
);

CREATE INDEX ix_my_entity_id_col_1_create_timestamp ON my_entity
(
    id_col_1 ASC,
    create_timestamp ASC
);

CREATE INDEX ix_my_entity_id_col_2_create_timestamp ON my_entity
(
    id_col_2 ASC,
    create_timestamp ASC
);

My function definition is:

CREATE OR REPLACE FUNCTION public.udf_my_entity_get_by_id
(
    p_id BIGINT
)
RETURNS TABLE
(
    id BIGINT
    ,id_col_1 BIGINT
    ,id_col_2 BIGINT
    ,text_column TEXT
    ,create_timestamp TIMESTAMP(6) WITH TIME ZONE
    ,update_timestamp TIMESTAMP(6) WITH TIME ZONE
)
LANGUAGE sql
AS $func$
SELECT
    e.id
    ,e.id_col_1
    ,e.id_col_2
    ,e.text_column
    ,e.create_timestamp
    ,e.update_timestamp
FROM public.my_entity AS e
WHERE e.id = p_id
$func$ STABLE;

How I call my function:

SELECT * FROM udf_my_entity_get_by_id(13642);

Explain Analyse:

Index Scan using pk_my_entity on my_entity e  (cost=0.29..2.50 rows=1 width=289) (actual time=0.029..0.032 rows=1 loops=1)
  Index Cond: (id = '13642'::bigint)
Planning Time: 0.237 ms
Execution Time: 0.059 ms

My raw query:

SELECT e.id, e.id_col_1, e.id_col_2, e.text_column, e.create_timestamp, e.update_timestamp FROM public.my_entity AS e WHERE e.id = 13642;

Explain Analyse:

SELECT e.id, e.id_col_1, e.id_col_2, e.text_column, e.create_timestamp, e.update_timestamp FROM public.my_entity AS e WHERE e.id = 13642;

Index Scan using pk_my_entity on my_entity e  (cost=0.29..2.50 rows=1 width=289) (actual time=0.030..0.033 rows=1 loops=1)
  Index Cond: (id = 13642)
Planning Time: 0.112 ms
Execution Time: 0.063 ms

My question is: does planning time affect my application performance? If table-valued functions are cached, why is planning time much higher for functions?

Thank you for your help.

2 Answers 2

1

Table valued SQL functions are not cached in any way, so from a performance perspective, this is a net loss.

There are two things you can consider:

  1. writing a PL/pgSQL function, which will use a generic plan from the sixth execution on:

    CREATE FUNCTION public.udf_entry_get_by_id (p_id BIGINT) RETURNS TABLE (...)
       LANGUAGE plpgsql STABLE AS
    'BEGIN
       RETURN QUERY
          SELECT e.id,
                 e.topic_id,
                 e.user_id,
                 e.content,
                 e.create_timestamp,
                 e.update_timestamp
          FROM public.entry AS e
          WHERE e.id = p_id;
    END;';
    
  2. writing an SQL function with the standard confirming syntax, which will not cache the query plan, but will save the effort of parsing the query:

    CREATE FUNCTION public.udf_entry_get_by_id (p_id BIGINT) RETURNS TABLE (...)
       STABLE
    BEGIN ATOMIC
       SELECT e.id,
              e.topic_id,
              e.user_id,
              e.content,
              e.create_timestamp,
              e.update_timestamp
       FROM public.entry AS e
       WHERE e.id = p_id;
    END;
    
1
  • 1
    You should benchmark it. I expect that there is only a small difference between the PL/pgSQL function and a prepared statement, but saving the overhead of a function call might offer a measurable benefit. Note that you need a realistic amount of data in the tables to get reliable numbers. Commented Mar 27 at 12:06
0

The difference between your two EXPLAIN ANALYZE's is only 121 microseconds, which is extremely tiny.

EXPLAIN ANALYZE has some overhead, and on such fast queries, other overhead like network and parsing becomes significant also. It only runs the query once, which means effect from other stuff the machine is doing at the same time can be significant, so there will be some variance, which could be a lot more than 121µs. Basically, with such short timings, don't use just one try with EXPLAIN ANALYZE to make optimization decisions.

To benchmark these queries, it is more accurate to run them from your application, many times, averaging the time, which gives a more relevant answer.

That said, if 121µs matters for your performance, then that either means...

  • You're executing the query a zillion times in a loop

That's a Bad Idea: you will get much better performance by gathering a list of ids of the rows you want to grab, and getting them all in one query using IN(), or unnest(), or a JOIN with VALUES()... or moving the whole thing into a JOIN.

If you use an ORM and you select a list of rows, sometimes you will catch it red handed doing one query per row to fetch relations with the rows that were selected. If the ORM is not dumb it should be able to be configured to do it the right way as explained in the above paragraph. If it can't avoid doing this, it is not suitable for use.

  • You're executing the query a zillion times but it's not in a loop

For example you have a server handling lots of requests and each needs to do one of these queries. In this case you can't use the above trick. But if you use connection pooling, you can use persistent prepared statements or plpgsql functions. You should check that the pool doesn't reinitialize the database session with DISCARD ALL between each use, because that deletes all cached plans.

2
  • Benchmark it from the app. But remember premature optimization is evil. Why are you counting microseconds? Usually this indicates another problem. Are you running this query in a loop?
    – bobflux
    Commented Mar 27 at 12:06
  • Then you should benchmark and optimize the big slow queries, those really matter. The fast queries like this example most likely won't be your bottleneck.
    – bobflux
    Commented Mar 27 at 14:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.