Re: [HACKERS] is possible cache tupledesc templates in execution plan? significant performance issue, maybe bug?

[Pavel Stehule]

2017-10-08 19:10 GMT+02:00 Pavel Stehule :

>
>
> 2017-10-08 19:04 GMT+02:00 Pavel Stehule :
>
>>
>>
>> 2017-10-08 18:59 GMT+02:00 Andres Freund :
>>
>>> On 2017-10-08 18:57:28 +0200, Pavel Stehule wrote:
>>> > 2017-10-08 18:44 GMT+02:00 Andres Freund :
>>> >
>>> > > Hi,
>>> > >
>>> > > On 2017-10-08 18:36:23 +0200, Pavel Stehule wrote:
>>> > > > 2. Lot of used tables are pretty wide - 60, 120, .. columns
>>> > > >
>>> > > > Now, I am doing profiling, and I see so most time is related to
>>> > > >
>>> > > > ExecTypeFromTLInternal(List *targetList, bool hasoid, bool
>>> skipjunk)
>>> > >
>>> > > Yea, that's known - I've complained about this a couple times. You
>>> could
>>> > > try whether the following master branch helps:
>>> > > https://git.postgresql.org/gitweb/?p=users/andresfreund/
>>> > > postgres.git;a=shortlog;h=refs/heads/simple_statement_perf
>>> > >
>>> > > That's just micro-optimization though, not a more fundamental
>>> > > solution. But for me it yields pretty nice speedups for cases with
>>> long
>>> > > tlists.
>>> > >
>>> > >
>>> > it is just this patch
>>> >
>>> > HeapTuple   tup;
>>> > Form_pg_type typTup;
>>> >
>>> > +   if (typid < FirstBootstrapObjectId)
>>> > +   break;
>>> > +
>>> > tup = SearchSysCache1(TYPEOID, ObjectIdGetDatum(typid));
>>> > if (!HeapTupleIsValid(tup))
>>> > elog(ERROR, "cache lookup failed for type %u", typid);
>>>
>>> No.
>>>
>>
>> please, how I can clone your repo?
>>
>>
>> I found it
>
>

With your branch the execution is about 15-20% faster - so overhead of
exec  init is  more significant.

Unfortunately Oracle is significantly faster for this pattern

Regards

Pavel

Re: [HACKERS] is possible cache tupledesc templates in execution plan? significant performance issue, maybe bug?

[Pavel Stehule]

2017-10-08 19:04 GMT+02:00 Pavel Stehule :

>
>
> 2017-10-08 18:59 GMT+02:00 Andres Freund :
>
>> On 2017-10-08 18:57:28 +0200, Pavel Stehule wrote:
>> > 2017-10-08 18:44 GMT+02:00 Andres Freund :
>> >
>> > > Hi,
>> > >
>> > > On 2017-10-08 18:36:23 +0200, Pavel Stehule wrote:
>> > > > 2. Lot of used tables are pretty wide - 60, 120, .. columns
>> > > >
>> > > > Now, I am doing profiling, and I see so most time is related to
>> > > >
>> > > > ExecTypeFromTLInternal(List *targetList, bool hasoid, bool skipjunk)
>> > >
>> > > Yea, that's known - I've complained about this a couple times. You
>> could
>> > > try whether the following master branch helps:
>> > > https://git.postgresql.org/gitweb/?p=users/andresfreund/
>> > > postgres.git;a=shortlog;h=refs/heads/simple_statement_perf
>> > >
>> > > That's just micro-optimization though, not a more fundamental
>> > > solution. But for me it yields pretty nice speedups for cases with
>> long
>> > > tlists.
>> > >
>> > >
>> > it is just this patch
>> >
>> > HeapTuple   tup;
>> > Form_pg_type typTup;
>> >
>> > +   if (typid < FirstBootstrapObjectId)
>> > +   break;
>> > +
>> > tup = SearchSysCache1(TYPEOID, ObjectIdGetDatum(typid));
>> > if (!HeapTupleIsValid(tup))
>> > elog(ERROR, "cache lookup failed for type %u", typid);
>>
>> No.
>>
>
> please, how I can clone your repo?
>
>
> I found it

Re: [HACKERS] is possible cache tupledesc templates in execution plan? significant performance issue, maybe bug?

[Pavel Stehule]

2017-10-08 18:59 GMT+02:00 Andres Freund :

