2010/11/18 Tom Lane <t...@sss.pgh.pa.us>: > Pavel Stehule <pavel.steh...@gmail.com> writes: >> 2010/11/18 Alvaro Herrera <alvhe...@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 > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
