Re: [HACKERS] On markers of changed data

2017-10-08 Thread Andrey Borodin

> 8 окт. 2017 г., в 20:11, Stephen Frost  написал(а):
> * Andrey Borodin (x4...@yandex-team.ru) wrote:
>> But my other question still seems unanswered: can I use LSN logic for 
>> incrementing FSM and VM? Seems like most of the time there is valid LSN
> 
> I haven't gone and audited it myself, but I would certainly expect you
> to be able to use the LSN for everything which is WAL'd.  If you have
> cases where that's not the case, it'd be useful to see them.

Thanks, Stephen, this actually pointed what to look for
VM is WAL-logged [0]
FSM is not [1]

Now I have everything I wanted, and go back coding :)

Best regards, Andrey Borodin.

[0] 
https://github.com/postgres/postgres/blob/113b0045e20d40f726a0a30e33214455e4f1385e/src/backend/access/heap/visibilitymap.c#L315
[1] 
https://github.com/postgres/postgres/blob/1d25779284fe1ba08ecd57e647292a9deb241376/src/backend/storage/freespace/freespace.c#L593

-- 
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] Discussion on missing optimizations

2017-10-08 Thread David Rowley
On 9 October 2017 at 17:41, David Rowley  wrote:
> Thoughts?

Actually, I was a little inconsistent with my List NULL/NIL checks in
that last one.

I've attached an updated patch.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


remove_left_join_distinct_v2.patch
Description: Binary data

-- 
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] Discussion on missing optimizations

2017-10-08 Thread David Rowley
On 7 October 2017 at 14:48, Andres Freund  wrote:
> 3. JOIN Elimination
>
> There's been a lot of discussion and several patches. There's a bunch of
> problems here, one being that there's cases (during trigger firing,
> before the constraint checks) where foreign keys don't hold true, so we
> can't quite generally make these optimization.  Another concern is
> whether the added plan time is actually worthwhile.

I looked over this and it seems there's some pretty low hanging fruit
in there that we can add with just a handful of lines of new code.

This is the case for LEFT JOINs with a DISTINCT clause. Normally we
can only remove an unused LEFT JOINed relation if there are some
unique properties that ensure that the join does not duplicate any
outer row. We don't need to worry about this when there's a DISTINCT
clause, as the DISTINCT would remove any duplicates anyway. If I'm not
mistaken, we also don't need to bother looking at the actual distinct
clause's exprs since we'll already know that nothing is in there
regarding the relation being removed. The benefit to this could be
two-fold, as 1) we don't need to join to the unused relation and 2) we
don't need to remove any row duplication caused by the join.

While someone might argue that this is not going to be that common a
case to hit, if we consider how cheap this is to implement, it does
seem to be worth doing a couple of NULL checks in the planner for it.

The only slight downside I can see to this is that we're letting a few
more cases through rel_supports_distinctness() which is also used for
unique joins, and these proofs are not valid in those. However, it may
not be worth the trouble doing anything about that as relations
without unique indexes are pretty uncommon (at least in my world).

Thoughts?

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


remove_left_join_distinct.patch
Description: Binary data

-- 
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?

2017-10-08 Thread 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] Discussion on missing optimizations

2017-10-08 Thread Andres Freund
On 2017-10-08 17:11:44 -0400, Tom Lane wrote:
> Andres Freund  writes:
> > On 2017-10-08 11:28:09 -0400, Tom Lane wrote:
> >> https://commitfest.postgresql.org/15/1001/
> >> The reason that's not in v10 is we haven't been able to convince
> >> ourselves whether it's 100% correct.
> 
> > Unfortunately it won't help in this specific case (no support for UNION,
> > just UNION ALL), but I thought it might be interesting to reference
> > https://medium.com/@uwdb/introducing-cosette-527898504bd6
> > here.
> 
> Huh, that is an interesting project indeed.  Although I'm not sure that
> it quite addresses the question of whether an optimization transform
> is valid.  IIUC, it could prove that a particular query having been fed
> through the transform didn't change semantics, but that offers only
> limited insight into whether some other query fed through the transform
> might change.

According to the guide it offers some support for more general
transformations:
http://cosette.cs.washington.edu/guide#24-symbolic-predicates That's
still only going to be sufficient for some of the interesting cases, but
still...

Wonder about pinging them about the OR -> UNION case, they've been
responsive to problem in some threads I found online.

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


Re: [HACKERS] Discussion on missing optimizations

