Re: PSQL 9.5 select for update locks too many rows when using numeric instead of int

Uhm... maybe I misinterpreted the results.

Looking better, the root cause seems to be that the query planner is not using the index, resorting to a seq scan instead.

OK... that makes more sense.

Sorry for the bogus email.

Igor

From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Sfiligoi, Igor
Sent: Thursday, February 02, 2017 1:22 PM
To: pgsql-general(at)postgresql(dot)org
Subject: -EXT-[GENERAL] PSQL 9.5 select for update locks too many rows when using numeric instead of int

Dear PSQL team.

I just found a weird problem.

When I pass a numeric type to a select for update statement, it locks loads of rows, instead of a single one!
See explains below.

Is this a known bug (in 9.5)?
Any chance it was fixed in a more recent release?

Thanks,
Igor

Note: My table has about 200M rows.
true_data_id is of type bigint.

mcatdb=> PREPARE fooplan3 (NUMERIC) AS SELECT DI.object_status,DR.replica_status,DR.replication_id FROM MCAT_DATA_INFO DI, MCAT_DATA_REPLICA DR WHERE DI.true_data_id = DR.data_id AND DI.true_data_id = $1 FOR UPDATE;

mcatdb=> explain analyze EXECUTE fooplan3(209390104);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
LockRows (cost=0.57..16852579.49 rows=1036721 width=32) (actual time=233942.206..254040.547 rows=1 loops=1)
-> Nested Loop (cost=0.57..16842212.28 rows=1036721 width=32) (actual time=233942.171..254040.505 rows=1 loops=1)
-> Seq Scan on mcat_data_info di (cost=0.00..9867006.22 rows=1037098 width=22) (actual time=233942.109..254040.419 rows=1 loops=1)
Filter: ((true_data_id)::numeric = '209390104'::numeric)
Rows Removed by Filter: 207368796
-> Index Scan using pkey_data_replica on mcat_data_replica dr (cost=0.57..6.72 rows=1 width=26) (actual time=0.047..0.052 rows=1 loops=1)
Index Cond: (data_id = di.true_data_id)
Execution time: 254040.632 ms

mcatdb=> PREPARE fooplan4 (INT) AS SELECT DI.object_status,DR.replica_status,DR.replication_id FROM MCAT_DATA_INFO DI, MCAT_DATA_REPLICA DR WHERE DI.true_data_id = DR.data_id AND DI.true_data_id = $1 FOR UPDATE;
mcatdb=> explain analyze EXECUTE fooplan4(209390104);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
LockRows (cost=1.14..17.20 rows=1 width=32) (actual time=0.307..0.318 rows=1 loops=1)
-> Nested Loop (cost=1.14..17.19 rows=1 width=32) (actual time=0.232..0.243 rows=1 loops=1)
-> Index Scan using idx_0_data_info on mcat_data_info di (cost=0.57..8.59 rows=1 width=22) (actual time=0.193..0.197 rows=1 loops=1)
Index Cond: (true_data_id = 209390104)
-> Index Scan using pkey_data_replica on mcat_data_replica dr (cost=0.57..8.59 rows=1 width=26) (actual time=0.032..0.039 rows=1 loops=1)
Index Cond: (data_id = 209390104)
Execution time: 0.420 ms

mcatdb=> PREPARE fooplan5 (BIGINT) AS SELECT DI.object_status,DR.replica_status,DR.replication_id FROM MCAT_DATA_INFO DI, MCAT_DATA_REPLICA DR WHERE DI.true_data_id = DR.data_id AND DI.true_data_id = $1 FOR UPDATE;
mcatdb=> explain analyze EXECUTE fooplan5(209390104);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
LockRows (cost=1.14..17.20 rows=1 width=32) (actual time=0.316..0.347 rows=1 loops=1)
-> Nested Loop (cost=1.14..17.19 rows=1 width=32) (actual time=0.252..0.283 rows=1 loops=1)
-> Index Scan using idx_0_data_info on mcat_data_info di (cost=0.57..8.59 rows=1 width=22) (actual time=0.042..0.059 rows=1 loops=1)
Index Cond: (true_data_id = '209390104'::bigint)
-> Index Scan using pkey_data_replica on mcat_data_replica dr (cost=0.57..8.59 rows=1 width=26) (actual time=0.199..0.212 rows=1 loops=1)
Index Cond: (data_id = '209390104'::bigint)
Execution time: 0.443 ms
(7 rows)