1

On Postgrsql 13.5 - I have a SELECT statement with a stored procedure as condition on the PK column (id). This turns out to be very slow:

select * from xyz where id=myfunction('static-parameter') -- very slow

But breaking it up into two statements it's very fast. e.g.

select myfunction('static-parameter') -- Very fast
==> 123
select * from xyz where id=123;     -- very fast

I can only assume that postgres is running the function for each row. But why? And how to avoid this? Here's my real case with explain analyze.

# explain analyze select * from users where id = find_user_by_ip('10.255.0.3');
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Seq Scan on users  (cost=0.00..163.66 rows=1 width=113) (actual time=3214.990..4868.484 rows=1 loops=1)
   Filter: (id = find_user_by_ip('10.255.0.3'::character varying))
   Rows Removed by Filter: 592
 Planning Time: 0.074 ms
 Execution Time: 4868.502 ms
(5 rows)


# explain analyze select find_user_by_ip('10.255.0.3');
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Result  (cost=0.00..0.26 rows=1 width=4) (actual time=29.522..29.522 rows=1 loops=1)
 Planning Time: 0.059 ms
 Execution Time: 31.159 ms
(3 rows)

# explain analyze select * from users where id = 487;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using users_id_key on users  (cost=0.28..8.29 rows=1 width=113) (actual time=0.009..0.011 rows=1 loops=1)
   Index Cond: (id = 487)
 Planning Time: 0.299 ms
 Execution Time: 0.031 ms
(4 rows)
1

2 Answers 2

3

Your function (not stored procedure, which is a different thing in PostgreSQL) is probably marked as volatile. You should probably change the function so that it is instead marked as stable or immutable (assuming of course that one of those things is true of it) and then it should use the index fine.

1
  • Thanks it! I ran ALTER FUNCTION find_user_by_ip STABLE and now it's fast as expected. Thanks!!
    – bboness
    Commented Dec 3, 2023 at 11:33
1

Use this:

WITH cte AS
(
  SELECT myfunction('static-parameter') AS x
)
SELECT * FROM xyz WHERE id = (SELECT x FROM cte);

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.