2017-10-08 Thread Tom Lane
Andres Freund  writes:
> On 2017-10-08 11:28:09 -0400, Tom Lane wrote:
>> https://commitfest.postgresql.org/15/1001/
>> The reason that's not in v10 is we haven't been able to convince
>> ourselves whether it's 100% correct.

> Unfortunately it won't help in this specific case (no support for UNION,
> just UNION ALL), but I thought it might be interesting to reference
> https://medium.com/@uwdb/introducing-cosette-527898504bd6
> here.

Huh, that is an interesting project indeed.  Although I'm not sure that
it quite addresses the question of whether an optimization transform
is valid.  IIUC, it could prove that a particular query having been fed
through the transform didn't change semantics, but that offers only
limited insight into whether some other query fed through the transform
might change.

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


Re: [HACKERS] search path security issue?

2017-10-08 Thread Joe Conway
On 10/06/2017 12:52 AM, Magnus Hagander wrote:
> It would be a nice feature to have in general, like a "basic guc
> permissions" thing. At least allowing a superuser to prevent exactly
> this. You could argue the same thing for example for memory parameters
> and such. We have no permissions at all when it comes to userset gucs
> today -- and of course, if something should be added about this, it
> should be done in a way that works for all the userset variables, not
> just search_path. 

+1

I have wished for exactly that more than once before.

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature


Re: [HACKERS] [COMMITTERS] pgsql: Fix freezing of a dead HOT-updated tuple

2017-10-08 Thread Peter Geoghegan
On Sat, Oct 7, 2017 at 4:25 PM, Alvaro Herrera  wrote:
> Hmm, I think I added a random sleep (max. 100ms) right after the
> HeapTupleSatisfiesVacuum call in vacuumlazy.c (lazy_scan_heap), and that
> makes the race easier to hit.

I still cannot reproduce. Perhaps you can be more specific?

-- 
Peter Geoghegan


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


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

2017-10-08 Thread Pavel Stehule
The following workaround is working
>
> create view  as  select CISLOEXEKUCE, MT.ID_NAJDATSPLT
>   from najzalobpr MT, najvzallok A1,
> NAJZALOBST A2, NAJZALOBCE A3 where
> MT.ID_NAJVZALLOK= A1.ID_NAJVZALLOK AND
> A1.ID_NAJZALOBST=A2.ID_NAJZALOBST AND
> A2.ID_NAJZALOBCE= A3.ID_NAJZALOBCE;
>
>
> and function should be changed to
>
> BEGIN
>BEGIN
> select CISLOEXEKUCE INTO STRICT mADRA
>   from 
>   WHERE id_najdatsplt = mID_najdatsplt LIMIT 1;
> EXCEPTION
>   WHEN OTHERS THEN
> mADRA := NULL;
> END;
>
>
>
>   Result:=mADRA;
>   return(Result);
> end;
>
> So this issue is really related to tupleDesc management
>

I found a bug in this workaround. It doesn't work



>
>
>> Pavel
>>
>>
>>
>


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

2017-10-08 Thread 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?

2017-10-08 Thread 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?

2017-10-08 Thread 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?

2017-10-08 Thread 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
>


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

2017-10-08 Thread Pavel Stehule
2017-10-08 18:36 GMT+02:00 Pavel Stehule :

