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.sfili...@ga.com]
Sent: Thursday, February 02, 2017 4:29 PM
To: pgsql-general@postgresql.org
Subject: 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-ow...@postgresql.org<mailto:pgsql-general-ow...@postgresql.org>
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Sfiligoi, Igor
Sent: Thursday, February 02, 2017 1:22 PM
To: pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.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)
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.
