Re: Performance difference in accessing differrent columns in a Postgres Table
On Mon, Jul 30, 2018 at 12:11 AM, Pavel Stehule wrote: > 2018-07-30 1:00 GMT+02:00 Tom Lane : > >> David Rowley writes: >> > On 29 July 2018 at 17:38, Dinesh Kumar wrote: >> >> I found performance variance between accessing int1 and int200 column >> which >> >> is quite large. >> >> > Have a look at slot_deform_tuple and heap_deform_tuple. You'll see >> > that tuples are deformed starting at the first attribute. If you ask >> > for attribute 200 then it must deform 1-199 first. >> >> Note that that can be optimized away in some cases, though evidently >> not the one the OP is testing. From memory, you need a tuple that >> contains no nulls, and all the columns to the left of the target >> column have to be fixed-width datatypes. Otherwise, the offset to >> the target column is uncertain, and we have to search for it. >> > > JIT decrease a overhead of this. > The bottleneck here is such a simple construct, I don't see how JIT could improve it by much. And indeed, in my hands JIT makes it almost 3 times worse. Run against ab87b8fedce3fa77ca0d6, I get 12669.619 ms for the 2nd JIT execution and 4594.994 ms for the JIT=off. Cheers, Jeff drop table if exists i200c200; create table i200c200 ( pk bigint primary key, int1 bigint, int2 bigint, int3 bigint, int4 bigint, int5 bigint, int6 bigint, int7 bigint, int8 bigint, int9 bigint, int10 bigint, int11 bigint, int12 bigint, int13 bigint, int14 bigint, int15 bigint, int16 bigint, int17 bigint, int18 bigint, int19 bigint, int20 bigint, int21 bigint, int22 bigint, int23 bigint, int24 bigint, int25 bigint, int26 bigint, int27 bigint, int28 bigint, int29 bigint, int30 bigint, int31 bigint, int32 bigint, int33 bigint, int34 bigint, int35 bigint, int36 bigint, int37 bigint, int38 bigint, int39 bigint, int40 bigint, int41 bigint, int42 bigint, int43 bigint, int44 bigint, int45 bigint, int46 bigint, int47 bigint, int48 bigint, int49 bigint, int50 bigint, int51 bigint, int52 bigint, int53 bigint, int54 bigint, int55 bigint, int56 bigint, int57 bigint, int58 bigint, int59 bigint, int60 bigint, int61 bigint, int62 bigint, int63 bigint, int64 bigint, int65 bigint, int66 bigint, int67 bigint, int68 bigint, int69 bigint, int70 bigint, int71 bigint, int72 bigint, int73 bigint, int74 bigint, int75 bigint, int76 bigint, int77 bigint, int78 bigint, int79 bigint, int80 bigint, int81 bigint, int82 bigint, int83 bigint, int84 bigint, int85 bigint, int86 bigint, int87 bigint, int88 bigint, int89 bigint, int90 bigint, int91 bigint, int92 bigint, int93 bigint, int94 bigint, int95 bigint, int96 bigint, int97 bigint, int98 bigint, int99 bigint, int100 bigint, int101 bigint, int102 bigint, int103 bigint, int104 bigint, int105 bigint, int106 bigint, int107 bigint, int108 bigint, int109 bigint, int110 bigint, int111 bigint, int112 bigint, int113 bigint, int114 bigint, int115 bigint, int116 bigint, int117 bigint, int118 bigint, int119 bigint, int120 bigint, int121 bigint, int122 bigint, int123 bigint, int124 bigint, int125 bigint, int126 bigint, int127 bigint, int128 bigint, int129 bigint, int130 bigint, int131 bigint, int132 bigint, int133 bigint, int134 bigint, int135 bigint, int136 bigint, int137 bigint, int138 bigint, int139 bigint, int140 bigint, int141 bigint, int142 bigint, int143 bigint, int144 bigint, int145 bigint, int146 bigint, int147 bigint, int148 bigint, int149 bigint, int150 bigint, int151 bigint, int152 bigint, int153 bigint, int154 bigint, int155 bigint, int156 bigint, int157 bigint, int158 bigint, int159 bigint, int160 bigint, int161 bigint, int162 bigint, int163 bigint, int164 bigint, int165 bigint, int166 bigint, int167 bigint, int168 bigint, int169 bigint, int170 bigint, int171 bigint, int172 bigint, int173 bigint, int174 bigint, int175 bigint, int176 bigint, int177 bigint, int178 bigint, int179 bigint, int180 bigint, int181 bigint, int182 bigint, int183 bigint, int184 bigint, int185 bigint, int186 bigint, int187 bigint, int188 bigint, int189 bigint, int190 bigint, int191 bigint, int192 bigint, int193 bigint, int194 bigint, int195 bigint, int196 bigint, int197 bigint, int198 bigint, int199 bigint, int200 bigint, char1 varchar(255), char2 varchar(255), char3 varchar(255), char4 varchar(255), char5 varchar(255), char6 varchar(255), char7 varchar(255), char8 varchar(255), char9 varchar(255), char10 varchar(255), char11 varchar(255), char12 varchar(255), char13 varchar(255), char14 varchar(255), char15 varchar(255), char16 varchar(255), char17 varchar(255), char18 varchar(255), char19 varchar(255), char20 varchar(255), char21 varchar(255), char22 varchar(255), char23 varchar(255), char24 varchar(255), char25 varchar(255), char26 varchar(255), char27 varchar(255), char28 varchar(255), char29 varchar(255), char30 varchar(255), char31 varchar(255), char32 varchar(255), char33 varchar(255), char34 varchar(255), char35 varchar(255), char36 varchar(255), char37 varchar(255), char38 varchar(255), char39 varchar(255), char40 varchar(255), char41 varchar(255), char42 varchar(
Re: Performance difference in accessing differrent columns in a Postgres Table
2018-07-30 13:19 GMT+02:00 Jeff Janes : > On Mon, Jul 30, 2018 at 12:11 AM, Pavel Stehule > wrote: > >> 2018-07-30 1:00 GMT+02:00 Tom Lane : >> >>> David Rowley writes: >>> > On 29 July 2018 at 17:38, Dinesh Kumar wrote: >>> >> I found performance variance between accessing int1 and int200 column >>> which >>> >> is quite large. >>> >>> > Have a look at slot_deform_tuple and heap_deform_tuple. You'll see >>> > that tuples are deformed starting at the first attribute. If you ask >>> > for attribute 200 then it must deform 1-199 first. >>> >>> Note that that can be optimized away in some cases, though evidently >>> not the one the OP is testing. From memory, you need a tuple that >>> contains no nulls, and all the columns to the left of the target >>> column have to be fixed-width datatypes. Otherwise, the offset to >>> the target column is uncertain, and we have to search for it. >>> >> >> JIT decrease a overhead of this. >> > > The bottleneck here is such a simple construct, I don't see how JIT could > improve it by much. > > And indeed, in my hands JIT makes it almost 3 times worse. > > Run against ab87b8fedce3fa77ca0d6, I get 12669.619 ms for the 2nd JIT > execution and 4594.994 ms for the JIT=off. > look on http://www.postgresql-archive.org/PATCH-LLVM-tuple-deforming-improvements-td6029385.html thread, please. Regards Pavel > Cheers, > > Jeff >
Re: Performance difference in accessing differrent columns in a Postgres Table
On 2018-07-30 07:19:07 -0400, Jeff Janes wrote: > On Mon, Jul 30, 2018 at 12:11 AM, Pavel Stehule > wrote: > > > 2018-07-30 1:00 GMT+02:00 Tom Lane : > > > >> David Rowley writes: > >> > On 29 July 2018 at 17:38, Dinesh Kumar wrote: > >> >> I found performance variance between accessing int1 and int200 column > >> which > >> >> is quite large. > >> > >> > Have a look at slot_deform_tuple and heap_deform_tuple. You'll see > >> > that tuples are deformed starting at the first attribute. If you ask > >> > for attribute 200 then it must deform 1-199 first. > >> > >> Note that that can be optimized away in some cases, though evidently > >> not the one the OP is testing. From memory, you need a tuple that > >> contains no nulls, and all the columns to the left of the target > >> column have to be fixed-width datatypes. Otherwise, the offset to > >> the target column is uncertain, and we have to search for it. > >> > > > > JIT decrease a overhead of this. > > > > The bottleneck here is such a simple construct, I don't see how JIT could > improve it by much. The deparsing can become quite a bit faster with JITing, because we know the column types and width. If intermittent columns are NOT NULL and fixed width, we can even optimize processing them at runtime nearly entirely. > And indeed, in my hands JIT makes it almost 3 times worse. Not in my measurement. Your example won't use JIT at all, because it's below the cost threshold. So I think you might just be seeing cache + hint bit effects? > Run against ab87b8fedce3fa77ca0d6, I get 12669.619 ms for the 2nd JIT > execution and 4594.994 ms for the JIT=off. Even with a debug LLVM build, which greatly increases compilation overhead, I actually see quite the benefit when I force JIT to be used: postgres[26832][1]=# ;SET jit_above_cost = -1; set jit_optimize_above_cost = 0; set jit_inline_above_cost = 0; postgres[26832][1]=# explain (analyze, buffers, timing off) select pk, int200 from i200c200; ┌───┐ │QUERY PLAN │ ├───┤ │ Seq Scan on i200c200 (cost=0.00..22.28 rows=828 width=16) (actual rows=1000 loops=1) │ │ Buffers: shared hit=14 │ │ Planning Time: 0.069 ms │ │ Execution Time: 3645.069 ms │ └───┘ (4 rows) postgres[26832][1]=# ;SET jit_above_cost = 0; set jit_optimize_above_cost = 0; set jit_inline_above_cost = 0; postgres[26832][1]=# explain (analyze, buffers, timing off) select pk, int200 from i200c200; ┌───┐ │QUERY PLAN │ ├───┤ │ Seq Scan on i200c200 (cost=0.00..22.28 rows=828 width=16) (actual rows=1000 loops=1) │ │ Buffers: shared hit=14 │ │ Planning Time: 0.070 ms │ │ JIT: │ │ Functions: 2 │ │ Inlining: true │ │ Optimization: true │ │ Execution Time: 3191.683 ms │ └───┘ (8 rows) Now that's not *huge*, but nothing either. And it's a win even though JITing takes it good own time (we need to improve on that). If I force all the bigint columns to be NOT NULL DEFAULT 0 the results get more drastic: postgres[28528][1]=# ;SET jit_above_cost = 0; set jit_optimize_above_cost = 0; set jit_inline_above_cost = 0; ┌─┐ │ QUERY PLAN │ ├─┤ │ Seq Scan on i200c200 (cost=0.00..260.00 rows=1000 width=16) (actual rows=1
Re: Performance difference in accessing differrent columns in a Postgres Table
On Mon, Jul 30, 2018 at 12:01 PM, Pavel Stehule wrote: > > > 2018-07-30 13:19 GMT+02:00 Jeff Janes : > >> On Mon, Jul 30, 2018 at 12:11 AM, Pavel Stehule >> wrote: >> >>> 2018-07-30 1:00 GMT+02:00 Tom Lane : >>> David Rowley writes: > On 29 July 2018 at 17:38, Dinesh Kumar wrote: >> I found performance variance between accessing int1 and int200 column which >> is quite large. > Have a look at slot_deform_tuple and heap_deform_tuple. You'll see > that tuples are deformed starting at the first attribute. If you ask > for attribute 200 then it must deform 1-199 first. Note that that can be optimized away in some cases, though evidently not the one the OP is testing. From memory, you need a tuple that contains no nulls, and all the columns to the left of the target column have to be fixed-width datatypes. Otherwise, the offset to the target column is uncertain, and we have to search for it. >>> >>> JIT decrease a overhead of this. >>> >> >> The bottleneck here is such a simple construct, I don't see how JIT could >> improve it by much. >> >> And indeed, in my hands JIT makes it almost 3 times worse. >> >> Run against ab87b8fedce3fa77ca0d6, I get 12669.619 ms for the 2nd JIT >> execution and 4594.994 ms for the JIT=off. >> > > look on http://www.postgresql-archive.org/PATCH-LLVM-tuple- > deforming-improvements-td6029385.html thread, please. > > The opt1 patch did get performance back to "at least do no harm" territory, but it didn't improve over JIT=off. Adding the other two didn't get any further improvement. I don't know where the time is going with the as-committed JIT. None of the JIT-specific timings reported by EXPLAIN (ANALYZE) add up to anything close to the slow-down I'm seeing. Shouldn't compiling and optimization time show up there? Cheers, Jeff
Re: Performance difference in accessing differrent columns in a Postgres Table
Hi, On 2018-07-30 18:01:34 +0200, Pavel Stehule wrote: > look on > http://www.postgresql-archive.org/PATCH-LLVM-tuple-deforming-improvements-td6029385.html > thread, please. Given the results I just posted in the sibling email I don't think those issues apply here. Greetings, Andres Freund
Re: Performance difference in accessing differrent columns in a Postgres Table
Hi, On 2018-07-30 13:31:33 -0400, Jeff Janes wrote: > I don't know where the time is going with the as-committed JIT. None of > the JIT-specific timings reported by EXPLAIN (ANALYZE) add up to anything > close to the slow-down I'm seeing. Shouldn't compiling and optimization > time show up there? As my timings showed, I don't see the slowdown you're reporting. Could you post a few EXPLAIN ANALYZEs? Greetings, Andres Freund