> Hi
>
> I am looking why some queries are significantly slower on PostgreSQL than
> on Oracle, although there is pretty simple plan. The queries are usually
> 10x times slower on Postgres than on Oracle.
>
> I migrate old Oracle application to Postgres. There are important two
> factors:
>
> 1. Often usage of "view" functions (I don't know better terminology) like:
>
> CREATE OR REPLACE FUNCTION foo(_id integer)
> RETURNS text AS $$
> BEGIN
>   RETURN (SELECT xx FROM a JOIN b ON a.id = b.id WHERE b.y = _id)
> END;
> $$ LANGUAGE plpgsql;
>
> These functions are used in views
>
> CREATE VIEW xx AS
> SELECT a, b, c, foo(id) as d, ...
>
> And sometimes are used in filters
>
> SELECT * FROM xx WHERE d IN NOT NULL;
>
> 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)
>
> 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)
>
> I have a function
>
> CREATE OR REPLACE FUNCTION ides_funcs.najdatsplt_cislo_exekuce(mid_najdatsplt
> bigint)
>  RETURNS character varying
>  LANGUAGE plpgsql
>  STABLE SECURITY DEFINER COST 1000
> AS $function$
> DECLARE
>
>  Result varchar(200);
>
>   --mZALOBCISLO NAJZALOBCE.ZALOBCISLO%TYPE;
>   mAdra varchar(200);
>
>
> BEGIN
>BEGIN
> -- there are only tables
>  select CISLOEXEKUCE INTO STRICT mADRA
>   from najzalobpr MT, najvzallok A1,
> NAJZALOBST A2, NAJZALOBCE A3 where
> MT.ID_NAJVZALLOK= A1.ID_NAJVZALLOK AND
> A1.ID_NAJZALOBST=A2.ID_NAJZALOBST AND
> A2.ID_NAJZALOBCE= A3.ID_NAJZALOBCE AND
> MT.ID_NAJDATSPLT = mID_NAJDATSPLT  LIMIT 1;
> EXCEPTION
>   WHEN OTHERS THEN
> mADRA := NULL;
> END;
>
>
>
>   Result:=mADRA;
>   return(Result);
> end;
> $function$
>
> where is necessary only few columns:
>
> but it processing target list of length
>
> NOTICE:  plpgsql_exec_function: ides_funcs.najdatsplt_cislo_
> exekuce(bigint)
> NOTICE:  >>len: 38, hasoid: 0, skipjunk: 0
> NOTICE:  >>len: 21, hasoid: 0, skipjunk: 0
> NOTICE:  >>len: 1, hasoid: 0, skipjunk: 0
> NOTICE:  >>len: 65, hasoid: 0, skipjunk: 0
> NOTICE:  >>len: 1, hasoid: 0, skipjunk: 0
> NOTICE:  >>len: 93, hasoid: 0, skipjunk: 0
> NOTICE:  >>len: 1, hasoid: 0, skipjunk: 0
> NOTICE:  >>len: 1, hasoid: 0, skipjunk: 0
>
> len is length of targetlist
>
> The numbers are related to number of columns of tables najzalobpr,
> najvzallok, NAJZALOBST, ..
>
> Because these tables are wide, then the queries are too slow
>
> So, my questions?
>
> 1. Why target list is too long in this case. It should be reduced to few
> fields?
>
> 2. If is not possible to reduce the number of fields of target list, is
> possible to store tupledesc template to plan?
>
> Without this issue, the Postgres has same speed or is faster than Ora.
>
> I can send a schema by some private channel.
>
> Regards
>
>
The following workaround is working

create view  as  select CISLOEXEKUCE, MT.ID_NAJDATSPLT
  from najzalobpr MT, najvzallok A1,
NAJZALOBST A2, NAJZALOBCE A3 where
MT.ID_NAJVZALLOK= A1.ID_NAJVZALLOK AND
A1.ID_NAJZALOBST=A2.ID_NAJZALOBST AND
A2.ID_NAJZALOBCE= A3.ID_NAJZALOBCE;


and function should be changed to

BEGIN
   BEGIN
select CISLOEXEKUCE INTO STRICT mADRA
  from 
  WHERE id_najdatsplt = mID_najdatsplt LIMIT 1;
EXCEPTION
  WHEN OTHERS THEN
mADRA := NULL;
END;



  Result:=mADRA;
  return(Result);
end;

So this issue is really related to tupleDesc management



> Pavel
>
>
>


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

2017-10-08 Thread 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


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

2017-10-08 Thread Pavel Stehule
Hi

I am looking why some queries are significantly slower on PostgreSQL than
on Oracle, although there is pretty simple plan. The queries are usually
10x times slower on Postgres than on Oracle.

I migrate old Oracle application to Postgres. There are important two
factors:

1. Often usage of "view" functions (I don't know better terminology) like:

CREATE OR REPLACE FUNCTION foo(_id integer)
RETURNS text AS $$
BEGIN
  RETURN (SELECT xx FROM a JOIN b ON a.id = b.id WHERE b.y = _id)
END;
$$ LANGUAGE plpgsql;

These functions are used in views

CREATE VIEW xx AS
SELECT a, b, c, foo(id) as d, ...

And sometimes are used in filters

SELECT * FROM xx WHERE d IN NOT NULL;

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)

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)

I have a function

CREATE OR REPLACE FUNCTION
ides_funcs.najdatsplt_cislo_exekuce(mid_najdatsplt bigint)
 RETURNS character varying
 LANGUAGE plpgsql
 STABLE SECURITY DEFINER COST 1000
AS $function$
DECLARE

 Result varchar(200);

  --mZALOBCISLO NAJZALOBCE.ZALOBCISLO%TYPE;
  mAdra varchar(200);


BEGIN
   BEGIN
-- there are only tables
 select CISLOEXEKUCE INTO STRICT mADRA
  from najzalobpr MT, najvzallok A1,
