Обсуждение: Index ignored on pkid = curval('some_seq'), used with pkid = (selectcurval(''some_seq') )
Index ignored on pkid = curval('some_seq'), used with pkid = (selectcurval(''some_seq') )
От
Achilleas Mantzios
Дата:
On Postgresql 10 and 11 begin; insert into itemshist(id,repdate,systemdate,done,username) VALUES(1454349,current_date,current_timestamp,'t','foo'); dynacom=# explain analyze update itemshist set reason='{foo,bar}' where pkid = currval(('public.itemshist_pkid_seq'::text)::regclass); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Update on itemshist (cost=0.00..412082.16 rows=1 width=167) (actual time=15833.238..15833.238 rows=0 loops=1) -> Seq Scan on itemshist (cost=0.00..412082.16 rows=1 width=167) (actual time=14444.143..15833.210 rows=1 loops=1) Filter: (pkid = currval(('public.itemshist_pkid_seq'::text)::regclass)) Rows Removed by Filter: 14470046 Planning Time: 0.095 ms Trigger for constraint $1: time=0.240 calls=1 Trigger itemshist_dbmirror_trig: time=3.101 calls=1 Execution Time: 15836.636 ms (8 rows) -- but if I compare against select currval it uses the index: dynacom=# explain analyze update itemshist set reason='{foo,bar}' where pkid = ( SELECT currval(('public.itemshist_pkid_seq'::text)::regclass)); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Update on itemshist (cost=0.45..8.47 rows=1 width=167) (actual time=0.048..0.048 rows=0 loops=1) InitPlan 1 (returns $0) -> Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1) -> Index Scan using itemshist_pkey on itemshist (cost=0.43..8.45 rows=1 width=167) (actual time=0.028..0.033 rows=1loops=1) Index Cond: (pkid = $0) Planning Time: 0.095 ms Trigger for constraint $1: time=0.145 calls=1 Trigger itemshist_dbmirror_trig: time=3.026 calls=1 Execution Time: 3.273 ms (9 rows) -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Achilleas Mantzios <achill@matrix.gatewaynet.com> writes: > dynacom=# explain analyze update itemshist set reason='{foo,bar}' where pkid = currval(('public.itemshist_pkid_seq'::text)::regclass); currval() is marked volatile, so that's not a legal index qualification. (Perhaps there's an argument that it'd be more useful to consider it stable, but certainly if you used it in the same query as a nextval() on the same sequence, you'd have trouble.) > -- but if I compare against select currval it uses the index: > dynacom=# explain analyze update itemshist set reason='{foo,bar}' where pkid = ( SELECT currval(('public.itemshist_pkid_seq'::text)::regclass)); Yeah, the planner does not consider uncorrelated scalar sub-selects to be volatile; they'll be evaluated only once per query, regardless of what they contain. So this is sort of a traditional hack for freezing a volatile function's result. (I have no idea whether other RDBMSes read the SQL spec the same way on this point.) regards, tom lane
Re: Index ignored on pkid = curval('some_seq'), used with pkid =(select curval(''some_seq') )
От
Achilleas Mantzios
Дата:
On 27/2/19 5:21 μ.μ., Tom Lane wrote: > Achilleas Mantzios <achill@matrix.gatewaynet.com> writes: >> dynacom=# explain analyze update itemshist set reason='{foo,bar}' where pkid = currval(('public.itemshist_pkid_seq'::text)::regclass); > currval() is marked volatile, so that's not a legal index > qualification. > > (Perhaps there's an argument that it'd be more useful to consider it > stable, but certainly if you used it in the same query as a nextval() > on the same sequence, you'd have trouble.) > >> -- but if I compare against select currval it uses the index: >> dynacom=# explain analyze update itemshist set reason='{foo,bar}' where pkid = ( SELECT currval(('public.itemshist_pkid_seq'::text)::regclass)); > Yeah, the planner does not consider uncorrelated scalar sub-selects > to be volatile; they'll be evaluated only once per query, regardless > of what they contain. So this is sort of a traditional hack for > freezing a volatile function's result. (I have no idea whether other > RDBMSes read the SQL spec the same way on this point.) Thanks Tom. > > regards, tom lane > -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt