Update statement on PostgreSQL not using primary key index to update

Question

I have a stored procedure on PostgreSQL like this:

create or replace procedure new_emp_sp (f_name varchar, l_name varchar, age integer, threshold integer, dept varchar)
language plpgsql
as $$
declare
    new_emp_count integer;
begin
    INSERT INTO employees (id, first_name, last_name, age)
        VALUES (nextval('emp_id_seq'), 
                random_string(10),
                random_string(20),
                age);
    select count(*) into new_emp_count from employees where age > threshold;
    update dept_employees set emp_count = new_emp_count where id = dept;
end; $$

I have enabled auto_explain module and set log_min_duration to 0 so that it logs everything.

I have an issue with the update statement in the procedure. From the auto_explain logs I see that it is not using the primary key index to update the table:

->  Seq Scan on dept_employees  (cost=0.00..1.05 rows=1 width=14) (actual time=0.005..0.006 rows=1 loops=1)
        Filter: ((id)::text = 'ABC'::text)
        Rows Removed by Filter: 3

This worked as expected until a couple of hours ago and I used to get a log like this:

->  Index Scan using dept_employees_pkey on dept_employees  (cost=0.15..8.17 rows=1 width=48) (actual time=0.010..0.011 rows=1 loops=1)
        Index Cond: ((id)::text = 'ABC'::text)

Without the procedure, if I run the statement standalone like this:

explain analyze update dept_employees set emp_count = 123 where id = 'ABC';

The statement correctly uses the primary key index:

Update on dept_employees  (cost=0.15..8.17 rows=1 width=128) (actual time=0.049..0.049 rows=0 loops=1)
->  Index Scan using dept_employees_pkey on dept_employees  (cost=0.15..8.17 rows=1 width=128) (actual time=0.035..0.036 rows=1 loops=1)
        Index Cond: ((id)::text = 'ABC'::text)

I can't figure out what has gone wrong especially because it worked perfectly just a couple of hours ago.

I don’t understand the question, do you actually have a problem? You complain about the query plan, but does the changed plan have a problem or is it just different? — Frank Heikens
– Frank Heikens, Commented Mar 7, 2022 at 18:38
The Seq Scan is reported on a table with just 4 rows. I doubt using an index scan would make things any faster on such a tiny table — user330315
– user330315, Commented Mar 7, 2022 at 18:42
@a_horse_with_no_name but it uses the index when I run the query manually? By manually I mean without calling the stored procedure — user9492428
– user9492428, Commented Mar 7, 2022 at 19:00
There can be a difference in planning when using stored procedures/functions. In your case you're updating an almost empty table within a fraction of a millisecond no matter what query plan is used. — Frank Heikens
– Frank Heikens, Commented Mar 7, 2022 at 19:10

Schwern · Accepted Answer · 2022-03-07 21:34:36Z

1

It is faster to scan N rows sequentially than to scan N rows using an index. So for small tables Postgres may decide that a sequence scan is faster than an index scan.

PL/pgSQL can cache prepared statements and execution plans, so you're probably getting a cached execution plan from when the table was smaller.

answered Mar 7, 2022 at 21:34

Schwern

167k28 gold badges225 silver badges370 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update statement on PostgreSQL not using primary key index to update

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related