pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance difference in accessing differrent columns in a Postgres Table
17+ messages / 6 participants
[nested] [flat]
* Performance difference in accessing differrent columns in a Postgres Table
@ 2018-07-29 05:38 Dinesh Kumar <dns98944@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Dinesh Kumar @ 2018-07-29 05:38 UTC (permalink / raw)
To: pgsql-performance
Hello All,
I created a table with 200 bigint column, 200 varchar column. (Postgres
10.4)
create table i200c200 ( pk bigint primary key, int1 bigint, int2
bigint,....., int200 bigint, char1 varchar(255),......, char200
varchar(255)) ;
Inserted values only in pk,int1,int200 columns with some random data ( from
generate series) and remaining columns are all null. The table has 1000000
rows.
I found performance variance between accessing int1 and int200 column which
is quite large.
Reports from pg_stat_statements:
query | total_time | min_time |
max_time | mean_time | stddev_time
-----------------------------------------+------------+----------+----------+-----------+--------------------
select pk,int1 from i200c200 limit 200 | 0.65 | 0.102 |
0.138 | 0.13 | 0.0140142784330839
select pk,int199 from i200c200 limit $1 | 1.207 | 0.18 |
0.332 | 0.2414 | 0.0500583659341773
select pk,int200 from i200c200 limit 200| 1.67 | 0.215 |
0.434 | 0.334 | 0.0697825193010399
Explain Analyse:
explain analyse select pk,int1 from i200c200 limit 1000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..23.33 rows=1000 width=16) (actual
time=0.014..0.390 rows=1000 loops=1)
-> Seq Scan on i200c200 (cost=0.00..23334.00 rows=1000000
width=16) (actual time=0.013..0.268 rows=1000 loops=1)
Planning time: 0.066 ms
Execution time: 0.475 ms
explain analyse select pk,int200 from i200c200 limit 1000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..23.33 rows=1000 width=16) (actual
time=0.012..1.001 rows=1000 loops=1)
-> Seq Scan on i200c200 (cost=0.00..23334.00 rows=1000000
width=16) (actual time=0.011..0.894 rows=1000 loops=1)
Planning time: 0.049 ms
Execution time: 1.067 ms
I am curious in getting this postgres behaviour and its internals.
Note: I have the tried the same query with int199 column which is null in
all rows,it is still performance variant.Since,postgres doesn't store null
values in data instead it store in null bit map,there should not be this
variation(because i'm having data only for pk,int1,int200).I am wondering
that this null bit map lookup is slowing down this , because each row in my
table is having a null bit map of size (408 bits).As newbie I am wondering
whether this null bit map lookup for non-earlier column is taking too much
time (for scanning the null bit map itself).Am i thinking in right way?
Thanks in advance,
Dineshkumar.P
Postgres Newbie.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Performance difference in accessing differrent columns in a Postgres Table
@ 2018-07-29 21:53 David Rowley <david.rowley@2ndquadrant.com>
parent: Dinesh Kumar <dns98944@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: David Rowley @ 2018-07-29 21:53 UTC (permalink / raw)
To: Dinesh Kumar <dns98944@gmail.com>; +Cc: pgsql-performance
On 29 July 2018 at 17:38, Dinesh Kumar <dns98944@gmail.com> 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.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Performance difference in accessing differrent columns in a Postgres Table
@ 2018-07-29 23:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: David Rowley <david.rowley@2ndquadrant.com>
0 siblings, 1 reply; 17+ messages in thread
From: Tom Lane @ 2018-07-29 23:00 UTC (permalink / raw)
To: David Rowley <david.rowley@2ndquadrant.com>; +Cc: Dinesh Kumar <dns98944@gmail.com>; pgsql-performance
David Rowley <david.rowley@2ndquadrant.com> writes:
> On 29 July 2018 at 17:38, Dinesh Kumar <dns98944@gmail.com> 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.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Performance difference in accessing differrent columns in a Postgres Table
@ 2018-07-30 04:11 Pavel Stehule <pavel.stehule@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 17+ messages in thread
From: Pavel Stehule @ 2018-07-30 04:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David Rowley <david.rowley@2ndquadrant.com>; Dinesh Kumar <dns98944@gmail.com>; pgsql-performance
2018-07-30 1:00 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
> David Rowley <david.rowley@2ndquadrant.com> writes:
> > On 29 July 2018 at 17:38, Dinesh Kumar <dns98944@gmail.com> 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.
Regards
Pavel
> regards, tom lane
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Performance difference in accessing differrent columns in a Postgres Table
@ 2018-07-30 11:19 Jeff Janes <jeff.janes@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 2 replies; 17+ messages in thread
From: Jeff Janes @ 2018-07-30 11:19 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; David Rowley <david.rowley@2ndquadrant.com>; Dinesh Kumar <dns98944@gmail.com>; pgsql-performance
On Mon, Jul 30, 2018 at 12:11 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:
> 2018-07-30 1:00 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
>
>> David Rowley <david.rowley@2ndquadrant.com> writes:
>> > On 29 July 2018 at 17:38, Dinesh Kumar <dns98944@gmail.com> 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
Attachments:
[text/x-sh] wide.sh (7.4K, ../../CAMkU=1zVhQ5k5d=YyHNyrigLUNTkOj4=YB17s9--3ts8H-SO=Q@mail.gmail.com/3-wide.sh)
download | inline:
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(255),
char43 varchar(255),
char44 varchar(255),
char45 varchar(255),
char46 varchar(255),
char47 varchar(255),
char48 varchar(255),
char49 varchar(255),
char50 varchar(255),
char51 varchar(255),
char52 varchar(255),
char53 varchar(255),
char54 varchar(255),
char55 varchar(255),
char56 varchar(255),
char57 varchar(255),
char58 varchar(255),
char59 varchar(255),
char60 varchar(255),
char61 varchar(255),
char62 varchar(255),
char63 varchar(255),
char64 varchar(255),
char65 varchar(255),
char66 varchar(255),
char67 varchar(255),
char68 varchar(255),
char69 varchar(255),
char70 varchar(255),
char71 varchar(255),
char72 varchar(255),
char73 varchar(255),
char74 varchar(255),
char75 varchar(255),
char76 varchar(255),
char77 varchar(255),
char78 varchar(255),
char79 varchar(255),
char80 varchar(255),
char81 varchar(255),
char82 varchar(255),
char83 varchar(255),
char84 varchar(255),
char85 varchar(255),
char86 varchar(255),
char87 varchar(255),
char88 varchar(255),
char89 varchar(255),
char90 varchar(255),
char91 varchar(255),
char92 varchar(255),
char93 varchar(255),
char94 varchar(255),
char95 varchar(255),
char96 varchar(255),
char97 varchar(255),
char98 varchar(255),
char99 varchar(255),
char100 varchar(255),
char101 varchar(255),
char102 varchar(255),
char103 varchar(255),
char104 varchar(255),
char105 varchar(255),
char106 varchar(255),
char107 varchar(255),
char108 varchar(255),
char109 varchar(255),
char110 varchar(255),
char111 varchar(255),
char112 varchar(255),
char113 varchar(255),
char114 varchar(255),
char115 varchar(255),
char116 varchar(255),
char117 varchar(255),
char118 varchar(255),
char119 varchar(255),
char120 varchar(255),
char121 varchar(255),
char122 varchar(255),
char123 varchar(255),
char124 varchar(255),
char125 varchar(255),
char126 varchar(255),
char127 varchar(255),
char128 varchar(255),
char129 varchar(255),
char130 varchar(255),
char131 varchar(255),
char132 varchar(255),
char133 varchar(255),
char134 varchar(255),
char135 varchar(255),
char136 varchar(255),
char137 varchar(255),
char138 varchar(255),
char139 varchar(255),
char140 varchar(255),
char141 varchar(255),
char142 varchar(255),
char143 varchar(255),
char144 varchar(255),
char145 varchar(255),
char146 varchar(255),
char147 varchar(255),
char148 varchar(255),
char149 varchar(255),
char150 varchar(255),
char151 varchar(255),
char152 varchar(255),
char153 varchar(255),
char154 varchar(255),
char155 varchar(255),
char156 varchar(255),
char157 varchar(255),
char158 varchar(255),
char159 varchar(255),
char160 varchar(255),
char161 varchar(255),
char162 varchar(255),
char163 varchar(255),
char164 varchar(255),
char165 varchar(255),
char166 varchar(255),
char167 varchar(255),
char168 varchar(255),
char169 varchar(255),
char170 varchar(255),
char171 varchar(255),
char172 varchar(255),
char173 varchar(255),
char174 varchar(255),
char175 varchar(255),
char176 varchar(255),
char177 varchar(255),
char178 varchar(255),
char179 varchar(255),
char180 varchar(255),
char181 varchar(255),
char182 varchar(255),
char183 varchar(255),
char184 varchar(255),
char185 varchar(255),
char186 varchar(255),
char187 varchar(255),
char188 varchar(255),
char189 varchar(255),
char190 varchar(255),
char191 varchar(255),
char192 varchar(255),
char193 varchar(255),
char194 varchar(255),
char195 varchar(255),
char196 varchar(255),
char197 varchar(255),
char198 varchar(255),
char199 varchar(255),
char200 varchar(255)
) ;
insert into i200c200 (pk, int1, int200) select x, random()*100000000, random()*100000000 from generate_series(1,10000000) f(x);
vacuum analyze i200c200;
explain analyze select pk, int200 from i200c200;
explain analyze select pk, int200 from i200c200;
set jit=off;
explain analyze select pk, int200 from i200c200;
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Performance difference in accessing differrent columns in a Postgres Table
@ 2018-07-30 16:01 Pavel Stehule <pavel.stehule@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
1 sibling, 2 replies; 17+ messages in thread
From: Pavel Stehule @ 2018-07-30 16:01 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; David Rowley <david.rowley@2ndquadrant.com>; Dinesh Kumar <dns98944@gmail.com>; pgsql-performance
2018-07-30 13:19 GMT+02:00 Jeff Janes <jeff.janes@gmail.com>:
> On Mon, Jul 30, 2018 at 12:11 AM, Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
>
>> 2018-07-30 1:00 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
>>
>>> David Rowley <david.rowley@2ndquadrant.com> writes:
>>> > On 29 July 2018 at 17:38, Dinesh Kumar <dns98944@gmail.com> 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
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Performance difference in accessing differrent columns in a Postgres Table
@ 2018-07-30 17:23 Andres Freund <andres@anarazel.de>
parent: Jeff Janes <jeff.janes@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: Andres Freund @ 2018-07-30 17:23 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; David Rowley <david.rowley@2ndquadrant.com>; Dinesh Kumar <dns98944@gmail.com>; pgsql-performance
On 2018-07-30 07:19:07 -0400, Jeff Janes wrote:
> On Mon, Jul 30, 2018 at 12:11 AM, Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
>
> > 2018-07-30 1:00 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
> >
> >> David Rowley <david.rowley@2ndquadrant.com> writes:
> >> > On 29 July 2018 at 17:38, Dinesh Kumar <dns98944@gmail.com> 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..233332.28 rows=9999828 width=16) (actual rows=10000000 loops=1) │
│ Buffers: shared hit=133334 │
│ 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..233332.28 rows=9999828 width=16) (actual rows=10000000 loops=1) │
│ Buffers: shared hit=133334 │
│ 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..2600000.00 rows=10000000 width=16) (actual rows=10000000 loops=1) │
│ Buffers: shared hit=2500000 │
│ Planning Time: 0.066 ms │
│ JIT: │
│ Functions: 2 │
│ Inlining: true │
│ Optimization: true │
│ Execution Time: 4837.872 ms │
└─────────────────────────────────────────────────────────────────────────────────────────────────────┘
(8 rows)
postgres[28528][1]=# ;SET jit_above_cost = -1; set jit_optimize_above_cost = 0; set jit_inline_above_cost = 0;
postgres[28528][1]=# explain (analyze, buffers, timing off) select pk, int200 from i200c200;
┌─────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├─────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Seq Scan on i200c200 (cost=0.00..2600000.00 rows=10000000 width=16) (actual rows=10000000 loops=1) │
│ Buffers: shared hit=2500000 │
│ Planning Time: 0.067 ms │
│ Execution Time: 8192.236 ms │
└─────────────────────────────────────────────────────────────────────────────────────────────────────┘
that's because the JITed version essentially now boils down to a near
optimal loop around the intermittent bigint columns (which we deform
because we use a slot - at some point we're going to have to do
better). No checks for the NULL bitmap, no alignment considerations,
all that's optimized away.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Performance difference in accessing differrent columns in a Postgres Table
@ 2018-07-30 17:31 Jeff Janes <jeff.janes@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: Jeff Janes @ 2018-07-30 17:31 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; David Rowley <david.rowley@2ndquadrant.com>; Dinesh Kumar <dns98944@gmail.com>; pgsql-performance
On Mon, Jul 30, 2018 at 12:01 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:
>
>
> 2018-07-30 13:19 GMT+02:00 Jeff Janes <jeff.janes@gmail.com>:
>
>> On Mon, Jul 30, 2018 at 12:11 AM, Pavel Stehule <pavel.stehule@gmail.com>
>> wrote:
>>
>>> 2018-07-30 1:00 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
>>>
>>>> David Rowley <david.rowley@2ndquadrant.com> writes:
>>>> > On 29 July 2018 at 17:38, Dinesh Kumar <dns98944@gmail.com> 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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Performance difference in accessing differrent columns in a Postgres Table
@ 2018-07-30 17:45 Andres Freund <andres@anarazel.de>
parent: Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 0 replies; 17+ messages in thread
From: Andres Freund @ 2018-07-30 17:45 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Jeff Janes <jeff.janes@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; David Rowley <david.rowley@2ndquadrant.com>; Dinesh Kumar <dns98944@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Performance difference in accessing differrent columns in a Postgres Table
@ 2018-07-30 19:02 Andres Freund <andres@anarazel.de>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Andres Freund @ 2018-07-30 19:02 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; David Rowley <david.rowley@2ndquadrant.com>; Dinesh Kumar <dns98944@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Performance difference in accessing differrent columns in a Postgres Table
@ 2018-07-31 16:56 Jeff Janes <jeff.janes@gmail.com>
parent: Andres Freund <andres@anarazel.de>
0 siblings, 1 reply; 17+ messages in thread
From: Jeff Janes @ 2018-07-31 16:56 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; David Rowley <david.rowley@2ndquadrant.com>; Dinesh Kumar <dns98944@gmail.com>; pgsql-performance
On Mon, Jul 30, 2018 at 1:23 PM, Andres Freund <andres@anarazel.de> wrote:
> On 2018-07-30 07:19:07 -0400, Jeff Janes wrote:
>
> > 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?
>
No, it is definitely JIT. The explain plans show it, and the cost of the
query is 230,000 while the default setting of jit_above_cost is 100,000.
It is fully reproducible by repeatedly toggling the JIT setting. It
doesn't seem to be the cost of compiling the code that slows it down (I'm
assuming the code is compiled once per tuple descriptor, not once per
tuple), but rather the efficiency of the compiled code.
>
> > 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:
>
I don't see a change when I compile without --enable-debug,
and jit_debugging_support is off, or in 11beta2 nonexistent. How can I
know if I have a debug LLVM build, and turn it off if I do?
>
>
> 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;
>
Lowering jit_optimize_above_cost does redeem this for me. It brings it
back to being a tie with JIT=OFF. I don't see any further improvement by
lowering jit_inline_above_cost, and overall it is just a statistical tie
with JIT=off, not an improvement as you get, but at least it isn't a
substantial loss.
Under what conditions would I want to do jit without doing optimizations on
it? Is there a rule of thumb that could be documented, or do we just use
the experimental method for each query?
I don't know how sensitive JIT is to hardware. I'm using Ubuntu 16.04 on
VirtualBox (running on Windows 10) on an i5-7200U, which might be important.
I had previously done a poor-man's JIT where I created 4 versions of the
main 'for' loop in slot_deform_tuple. I did a branch on "if(hasnulls)",
and then each branch had two loops, one for when 'slow' is false, and then
one for after 'slow' becomes true so we don't have to keep setting it true
again once it already is, in a tight loop. I didn't see noticeable
improvement there (although perhaps I would have on different hardware), so
didn't see how JIT could help with this almost-entirely-null case. I'm not
trying to address JIT in general, just as it applies to this particular
case.
Unrelated to JIT and relevant to the 'select pk, int199' case but not the
'select pk, int200' case, it seems we have gone to some length to make slot
deforming be efficient for incremental use, but then just deform in bulk
anyway up to maximum attnum used in the query, at least in this case. Is
that because incremental deforming is not cache efficient?
Cheers,
Jeff
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Performance difference in accessing differrent columns in a Postgres Table
@ 2018-07-31 17:35 Andres Freund <andres@anarazel.de>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: Andres Freund @ 2018-07-31 17:35 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; David Rowley <david.rowley@2ndquadrant.com>; Dinesh Kumar <dns98944@gmail.com>; pgsql-performance
Hi,
On 2018-07-31 12:56:26 -0400, Jeff Janes wrote:
> On Mon, Jul 30, 2018 at 1:23 PM, Andres Freund <andres@anarazel.de> wrote:
>
> > On 2018-07-30 07:19:07 -0400, Jeff Janes wrote:
> >
> > > 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?
> >
>
> No, it is definitely JIT. The explain plans show it, and the cost of the
> query is 230,000 while the default setting of jit_above_cost is 100,000.
> It is fully reproducible by repeatedly toggling the JIT setting. It
> doesn't seem to be the cost of compiling the code that slows it down (I'm
> assuming the code is compiled once per tuple descriptor, not once per
> tuple), but rather the efficiency of the compiled code.
Interesting. I see a smaller benefit without opt, but still one. I guess
that depends on code emission.
> > > 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:
> >
>
> I don't see a change when I compile without --enable-debug,
> and jit_debugging_support is off, or in 11beta2 nonexistent. How can I
> know if I have a debug LLVM build, and turn it off if I do?
llvm-config --assertion-mode should tell you.
> > 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;
> >
>
> Lowering jit_optimize_above_cost does redeem this for me. It brings it
> back to being a tie with JIT=OFF. I don't see any further improvement by
> lowering jit_inline_above_cost, and overall it is just a statistical tie
> with JIT=off, not an improvement as you get, but at least it isn't a
> substantial loss.
Interesting, as posted, I do see quite measurable improvements. What's
your version of LLVM?
> Under what conditions would I want to do jit without doing optimizations on
> it? Is there a rule of thumb that could be documented, or do we just use
> the experimental method for each query?
I don't think we quite know yet. Optimization for larger queries can
take a while. For expression heavy queries there's a window where JITing
can help, but optimization can be beneficial.
> I had previously done a poor-man's JIT where I created 4 versions of the
> main 'for' loop in slot_deform_tuple. I did a branch on "if(hasnulls)",
> and then each branch had two loops, one for when 'slow' is false, and then
> one for after 'slow' becomes true so we don't have to keep setting it true
> again once it already is, in a tight loop. I didn't see noticeable
> improvement there (although perhaps I would have on different hardware), so
> didn't see how JIT could help with this almost-entirely-null case. I'm not
> trying to address JIT in general, just as it applies to this particular
> case.
I don't see how it follows from that observation that JITing can't be
beneficial? The bitmap access alone can be optimized if you unroll the
loop (as now the offsets into it are constant). The offset computations
into tts_values/isnull aren't dynamic anymore. The loop counter is
gone. And nearly all tuple have hasnulls set, so specializing for that
case isn't going to get you that much, it's perfectly predictable.
> Unrelated to JIT and relevant to the 'select pk, int199' case but not the
> 'select pk, int200' case, it seems we have gone to some length to make slot
> deforming be efficient for incremental use, but then just deform in bulk
> anyway up to maximum attnum used in the query, at least in this case. Is
> that because incremental deforming is not cache efficient?
Well, that's not *quite* how it works: We always deform up to the point
used in a certain "level" of the query. E.g. if a select's where clause
needs something up to attribute 3, the seqscan might deform only up to
there, even though an aggregate ontop of that might need up to 10. But
yes, you're right, uselessly incrementally deforming isn't cache
efficient.
I think before long we're going to have to change the slot mechanism so
we don't deform columns we don't actually need. I.e we'll need something
like a bitmap of needed columns and skip over unneeded ones. When not
JITed that'll allow us to skip copying such columns (removing an 8 and 1
byte write), when JITing we can do better, and e.g. entirely skip
processing fixed width NOT MULL columns that aren't needed.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Performance difference in accessing differrent columns in a Postgres Table
@ 2018-08-01 15:21 Jeff Janes <jeff.janes@gmail.com>
parent: Andres Freund <andres@anarazel.de>
0 siblings, 1 reply; 17+ messages in thread
From: Jeff Janes @ 2018-08-01 15:21 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; David Rowley <david.rowley@2ndquadrant.com>; Dinesh Kumar <dns98944@gmail.com>; pgsql-performance
On Mon, Jul 30, 2018 at 3:02 PM, Andres Freund <andres@anarazel.de> wrote:
> 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?
>
I don't think you showed any timings where jit_above_cost < query cost <
jit_optimize_above_cost, which is where I saw the slow down. (That is also
where things naturally land for me using default settings)
I've repeated my test case on a default build (./configure --with-llvm
--prefix=....) and default postgresql.conf, using the post-11BETA2 commit
5a71d3e.
I've attached the full test case, and the full output.
Here are the last two executions, with jit=on and jit=off, respectively.
Doing it with TIMING OFF doesn't meaningfully change things, nor does
increasing shared_buffers beyond the default.
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Seq Scan on i200c200 (cost=0.00..233332.28 rows=9999828 width=16) (actual
time=29.317..11966.291 rows=10000000 loops=1)
Planning Time: 0.034 ms
JIT:
Functions: 2
Generation Time: 1.589 ms
Inlining: false
Inlining Time: 0.000 ms
Optimization: false
Optimization Time: 9.002 ms
Emission Time: 19.948 ms
Execution Time: 12375.493 ms
(11 rows)
Time: 12376.281 ms (00:12.376)
SET
Time: 1.955 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on i200c200 (cost=0.00..233332.28 rows=9999828 width=16) (actual
time=0.063..3897.302 rows=10000000 loops=1)
Planning Time: 0.037 ms
Execution Time: 4292.400 ms
(3 rows)
Time: 4293.196 ms (00:04.293)
Cheers,
Jeff
Attachments:
[application/octet-stream] wide.sql (7.6K, ../../CAMkU=1zrdi8Ga21E_ECQ8bP+9+Q7E76gkG5Xj_MLkuCQNm4mDg@mail.gmail.com/3-wide.sql)
download
[application/octet-stream] wide.out (3.0K, ../../CAMkU=1zrdi8Ga21E_ECQ8bP+9+Q7E76gkG5Xj_MLkuCQNm4mDg@mail.gmail.com/4-wide.out)
download
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Performance difference in accessing differrent columns in a Postgres Table
@ 2018-09-05 04:21 Dinesh Kumar <dns98944@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Dinesh Kumar @ 2018-09-05 04:21 UTC (permalink / raw)
To: jeff.janes@gmail.com; +Cc: andres@anarazel.de; pavel.stehule@gmail.com; tgl@sss.pgh.pa.us; david.rowley@2ndquadrant.com; pgsql-performance
Hi All,
I was wondering whether the case is solved or still continuing. As a
Postgres newbie, I can't understand any of the terms (JIT, tuple
deformation) as you mentioned above. Please anyone let me know , what is
the current scenario.
Thanks,
Dineshkumar.
On Wed, Aug 1, 2018 at 8:51 PM Jeff Janes <jeff.janes@gmail.com> wrote:
> On Mon, Jul 30, 2018 at 3:02 PM, Andres Freund <andres@anarazel.de> wrote:
>
>> 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?
>>
>
>
> I don't think you showed any timings where jit_above_cost < query cost <
> jit_optimize_above_cost, which is where I saw the slow down. (That is also
> where things naturally land for me using default settings)
>
> I've repeated my test case on a default build (./configure --with-llvm
> --prefix=....) and default postgresql.conf, using the post-11BETA2 commit
> 5a71d3e.
>
>
> I've attached the full test case, and the full output.
>
> Here are the last two executions, with jit=on and jit=off, respectively.
> Doing it with TIMING OFF doesn't meaningfully change things, nor does
> increasing shared_buffers beyond the default.
>
>
>
> QUERY PLAN
>
> --------------------------------------------------------------------------------------------------------------------------
> Seq Scan on i200c200 (cost=0.00..233332.28 rows=9999828 width=16)
> (actual time=29.317..11966.291 rows=10000000 loops=1)
> Planning Time: 0.034 ms
> JIT:
> Functions: 2
> Generation Time: 1.589 ms
> Inlining: false
> Inlining Time: 0.000 ms
> Optimization: false
> Optimization Time: 9.002 ms
> Emission Time: 19.948 ms
> Execution Time: 12375.493 ms
> (11 rows)
>
> Time: 12376.281 ms (00:12.376)
> SET
> Time: 1.955 ms
> QUERY PLAN
>
> ------------------------------------------------------------------------------------------------------------------------
> Seq Scan on i200c200 (cost=0.00..233332.28 rows=9999828 width=16)
> (actual time=0.063..3897.302 rows=10000000 loops=1)
> Planning Time: 0.037 ms
> Execution Time: 4292.400 ms
> (3 rows)
>
> Time: 4293.196 ms (00:04.293)
>
> Cheers,
>
> Jeff
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Performance difference in accessing differrent columns in a Postgres Table
@ 2018-09-05 16:00 Jeff Janes <jeff.janes@gmail.com>
parent: Dinesh Kumar <dns98944@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Jeff Janes @ 2018-09-05 16:00 UTC (permalink / raw)
To: Dinesh Kumar <dns98944@gmail.com>; +Cc: Andres Freund <andres@anarazel.de>; Pavel Stehule <pavel.stehule@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; David Rowley <david.rowley@2ndquadrant.com>; pgsql-performance
On Wed, Sep 5, 2018 at 12:21 AM Dinesh Kumar <dns98944@gmail.com> wrote:
> Hi All,
> I was wondering whether the case is solved or still continuing. As a
> Postgres newbie, I can't understand any of the terms (JIT, tuple
> deformation) as you mentioned above. Please anyone let me know , what is
> the current scenario.
>
>
JIT is a just-in-time compilation, which will be new in v11. Tuple
deforming is how you get the row from the on-disk format to the in-memory
format.
Some people see small improvements in tuple deforming using JIT in your
situation, some see large decreases, depending on settings and apparently
on hardware. But regardless, JIT is not going to reduce your particular
use case (many nullable and actually null columns, referencing a
high-numbered column) down to being constant-time operation in the number
of preceding columns. Maybe JIT will reduce the penalty for accessing a
high-numbered column by 30%, but won't reduce the penalty by 30 fold. Put
your NOT NULL columns first and then most frequently accessed NULLable
columns right after them, if you can.
Cheers,
Jeff
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Performance difference in accessing differrent columns in a Postgres Table
@ 2018-09-05 16:07 Jeff Janes <jeff.janes@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Jeff Janes @ 2018-09-05 16:07 UTC (permalink / raw)
To: Dinesh Kumar <dns98944@gmail.com>; +Cc: Andres Freund <andres@anarazel.de>; Pavel Stehule <pavel.stehule@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; David Rowley <david.rowley@2ndquadrant.com>; pgsql-performance
On Wed, Sep 5, 2018 at 12:00 PM Jeff Janes <jeff.janes@gmail.com> wrote:
> On Wed, Sep 5, 2018 at 12:21 AM Dinesh Kumar <dns98944@gmail.com> wrote:
>
>> Hi All,
>> I was wondering whether the case is solved or still continuing. As a
>> Postgres newbie, I can't understand any of the terms (JIT, tuple
>> deformation) as you mentioned above. Please anyone let me know , what is
>> the current scenario.
>>
>>
> JIT is a just-in-time compilation, which will be new in v11. Tuple
> deforming is how you get the row from the on-disk format to the in-memory
> format.
>
> Some people see small improvements in tuple deforming using JIT in your
> situation, some see large decreases, depending on settings and apparently
> on hardware. But regardless, JIT is not going to reduce your particular
> use case (many nullable and actually null columns, referencing a
> high-numbered column) down to being constant-time operation in the number
> of preceding columns. Maybe JIT will reduce the penalty for accessing a
> high-numbered column by 30%, but won't reduce the penalty by 30 fold. Put
> your NOT NULL columns first and then most frequently accessed NULLable
> columns right after them, if you can.
>
Correction: NOT NULL columns with fixed width types first. Then of the
columns which are either nullable or variable width types, put the most
frequently accessed earlier.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Performance difference in accessing differrent columns in a Postgres Table
@ 2018-09-07 03:28 Dinesh Kumar <dns98944@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: Dinesh Kumar @ 2018-09-07 03:28 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Andres Freund <andres@anarazel.de>; Pavel Stěhule <pavel.stehule@gmail.com>; tgl@sss.pgh.pa.us; david.rowley@2ndquadrant.com; pgsql-performance
Ok, will do that. Thanks a lot.
On Wed, Sep 5, 2018 at 9:37 PM Jeff Janes <jeff.janes@gmail.com> wrote:
>
>
> On Wed, Sep 5, 2018 at 12:00 PM Jeff Janes <jeff.janes@gmail.com> wrote:
>
>> On Wed, Sep 5, 2018 at 12:21 AM Dinesh Kumar <dns98944@gmail.com> wrote:
>>
>>> Hi All,
>>> I was wondering whether the case is solved or still continuing. As a
>>> Postgres newbie, I can't understand any of the terms (JIT, tuple
>>> deformation) as you mentioned above. Please anyone let me know , what is
>>> the current scenario.
>>>
>>>
>> JIT is a just-in-time compilation, which will be new in v11. Tuple
>> deforming is how you get the row from the on-disk format to the in-memory
>> format.
>>
>> Some people see small improvements in tuple deforming using JIT in your
>> situation, some see large decreases, depending on settings and apparently
>> on hardware. But regardless, JIT is not going to reduce your particular
>> use case (many nullable and actually null columns, referencing a
>> high-numbered column) down to being constant-time operation in the number
>> of preceding columns. Maybe JIT will reduce the penalty for accessing a
>> high-numbered column by 30%, but won't reduce the penalty by 30 fold. Put
>> your NOT NULL columns first and then most frequently accessed NULLable
>> columns right after them, if you can.
>>
>
> Correction: NOT NULL columns with fixed width types first. Then of the
> columns which are either nullable or variable width types, put the most
> frequently accessed earlier.
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2018-09-07 03:28 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-07-29 05:38 Performance difference in accessing differrent columns in a Postgres Table Dinesh Kumar <dns98944@gmail.com>
2018-07-29 21:53 ` David Rowley <david.rowley@2ndquadrant.com>
2018-07-29 23:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-07-30 04:11 ` Pavel Stehule <pavel.stehule@gmail.com>
2018-07-30 11:19 ` Jeff Janes <jeff.janes@gmail.com>
2018-07-30 16:01 ` Pavel Stehule <pavel.stehule@gmail.com>
2018-07-30 17:31 ` Jeff Janes <jeff.janes@gmail.com>
2018-07-30 19:02 ` Andres Freund <andres@anarazel.de>
2018-08-01 15:21 ` Jeff Janes <jeff.janes@gmail.com>
2018-09-05 04:21 ` Dinesh Kumar <dns98944@gmail.com>
2018-09-05 16:00 ` Jeff Janes <jeff.janes@gmail.com>
2018-09-05 16:07 ` Jeff Janes <jeff.janes@gmail.com>
2018-09-07 03:28 ` Dinesh Kumar <dns98944@gmail.com>
2018-07-30 17:45 ` Andres Freund <andres@anarazel.de>
2018-07-30 17:23 ` Andres Freund <andres@anarazel.de>
2018-07-31 16:56 ` Jeff Janes <jeff.janes@gmail.com>
2018-07-31 17:35 ` Andres Freund <andres@anarazel.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox