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

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

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

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

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%

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

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

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

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

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

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

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

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

[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

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

[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

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

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

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

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

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

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

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

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