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

2010/11/18 Tom Lane <tgl@sss.pgh.pa.us>:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> 2010/11/18 Alvaro Herrera <alvherre@commandprompt.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
>