Skip to main content

One post tagged with "prepared statements"

View All Tags

· 7 min read
Nikolay Samokhvalov

As was discussed in #PostgresMarathon 2-002, for a simple SELECT from a table, at planning time, Postgres locks the table and all of its indexes with AccessShareLock. A simple demo to remind it (let me be a bit weird here and save some bytes when typing SQL):

test=# create table t();
CREATE TABLE
test=# create index on t((1));
CREATE INDEX
test=# create index on t((1));
CREATE INDEX
test=# create index on t((1));
CREATE INDEX
test=# create index on t((1));
CREATE INDEX
test=# create index on t((1));
CREATE INDEX
test=# \d t
                Table "public.t"
 Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
Indexes:
    "t_expr_idx" btree ((1))
    "t_expr_idx1" btree ((1))
    "t_expr_idx2" btree ((1))
    "t_expr_idx3" btree ((1))
    "t_expr_idx4" btree ((1))

test=#
test=# begin; explain select from t;
BEGIN
                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on t  (cost=0.00..39.10 rows=2910 width=0)
(1 row)

test=*# select relation::regclass, mode from pg_locks where pid = pg_backend_pid();
  relation   |      mode
-------------+-----------------
 t_expr_idx2 | AccessShareLock
 pg_locks    | AccessShareLock
 t_expr_idx3 | AccessShareLock
 t_expr_idx4 | AccessShareLock
 t_expr_idx  | AccessShareLock
 t_expr_idx1 | AccessShareLock
 t           | AccessShareLock
             | ExclusiveLock
(8 rows)

test=*#

– indeed, all indexes locked.

Using prepared statements to reduce locking​

To mitigate it, we can just use prepared statements. Let's create one:

prepare test_query (int) as select from t;