NAJZALOBST A2, NAJZALOBCE A3 where
MT.ID_NAJVZALLOK= A1.ID_NAJVZALLOK AND
A1.ID_NAJZALOBST=A2.ID_NAJZALOBST AND
A2.ID_NAJZALOBCE= A3.ID_NAJZALOBCE AND
MT.ID_NAJDATSPLT = mID_NAJDATSPLT  LIMIT 1;
EXCEPTION
  WHEN OTHERS THEN
mADRA := NULL;
END;



  Result:=mADRA;
  return(Result);
end;
$function$

where is necessary only few columns:

but it processing target list of length

NOTICE:  plpgsql_exec_function: ides_funcs.najdatsplt_cislo_exekuce(bigint)
NOTICE:  >>len: 38, hasoid: 0, skipjunk: 0
NOTICE:  >>len: 21, hasoid: 0, skipjunk: 0
NOTICE:  >>len: 1, hasoid: 0, skipjunk: 0
NOTICE:  >>len: 65, hasoid: 0, skipjunk: 0
NOTICE:  >>len: 1, hasoid: 0, skipjunk: 0
NOTICE:  >>len: 93, hasoid: 0, skipjunk: 0
NOTICE:  >>len: 1, hasoid: 0, skipjunk: 0
NOTICE:  >>len: 1, hasoid: 0, skipjunk: 0

len is length of targetlist

The numbers are related to number of columns of tables najzalobpr,
najvzallok, NAJZALOBST, ..

Because these tables are wide, then the queries are too slow

So, my questions?

1. Why target list is too long in this case. It should be reduced to few
fields?

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

Without this issue, the Postgres has same speed or is faster than Ora.

I can send a schema by some private channel.

Regards

Pavel


Re: [HACKERS] Discussion on missing optimizations

2017-10-08 Thread Tom Lane
Petr Jelinek  writes:
> Okay, that makes sense, thanks for explanation. Your patch is the way to
> go then.

Hearing no further comment, pushed.  Thanks for reviewing it.

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


Re: [HACKERS] Discussion on missing optimizations

2017-10-08 Thread Andres Freund
On 2017-10-08 11:28:09 -0400, Tom Lane wrote:
> Adam Brusselback  writes:
> > On another note:
> >> turning ORs into UNIONs
> 
> > This is another one which would be incredibly useful for me.  I've had
> > to do this manually for performance reasons far too often.
> 
> Well, maybe you could sign up to help review the open patch for that then:
> https://commitfest.postgresql.org/15/1001/
> 
> The reason that's not in v10 is we haven't been able to convince
> ourselves whether it's 100% correct.

Unfortunately it won't help in this specific case (no support for UNION,
just UNION ALL), but I thought it might be interesting to reference
https://medium.com/@uwdb/introducing-cosette-527898504bd6
here.

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


Re: [HACKERS] Discussion on missing optimizations

2017-10-08 Thread Tom Lane
Adam Brusselback  writes:
> On another note:
>> turning ORs into UNIONs

> This is another one which would be incredibly useful for me.  I've had
> to do this manually for performance reasons far too often.

Well, maybe you could sign up to help review the open patch for that then:
https://commitfest.postgresql.org/15/1001/

The reason that's not in v10 is we haven't been able to convince
ourselves whether it's 100% correct.

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


Re: [HACKERS] Discussion on missing optimizations

2017-10-08 Thread Adam Brusselback
> I can't get very excited about this one either, though I do believe it
> can arise as the author says, "when you build complex views and JOIN
> them to each other".  Maybe I'm not excited about it because I've not
> needed it :)

This is one that I know would help with my database.  There is a ton
of logic stored in views,
which get joined to to the original table to filter the set rather
than imposing that set of
conditions in every separate query.

It would be really nice if the optimizer could simplify those to
eliminate the self join.  It's almost always
on the primary key of a table that the join would happen on, and if
not it'd be a non-nullable column for sure.


On another note:
> turning ORs into UNIONs
This is another one which would be incredibly useful for me.  I've had
to do this manually for performance
reasons far too often.


> Partially agreed. A comment to the article also mentions that some other
> database performs more optimizations depending on the cost of the
> plan. That's not easy to do in our current plan structure, but I think
> it's quite a worthwhile concept.

I would love to see this in Postgres.  It would allow the planner to
not waste cycles unnecessarily on
queries where it's just not needed, and to potentially spend a few
more cycles planning on very
costly queries to save a ton while executing.


-- 
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] On markers of changed data

2017-10-08 Thread Stephen Frost
Andrey,

* Andrey Borodin (x4...@yandex-team.ru) wrote:
> But my other question still seems unanswered: can I use LSN logic for 
> incrementing FSM and VM? Seems like most of the time there is valid LSN

I haven't gone and audited it myself, but I would certainly expect you
to be able to use the LSN for everything which is WAL'd.  If you have
cases where that's not the case, it'd be useful to see them.

Thanks!

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] Help required to debug pg_repack breaking logical replication

2017-10-08 Thread Craig Ringer
On 8 October 2017 at 02:37, Daniele Varrazzo  wrote:
> Hello,
>
> we have been reported, and I have experienced a couple of times,
> pg_repack breaking logical replication.
>
> - https://github.com/reorg/pg_repack/issues/135
> - https://github.com/2ndQuadrant/pglogical/issues/113

Yeah, I was going to say I've seen reports of this with pglogical, but
I see you've linked to them.

I haven't had a chance to look into it though, and haven't had a
suitable reproducible test case.

> In the above issue #113, Petr Jelinek commented:
>
>> From quick look at pg_repack, the way it does table rewrite is almost 
>> guaranteed
>> to break logical decoding unless there is zero unconsumed changes for a 
>> given table
>> as it does not build the necessary mappings info for logical decoding that 
>> standard
>> heap rewrite in postgres does.
>
> unfortunately he didn't follow up to further details requests.

At a guess he's referring to src/backend/access/heap/rewriteheap.c .

I'd explain better if I understood what was going on myself, but I
haven't really understood the logical decoding parts of that code.

> - Is Petr diagnosis right and freezing of logical replication is to be
> blamed to missing mapping?
> - Can you suggest a test to reproduce the issue reliably?
> - What are mapped relations anyway?

I can't immediately give you the answers you seek, but start by
studying src/backend/access/heap/rewriteheap.c . Notably
logical_end_heap_rewrite, logical_rewrite_heap_tuple,
logical_begin_heap_rewrite.

At a wild "I haven't read any of the relevant code in detail yet" stab
in the dark, pg_repack is failing to do the bookkeeping required by
logical decoding around relfilenode changes, cmin/cmax, etc.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
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] Slow synchronous logical replication

2017-10-08 Thread Craig Ringer
On 8 October 2017 at 03:58, Konstantin Knizhnik
 wrote:

> The question was about logical replication mechanism in mainstream version
> of Postgres.

I think it'd be helpful if you provided reproduction instructions,
test programs, etc, making it very clear when things are / aren't
related to your changes.

> I think that most of people are using asynchronous logical replication and
> synchronous LR is something exotic and not well tested and investigated.
> It will be great if I am wrong:)

I doubt it's widely used. That said, a lot of people use synchronous
replication with BDR and pglogical, which are ancestors of the core
logical rep code and design.

I think you actually need to collect some proper timings and
diagnostics here, rather than hand-waving about it being "slow". A
good starting point might be setting some custom 'perf' tracepoints,
or adding some 'elog()'ing for timestamps. Then scrape the results and
build a latency graph.

That said, if I had to guess why it's slow, I'd say that you're facing
a number of factors:

* By default, logical replication in PostgreSQL does not do an
immediate flush to disk after downstream commit. In the interests of
faster apply performance it instead delays sending flush confirmations
until the next time WAL is flushed out. See the docs for CREATE
SUBSCRIPTION, notably the synchronous_commit option. This will
obviously greatly increase latencies on sync commit.

* Logical decoding doesn't *start* streaming a transaction until the
origin node finishes the xact and writes a COMMIT, then the xlogreader
picks it up.

* As a consequence of the above, a big xact holds up commit
confirmations of smaller ones by a LOT more than is the case for
streaming physical replication.

Hopefully that gives you something to look into, anyway. Maybe you'll
be inspired to work on parallelized logical decoding :)

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
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] On markers of changed data

2017-10-08 Thread Andrey Borodin
Tom, Alvaro, Michael, and especially Septhen, thank you for your valuable 
comments.

I feel enlightened about mtime.
My takeaway is:
1. Any other marker would be better (It can be WAL scan during archiving, some 
new LSN-based mechanics* et c.)
2. mtime could be used, with precautions described by Stephen are taken.

But my other question still seems unanswered: can I use LSN logic for 
incrementing FSM and VM? Seems like most of the time there is valid LSN


* I like the idea of using something for both incr(diff) backups and VACUUM, it 
worth thinking about.

Best regards, Andrey Borodin.



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