> On 2017-10-08 18:57:28 +0200, Pavel Stehule wrote:
> > 2017-10-08 18:44 GMT+02:00 Andres Freund :
> >
> > > Hi,
> > >
> > > On 2017-10-08 18:36:23 +0200, Pavel Stehule wrote:
> > > > 2. Lot of used tables are pretty wide - 60, 120, .. columns
> > > >
> > > > Now, I am doing profiling, and I see so most time is related to
> > > >
> > > > ExecTypeFromTLInternal(List *targetList, bool hasoid, bool skipjunk)
> > >
> > > Yea, that's known - I've complained about this a couple times. You
> could
> > > try whether the following master branch helps:
> > > https://git.postgresql.org/gitweb/?p=users/andresfreund/
> > > postgres.git;a=shortlog;h=refs/heads/simple_statement_perf
> > >
> > > That's just micro-optimization though, not a more fundamental
> > > solution. But for me it yields pretty nice speedups for cases with long
> > > tlists.
> > >
> > >
> > it is just this patch
> >
> > HeapTuple   tup;
> > Form_pg_type typTup;
> >
> > +   if (typid < FirstBootstrapObjectId)
> > +   break;
> > +
> > tup = SearchSysCache1(TYPEOID, ObjectIdGetDatum(typid));
> > if (!HeapTupleIsValid(tup))
> > elog(ERROR, "cache lookup failed for type %u", typid);
>
> No.
>

please, how I can clone your repo?

Re: [HACKERS] is possible cache tupledesc templates in execution plan? significant performance issue, maybe bug?

[Andres Freund]

On 2017-10-08 18:57:28 +0200, Pavel Stehule wrote:
> 2017-10-08 18:44 GMT+02:00 Andres Freund :
> 
> > Hi,
> >
> > On 2017-10-08 18:36:23 +0200, Pavel Stehule wrote:
> > > 2. Lot of used tables are pretty wide - 60, 120, .. columns
> > >
> > > Now, I am doing profiling, and I see so most time is related to
> > >
> > > ExecTypeFromTLInternal(List *targetList, bool hasoid, bool skipjunk)
> >
> > Yea, that's known - I've complained about this a couple times. You could
> > try whether the following master branch helps:
> > https://git.postgresql.org/gitweb/?p=users/andresfreund/
> > postgres.git;a=shortlog;h=refs/heads/simple_statement_perf
> >
> > That's just micro-optimization though, not a more fundamental
> > solution. But for me it yields pretty nice speedups for cases with long
> > tlists.
> >
> >
> it is just this patch
> 
> HeapTuple   tup;
> Form_pg_type typTup;
> 
> +   if (typid < FirstBootstrapObjectId)
> +   break;
> +
> tup = SearchSysCache1(TYPEOID, ObjectIdGetDatum(typid));
> if (!HeapTupleIsValid(tup))
> elog(ERROR, "cache lookup failed for type %u", typid);

No.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] is possible cache tupledesc templates in execution plan? significant performance issue, maybe bug?

[Pavel Stehule]

2017-10-08 18:44 GMT+02:00 Andres Freund :

> Hi,
>
> On 2017-10-08 18:36:23 +0200, Pavel Stehule wrote:
> > 2. Lot of used tables are pretty wide - 60, 120, .. columns
> >
> > Now, I am doing profiling, and I see so most time is related to
> >
> > ExecTypeFromTLInternal(List *targetList, bool hasoid, bool skipjunk)
>
> Yea, that's known - I've complained about this a couple times. You could
> try whether the following master branch helps:
> https://git.postgresql.org/gitweb/?p=users/andresfreund/
> postgres.git;a=shortlog;h=refs/heads/simple_statement_perf
>
> That's just micro-optimization though, not a more fundamental
> solution. But for me it yields pretty nice speedups for cases with long
> tlists.
>
>
it is just this patch

HeapTuple   tup;
Form_pg_type typTup;

+   if (typid < FirstBootstrapObjectId)
+   break;
+
tup = SearchSysCache1(TYPEOID, ObjectIdGetDatum(typid));
if (!HeapTupleIsValid(tup))
elog(ERROR, "cache lookup failed for type %u", typid);

?



>
> > This function is executed in exec init time - in this case pretty often.
> > Although there are used few columns from the table, the target list is
> > build for columns (maybe it is bug)
>
> It's probably just the physical tlist "optimization".
>
>
> > 2. If is not possible to reduce the number of fields of target list, is
> > possible to store tupledesc template to plan?
>
> We should do that, but it's not a small change.
>
> Greetings,
>
> Andres Freund
>

Re: [HACKERS] is possible cache tupledesc templates in execution plan? significant performance issue, maybe bug?

[Andres Freund]

Hi,

On 2017-10-08 18:36:23 +0200, Pavel Stehule wrote:
> 2. Lot of used tables are pretty wide - 60, 120, .. columns
> 
> Now, I am doing profiling, and I see so most time is related to
> 
> ExecTypeFromTLInternal(List *targetList, bool hasoid, bool skipjunk)

Yea, that's known - I've complained about this a couple times. You could
try whether the following master branch helps:
https://git.postgresql.org/gitweb/?p=users/andresfreund/postgres.git;a=shortlog;h=refs/heads/simple_statement_perf

That's just micro-optimization though, not a more fundamental
solution. But for me it yields pretty nice speedups for cases with long
tlists.


> This function is executed in exec init time - in this case pretty often.
> Although there are used few columns from the table, the target list is
> build for columns (maybe it is bug)

It's probably just the physical tlist "optimization".


> 2. If is not possible to reduce the number of fields of target list, is
> possible to store tupledesc template to plan?

We should do that, but it's not a small change.

Greetings,

Andres Freund


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers