Re: final patch - plpgsql: for-in-array

2010/11/18 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> 2010/11/18 Alvaro Herrera <alvherre(at)commandprompt(dot)com>:
>>> I fail to see how this supports the FOR-IN-array development though.  It
>>> will just be another unused construct for most people, no?
>
>> maybe I don't understand well, but patch FOR-IN-ARRAY has a documentation
>
> UNNEST is documented too.  Adding still more features doesn't really
> improve matters for people who haven't memorized the documentation;
> it only makes it even harder for them to find out what they should be
> using.  (More features != better)
>

yes, but less user feature doesn't mean less code. Mainly in little
bit specific environment like plpgsql.

> To my mind, the complaint about subscripting being slow suggests that we
> ought to fix subscripting, not introduce a nonstandard feature that will
> make certain use-cases faster if people rewrite their code to use it.
>
> I think it would probably not be terribly hard to arrange forcible
> detoasting of an array variable's value the first time it gets
> subscripted, for instance.  Of course that only fixes some use-cases;
> but it would help, and it helps without requiring people to change their
> code.
>

This is just one half of problem and isn't simple. Second half is
"array_seek" - So any access with subscripts means seq reading of
array's data. Please, look on this part. I am thinking, so this is
more important, than anything what we discussed before. For fast
access there is necessary to call a deconstruct_array function. Then
you can access to subscripts quickly. Actually we have not a control
for access to items in array, when subscript is used in expression
(inside PL). So it is very difficult to accelerate speed in area -
probably it means a subscript expr should be evaluated individually.

A deconstruct_area is relative expensive function, so you have to have
a information about a using of array. Without it, and for smaller
arrays, the optimization can be bad. There isn't any backend
infrastructure for this decision now.

I did a profiling

first example: FOR IN ARRAY

samples % symbol name
336 20.6642 exec_eval_expr
269 16.5437 plpgsql_param_fetch
229 14.0836 exec_stmts
225 13.8376 exec_eval_datum
118 7.2571 exec_assign_value
91 5.5966 exec_eval_cleanup.clone.10
88 5.4121 setup_param_list
72 4.4280 __i686.get_pc_thunk.bx
65 3.9975 exec_eval_boolean
47 2.8905 exec_simple_cast_value
43 2.6445 free_var.clone.2
28 1.7220 exec_cast_value

samples % image name symbol name
1064 16.1188 postgres pglz_decompress
410 6.2112 postgres AllocSetAlloc
353 5.3477 postgres MemoryContextAllocZero
293 4.4387 postgres GetSnapshotData
290 4.3933 postgres AllocSetFree
281 4.2569 postgres ExecEvalParamExtern
223 3.3783 postgres ExecMakeFunctionResultNoSets
220 3.3328 postgres AllocSetReset
212 3.2116 postgres UTF8_MatchText
210 3.1813 postgres LWLockAcquire
195 2.9541 postgres AllocSetCheck
195 2.9541 postgres LWLockRelease
172 2.6057 postgres pfree
163 2.4693 postgres CopySnapshot
162 2.4542 postgres list_member_ptr
144 2.1815 postgres RevalidateCachedPlan
133 2.0148 postgres PushActiveSnapshot
121 1.8331 postgres PopActiveSnapshot
121 1.8331 postgres bms_is_member
118 1.7876 postgres MemoryContextAlloc
108 1.6361 postgres textlike
105 1.5907 postgres AcquireExecutorLocks
79 1.1968 postgres TransactionIdPrecedes
76 1.1513 postgres pgstat_end_function_usage
75 1.1362 postgres pgstat_init_function_usage
72 1.0907 postgres check_list_invariants

sample01 - FOR i IN array_lowe()..array_upper() for t1000

Profiling through timer interrupt
samples % symbol name
1039 29.4084 exec_stmts
723 20.4642 exec_eval_expr
587 16.6148 exec_eval_datum
408 11.5483 plpgsql_param_fetch
176 4.9816 exec_eval_cleanup.clone.10
167 4.7269 setup_param_list
159 4.5004 exec_eval_boolean
128 3.6230 __i686.get_pc_thunk.bx
66 1.8681 exec_simple_cast_value

samples % image name symbol name
312604 84.1141 postgres pglz_decompress
4800 1.2916 postgres hash_search_with_hash_value
4799 1.2913 postgres array_seek.clone.0
2935 0.7897 postgres LWLockAcquire
2399 0.6455 postgres _bt_compare
2219 0.5971 postgres LWLockRelease
1899 0.5110 postgres index_getnext
1374 0.3697 postgres hash_any
1257 0.3382 postgres LockAcquireExtended
1231 0.3312 postgres _bt_checkkeys
1208 0.3250 postgres AllocSetAlloc
1158 0.3116 postgres FunctionCall2
1102 0.2965 postgres toast_fetch_datum

same for t100

samples % symbol name
108 20.6107 exec_eval_expr
96 18.3206 plpgsql_param_fetch
92 17.5573 exec_eval_datum
66 12.5954 exec_stmts
43 8.2061 setup_param_list
38 7.2519 __i686.get_pc_thunk.bx
34 6.4885 exec_eval_cleanup.clone.10
16 3.0534 exec_simple_cast_value
12 2.2901 exec_eval_boolean

samples % image name symbol name
511 20.4646 postgres array_seek.clone.0
163 6.5278 postgres ExecEvalParamExtern
131 5.2463 postgres AllocSetAlloc
127 5.0861 postgres MemoryContextAllocZero
113 4.5254 postgres list_member_ptr
103 4.1249 postgres GetSnapshotData
95 3.8046 postgres AllocSetFree
92 3.6844 postgres LWLockAcquire
80 3.2038 postgres ExecMakeFunctionResultNoSets
74 2.9636 postgres UTF8_MatchText
70 2.8034 postgres LWLockRelease
57 2.2827 postgres ExecEvalArrayRef
57 2.2827 postgres RevalidateCachedPlan
53 2.1225 postgres AllocSetReset
48 1.9223 postgres AllocSetCheck
47 1.8823 postgres pfree
41 1.6420 postgres PushActiveSnapshot
40 1.6019 postgres CopySnapshot
40 1.6019 postgres bms_is_member
39 1.5619 postgres PopActiveSnapshot
37 1.4818 postgres AcquireExecutorLocks
32 1.2815 postgres array_ref
31 1.2415 postgres textlike
28 1.1213 postgres MemoryContextAlloc

sample3 FOR IN UNNEST

samples % symbol name
334 19.1844 exec_eval_expr
278 15.9678 plpgsql_param_fetch
246 14.1298 exec_eval_datum
180 10.3389 exec_stmts
140 8.0414 exec_assign_value
107 6.1459 setup_param_list
97 5.5715 exec_eval_cleanup.clone.10
97 5.5715 exec_move_row
84 4.8248 __i686.get_pc_thunk.bx
53 3.0442 exec_eval_boolean
42 2.4124 exec_simple_cast_value
36 2.0678 free_var.clone.2

samples % image name symbol name
996 11.5171 postgres pglz_decompress
507 5.8626 postgres AllocSetAlloc
494 5.7123 postgres list_member_ptr
411 4.7525 postgres MemoryContextAllocZero
344 3.9778 postgres ExecEvalParamExtern
305 3.5268 postgres GetSnapshotData
297 3.4343 postgres ExecMakeFunctionResultNoSets
265 3.0643 postgres AllocSetFree
250 2.8908 postgres UTF8_MatchText
242 2.7983 postgres LWLockRelease
236 2.7290 postgres LWLockAcquire
210 2.4283 postgres AllocSetReset
201 2.3242 postgres heap_form_tuple
198 2.2895 postgres AllocSetCheck
183 2.1161 postgres pfree
165 1.9080 postgres ExecProject
155 1.7923 postgres heap_fill_tuple
151 1.7461 postgres CopySnapshot
141 1.6304 postgres RevalidateCachedPlan
136 1.5726 postgres MemoryContextAlloc
114 1.3182 postgres PopActiveSnapshot
108 1.2488 postgres AcquireExecutorLocks
102 1.1795 postgres ExecMakeFunctionResult
102 1.1795 postgres pgstat_init_function_usage
95 1.0985 postgres textlike
94 1.0870 postgres bms_is_member
92 1.0638 postgres datumGetSize

For iteration over large array with subscripts I am thinking so enough
is a block repeated pglz_decompress. Others optimizations needs a
hundreds lines (my personal opinion)

regards

Pavel Stehule

>                        regards, tom lane
>