Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Sfiligoi, Igor
Sent: Thursday, February 02, 2017 1:22 PM
To: pgsql-general@xxxxxxxxxxxxxx
Subject: -EXT- 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)

 

 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux