|
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) Thanks, Patricia From: Sfiligoi, Igor [mailto:Igor.Sfiligoi@xxxxxx]
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)
Confidentiality Notice:: This email, including attachments, may include non-public, proprietary, confidential or legally privileged information. If you are not an intended recipient or an authorized agent of an intended recipient, you are hereby notified that any dissemination, distribution or copying of the information contained in or transmitted with this e-mail is unauthorized and strictly prohibited. If you have received this email in error, please notify the sender by replying to this message and permanently delete this e-mail, its attachments, and any copies of it immediately. You should not retain, copy or use this e-mail or any attachment for any purpose, nor disclose all or any part of the contents to any other person. Thank you.
|
