[GENERAL] Re: PSQL 9.5 select for update locks too many rows when usingnumeric instead of int - Mailing list pgsql-general

Looks to me the first plan was using seq scan not the index b/c the value had to be cast to numeric. In such case index is not used, as expected.

               Filter: ((true_data_id)::numeric = '209390104'::numeric)

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

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)