|
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 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) |
