Re: [HACKERS] Small improvement to compactify_tuples

On Wed, Nov 8, 2017 at 12:33 PM, Tom Lane wrote: > Robert Haas writes: >> On Tue, Nov 7, 2017 at 4:39 PM, Tom Lane wrote: >>> What I'm getting from the standard pgbench measurements, on both machines, >>> is that this patch might be

Re: [HACKERS] Small improvement to compactify_tuples

On Tue, Nov 7, 2017 at 11:42 AM, Юрий Соколов <funny.fal...@gmail.com> wrote: > > > 2017-11-07 17:15 GMT+03:00 Claudio Freire <klaussfre...@gmail.com>: >> Aside from requiring all that include magic, if you place specialized >> sort functions in a reusa

Re: [HACKERS] Small improvement to compactify_tuples

On Mon, Nov 6, 2017 at 9:08 PM, Юрий Соколов <funny.fal...@gmail.com> wrote: > 2017-11-07 1:14 GMT+03:00 Claudio Freire <klaussfre...@gmail.com>: >> >> I haven't seen this trick used in postgres, nor do I know whether it >> would be well received, so this is

Re: [HACKERS] Small improvement to compactify_tuples

On Mon, Nov 6, 2017 at 6:58 PM, Юрий Соколов <funny.fal...@gmail.com> wrote: > > 2017-11-06 17:55 GMT+03:00 Claudio Freire <klaussfre...@gmail.com>: >> >> On Mon, Nov 6, 2017 at 11:50 AM, Юрий Соколов <funny.fal...@gmail.com> >> wrote: >> >&g

Re: [HACKERS] Small improvement to compactify_tuples

On Mon, Nov 6, 2017 at 11:50 AM, Юрий Соколов wrote: >> Maybe leave a fallback to qsort if some corner case produces big buckets? > > For 8kb pages, each bucket is per 32 bytes. So, for heap pages it is at > most 1 heap-tuple per bucket, and for index pages it is at most 2

Re: [HACKERS] Small improvement to compactify_tuples

gt; it seems to me that we need to have >> >> for (_i = off; _i < _n; _i += 1) \ >> >> or maybe just _i++. > > > Shame on me :-( > I've wrote shell sort several times, so I forgot to recheck myself once > again. > And looks like best

Re: [HACKERS] Small improvement to compactify_tuples

On Fri, Nov 3, 2017 at 4:30 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Claudio Freire <klaussfre...@gmail.com> writes: >> On Thu, Nov 2, 2017 at 11:46 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: >>> BTW, the originally given test case shows no measurable improvem

Re: [HACKERS] Small improvement to compactify_tuples

On Thu, Nov 2, 2017 at 11:46 PM, Tom Lane wrote: > Sokolov Yura writes: >> [ 0001-Improve-compactify_tuples.patch, v5 or thereabouts ] > > I started to review this patch. I spent a fair amount of time on > beautifying the code, because I found it

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Sun, Oct 1, 2017 at 8:36 PM, Daniel Gustafsson <dan...@yesql.se> wrote: >> On 18 Aug 2017, at 13:39, Claudio Freire <klaussfre...@gmail.com> wrote: >> >> On Fri, Apr 7, 2017 at 10:51 PM, Claudio Freire <klaussfre...@gmail.com> >> wrote: >>> I

Re: [HACKERS] Small improvement to compactify_tuples

On Sat, Sep 23, 2017 at 5:56 AM, Sokolov Yura <funny.fal...@postgrespro.ru> wrote: > Hello, Claudio. > > Thank you for review and confirm of improvement. > > > On 2017-09-23 01:12, Claudio Freire wrote: >> >> >> Patch 1 applies cleanly, builds, and

Re: [HACKERS] Small improvement to compactify_tuples

On Tue, Sep 12, 2017 at 12:49 PM, Sokolov Yura wrote: > On 2017-07-21 13:49, Sokolov Yura wrote: >> >> On 2017-05-17 17:46, Sokolov Yura wrote: >>> >>> Alvaro Herrera писал 2017-05-15 20:13: As I understand, these patches are logically separate, so putting

Re: [HACKERS] GUC for cleanup indexes threshold.

On Fri, Sep 22, 2017 at 4:46 AM, Kyotaro HORIGUCHI <horiguchi.kyot...@lab.ntt.co.jp> wrote: > I apologize in advance of possible silliness. > > At Thu, 21 Sep 2017 13:54:01 -0300, Claudio Freire <klaussfre...@gmail.com> > wrote in

Re: [HACKERS] GUC for cleanup indexes threshold.

On Tue, Sep 19, 2017 at 8:55 PM, Peter Geoghegan <p...@bowt.ie> wrote: > On Tue, Sep 19, 2017 at 4:47 PM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> Maybe this is looking at the problem from the wrong direction. >> >> Why can't the page be added

Re: [HACKERS] GUC for cleanup indexes threshold.

On Tue, Sep 19, 2017 at 3:31 AM, Kyotaro HORIGUCHI wrote: > I was just looking the thread since it is found left alone for a > long time in the CF app. > > At Mon, 18 Sep 2017 16:35:58 -0700, Peter Geoghegan wrote in >

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Fri, Apr 7, 2017 at 10:51 PM, Claudio Freire <klaussfre...@gmail.com> wrote: > Indeed they do, and that's what motivated this patch. But I'd need > TB-sized tables to set up something like that. I don't have the > hardware or time available to do that (vacuum on bloated TB-siz

Re: In-place index updates and HOT (Was: [HACKERS] Patch: Write Amplification Reduction Method (WARM))

On Fri, Jul 28, 2017 at 8:32 PM, Peter Geoghegan wrote: > README.HOT says that that cost is not worth the benefit of > preventing a new index write, but I think that it ought to take into > account that not all index writes are equal. There is an appreciable > difference between

[HACKERS] [PATCH] Vacuum: Update FSM more frequently

major refactoring of the feature. So, patch attached. I'll add it to the next CF as well. From 791b9d2006b5abd67a8efb3f7c6cc99141ddbb09 Mon Sep 17 00:00:00 2001 From: Claudio Freire <klaussfre...@gmail.com> Date: Fri, 28 Jul 2017 21:31:59 -0300 Subject: [PATCH] Vacuum: Update FSM more frequently

Re: [HACKERS] Increase Vacuum ring buffer.

On Thu, Jul 27, 2017 at 2:10 PM, Alvaro Herrera <alvhe...@2ndquadrant.com> wrote: > Claudio Freire wrote: >> On Thu, Jul 27, 2017 at 1:46 PM, Alvaro Herrera >> <alvhe...@2ndquadrant.com> wrote: >> > Claudio Freire wrote: >> > >> >> > The

Re: [HACKERS] Increase Vacuum ring buffer.

On Thu, Jul 27, 2017 at 1:46 PM, Alvaro Herrera <alvhe...@2ndquadrant.com> wrote: > Claudio Freire wrote: > >> > The vacuuming the very large table with no index could also take a >> > long time, and it scans and vacuums blocks one by one. So I imagined >&g

Re: [HACKERS] Increase Vacuum ring buffer.

On Thu, Jul 27, 2017 at 6:16 AM, Masahiko Sawada <sawada.m...@gmail.com> wrote: > On Thu, Jul 27, 2017 at 5:48 PM, Sokolov Yura > <funny.fal...@postgrespro.ru> wrote: >> On 2017-07-27 11:30, Masahiko Sawada wrote: >>> >>> On Tue, Jul 25, 2017 at 2:27

Re: [HACKERS] Increase Vacuum ring buffer.

On Wed, Jul 26, 2017 at 1:39 PM, Sokolov Yura wrote: > On 2017-07-24 12:41, Sokolov Yura wrote: > test_master_1/pretty.log ... > time activity tps latency stddev min max > 11130 av+ch 198198ms374ms 7ms 1956ms > 11160

Re: [HACKERS] [WIP] [B-Tree] Keep indexes sorted by heap physical location

On Mon, Jul 24, 2017 at 2:43 PM, Peter Geoghegan <p...@bowt.ie> wrote: > On Mon, Jul 24, 2017 at 10:13 AM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> Vacuum *might* be able to redistribute tuples too, while holding locks >> to all 3 pages (the two childr

Re: [HACKERS] Increase Vacuum ring buffer.

On Mon, Jul 24, 2017 at 2:20 PM, Claudio Freire <klaussfre...@gmail.com> wrote: > On Mon, Jul 24, 2017 at 2:10 PM, Sokolov Yura > <funny.fal...@postgrespro.ru> wrote: >> On 2017-07-24 19:11, Claudio Freire wrote: >>> I was mostly thinking about something like t

Re: [HACKERS] Increase Vacuum ring buffer.

On Mon, Jul 24, 2017 at 2:10 PM, Sokolov Yura <funny.fal...@postgrespro.ru> wrote: > On 2017-07-24 19:11, Claudio Freire wrote: >> >> On Mon, Jul 24, 2017 at 6:37 AM, Sokolov Yura >> <funny.fal...@postgrespro.ru> wrote: >>> >>> Good day, Clau

Re: [HACKERS] [WIP] [B-Tree] Keep indexes sorted by heap physical location

On Mon, Jul 24, 2017 at 2:02 PM, Peter Geoghegan <p...@bowt.ie> wrote: > On Mon, Jul 24, 2017 at 9:51 AM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> My point was that the TID doesn't have to point to an actual tuple. >> >> It's more of a keyspace thi

Re: [HACKERS] [WIP] [B-Tree] Keep indexes sorted by heap physical location

On Wed, Nov 23, 2016 at 12:27 AM, Peter Geoghegan <p...@heroku.com> wrote: > On Mon, Nov 21, 2016 at 5:15 PM, Claudio Freire <klaussfre...@gmail.com> > wrote: >>> There are a couple >>> of tricky issues with that that you'd have to look out for, like >>

Re: [HACKERS] Increase Vacuum ring buffer.

On Mon, Jul 24, 2017 at 6:37 AM, Sokolov Yura <funny.fal...@postgrespro.ru> wrote: > Good day, Claudio > > > On 2017-07-22 00:27, Claudio Freire wrote: >> >> On Fri, Jul 21, 2017 at 2:41 PM, Sokolov Yura >> <funny.fal...@postgrespro.ru> wrote: >>>

Re: [HACKERS] [WIP] [B-Tree] Keep indexes sorted by heap physical location

On Fri, Jul 21, 2017 at 10:31 PM, Peter Geoghegan <p...@bowt.ie> wrote: > On Wed, Aug 17, 2016 at 7:54 PM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> The attached patch tries to maintain the initial status of B-Tree >> indexes, which are created with eq

Re: [HACKERS] Increase Vacuum ring buffer.

On Fri, Jul 21, 2017 at 2:41 PM, Sokolov Yura wrote: > > My friend noticed, that I didn't said why I bother with autovacuum. > Our customers suffers from table bloating. I've made synthetic > bloating test, and started experiments with modifying micro- and >

Re: [HACKERS] Increase Vacuum ring buffer.

On Thu, Jul 20, 2017 at 1:08 PM, Claudio Freire <klaussfre...@gmail.com> wrote: > So, the 64x increase may be justifiable in absolute terms: it's not > unlikely that a 16MB buffer will be evicted from the OS cache before > vacuum is done with it, even in heavily throttled

Re: [HACKERS] Increase Vacuum ring buffer.

On Thu, Jul 20, 2017 at 12:51 PM, Robert Haas <robertmh...@gmail.com> wrote: > On Thu, Jul 20, 2017 at 11:09 AM, Claudio Freire <klaussfre...@gmail.com> > wrote: >>> It's no secret that making the ring buffer larger will improve >>> performance -- in fact, n

Re: [HACKERS] Increase Vacuum ring buffer.

On Thu, Jul 20, 2017 at 11:59 AM, Robert Haas wrote: > On Tue, Jul 18, 2017 at 6:09 AM, Sokolov Yura > wrote: >> I investigated autovacuum performance, and found that it suffers a lot >> from small ring buffer. It suffers in a same way bulk

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

On Thu, Jul 20, 2017 at 12:08 AM, Peter Geoghegan wrote: >> The traditional >> wisdom about btrees, for instance, is that no matter how full you pack >> them to start with, the steady state is going to involve something like >> 1/3rd free space. You can call that bloat if you want,

Re: Fwd: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Wed, Jul 12, 2017 at 1:29 PM, Claudio Freire <klaussfre...@gmail.com> wrote: > On Wed, Jul 12, 2017 at 1:08 PM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> On Wed, Jul 12, 2017 at 11:48 AM, Alexey Chernyshov >> <a.chernys...@postgrespro.ru>

Re: Fwd: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Wed, Jul 12, 2017 at 1:08 PM, Claudio Freire <klaussfre...@gmail.com> wrote: > On Wed, Jul 12, 2017 at 11:48 AM, Alexey Chernyshov > <a.chernys...@postgrespro.ru> wrote: >> Thank you for the patch and benchmark results, I have a couple remarks. >> Firstly

Re: Fwd: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

s no need to do it manually, because the compiler will do this optimization > itself. I'll look into it. But my experience is that compilers won't align struct size like this, only attributes, and this attribute is composed of 16-bit attributes so it doesn't get aligned by default. >

Fwd: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

tation being better somehow, and a slowdown at scale 4000 in some variants. I believe this is due to those variants having highly clustered indexes. While the "shuf" (shuffled) variantes were intended to be the opposite of that, I suspect I somehow failed to get the desired outcome

Re: [HACKERS] RFC: Key normalization for nbtree

On Mon, Jul 10, 2017 at 3:40 PM, Peter Geoghegan wrote: > It might appear excessive to talk about several different techniques > in one place, but that seemed like the best way to me, because there > are subtle dependencies. If most of the optimizations are pursued as a > project

Re: [HACKERS] Perfomance bug in v10

On Fri, Jun 2, 2017 at 11:46 AM, Teodor Sigaev wrote: >> There were old threads about considering a risk factor when estimating >> plans, and I'm thinking this issue is the planner failing to do >> exactly that. >> > I'm afraid it's tool late for v10 Clearly -- Sent via

Re: [HACKERS] Perfomance bug in v10

On Fri, Jun 2, 2017 at 10:27 AM, Tom Lane wrote: > Teodor Sigaev writes: >>> Teodor, could you check if this patch fixes your real-world problem? > >> It works fine with original query, thank you. But some other query slowdowns >> for >> ~10% (9 secs vs 10

Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces

On Mon, May 29, 2017 at 3:53 PM, Bruce Momjian wrote: > Right now we don't document that temp_tablespaces can use > non-restart-safe storage, e.g. /tmp, ramdisks. Would this be safe? > Should we document this? I have set up things like that, but it's nontrivial. Just pointing

Re: [HACKERS] CTE inlining

On Wed, May 3, 2017 at 11:31 AM, David Fetter <da...@fetter.org> wrote: > On Wed, May 03, 2017 at 11:26:27AM -0300, Claudio Freire wrote: >> On Wed, May 3, 2017 at 2:19 AM, Craig Ringer <cr...@2ndquadrant.com> wrote: >> >> Or we will choose WITH MATE

Re: [HACKERS] CTE inlining

On Wed, May 3, 2017 at 2:19 AM, Craig Ringer wrote: >> Or we will choose WITH MATERIALIZE, and then the users aware of the fencing >> (and using the CTEs for that purpose) will have to modify the queries. But >> does adding MATERIALIZE quality as major query rewrite? > >

Re: [HACKERS] PG 10 release notes

On Tue, Apr 25, 2017 at 2:45 PM, Bruce Momjian wrote: > On Tue, Apr 25, 2017 at 10:37:48AM -0700, Andres Freund wrote: >> On 2017-04-25 13:11:32 -0400, Bruce Momjian wrote: >> > I don't think this warrants inclusion in the release notes for reasons >> > already discussed. The

Re: [HACKERS] PG 10 release notes

On Tue, Apr 25, 2017 at 2:11 PM, Bruce Momjian <br...@momjian.us> wrote: > On Tue, Apr 25, 2017 at 01:37:13PM -0300, Claudio Freire wrote: >> > I think it has been pretty common to accumulate a lot of such changes >> > into generic entries like, say, "speedu

Re: [HACKERS] PG 10 release notes

cate scan has been measured to be five times faster than without this patch (that was on a slow disk, but it shouldn't hurt on fast disks.) Author: Álvaro Herrera, loosely based on a submission by Claudio Freire Discussion: https://postgr.es/m/cagtbqpa6nfgo_6g_y_7zqx8l9gchdsqkydo1tguh

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Sun, Apr 23, 2017 at 12:41 PM, Robert Haas wrote: >> That's after inlining the compare on both the linear and sequential >> code, and it seems it lets the compiler optimize the binary search to >> the point where it outperforms the sequential search. >> >> That's not the

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Wed, Apr 12, 2017 at 4:35 PM, Robert Haas <robertmh...@gmail.com> wrote: > On Tue, Apr 11, 2017 at 4:38 PM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> In essence, the patch as it is proposed, doesn't *need* a binary >> search, because the segment list c

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Tue, Apr 11, 2017 at 4:17 PM, Robert Haas <robertmh...@gmail.com> wrote: > On Tue, Apr 11, 2017 at 2:59 PM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> On Tue, Apr 11, 2017 at 3:53 PM, Robert Haas <robertmh...@gmail.com> wrote: >>> 1TB / 8kB pe

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Tue, Apr 11, 2017 at 3:59 PM, Claudio Freire <klaussfre...@gmail.com> wrote: > On Tue, Apr 11, 2017 at 3:53 PM, Robert Haas <robertmh...@gmail.com> wrote: >> 1TB / 8kB per page * 60 tuples/page * 20% * 6 bytes/tuple = 9216MB of >> maintenance_work_mem >> >&

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Tue, Apr 11, 2017 at 3:53 PM, Robert Haas wrote: > 1TB / 8kB per page * 60 tuples/page * 20% * 6 bytes/tuple = 9216MB of > maintenance_work_mem > > So we'll allocate 128MB+256MB+512MB+1GB+2GB+4GB which won't be quite > enough so we'll allocate another 8GB, for a total of

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Fri, Apr 7, 2017 at 10:06 PM, Claudio Freire <klaussfre...@gmail.com> wrote: >>> >> + if (seg->num_dead_tuples >= seg->max_dead_tuples) >>> >> + { >>> >> + /* >>> >&g

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Fri, Apr 7, 2017 at 10:12 PM, Andres Freund <and...@anarazel.de> wrote: > On 2017-04-07 22:06:13 -0300, Claudio Freire wrote: >> On Fri, Apr 7, 2017 at 9:56 PM, Andres Freund <and...@anarazel.de> wrote: >> > Hi, >> > >> > >> > On 2017

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Fri, Apr 7, 2017 at 9:56 PM, Andres Freund <and...@anarazel.de> wrote: > Hi, > > > On 2017-04-07 19:43:39 -0300, Claudio Freire wrote: >> On Fri, Apr 7, 2017 at 5:05 PM, Andres Freund <and...@anarazel.de> wrote: >> > Hi, >> > >> > I've

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Fri, Apr 7, 2017 at 5:05 PM, Andres Freund <and...@anarazel.de> wrote: > Hi, > > I've *not* read the history of this thread. So I really might be > missing some context. > > >> From e37d29c26210a0f23cd2e9fe18a264312fecd383 Mon Sep 17 00:00:00 2001 >

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Fri, Apr 7, 2017 at 7:43 PM, Claudio Freire <klaussfre...@gmail.com> wrote: >>> + * Lookup in that structure proceeds sequentially in the list of segments, >>> + * and with a binary search within each segment. Since segment's size grows >>> + * exponentiall

Re: [HACKERS] Making clausesel.c Smarter

On Fri, Apr 7, 2017 at 2:28 AM, David Rowley wrote: >> + if (rqlist->hibound == DEFAULT_INEQ_SEL || rqlist->lobound == >> DEFAULT_INEQ_SEL) >> + { >> + /* No point in checking null selectivity, DEFAULT_INEQ_SEL >> implies we have no stats */ >> + s2 =

Re: [HACKERS] Making clausesel.c Smarter

On Tue, Apr 4, 2017 at 1:00 PM, Claudio Freire <klaussfre...@gmail.com> wrote: >>> If you ask me, I'd just leave: >>> >>> + if (rqlist->hibound == DEFAULT_INEQ_SEL || rqlist->lobound == >>> DEFAULT_INEQ_SEL) >>> + { >>> + /* N

Re: [HACKERS] Making clausesel.c Smarter

On Tue, Apr 4, 2017 at 8:21 AM, David Rowley <david.row...@2ndquadrant.com> wrote: > On 4 April 2017 at 13:35, Claudio Freire <klaussfre...@gmail.com> wrote: >> On Mon, Apr 3, 2017 at 9:19 PM, Claudio Freire <klaussfre...@gmail.com> >> wrote: >>> On

Re: [HACKERS] Making clausesel.c Smarter

On Tue, Apr 4, 2017 at 8:12 AM, David Rowley wrote: > Result Comparison > > Master median tps Patch median tps comparison > Test 1 6993.7 6714.3 104.16% > Test 2 7053.1 6921.6 101.90% > Test 3 5137.2 4954.2 103.69% > Test 4 27.1 19.4 139.72% > Test 5 54.1 51.4

Re: [HACKERS] Making clausesel.c Smarter

On Mon, Apr 3, 2017 at 9:19 PM, Claudio Freire <klaussfre...@gmail.com> wrote: > On Mon, Apr 3, 2017 at 8:52 PM, David Rowley > <david.row...@2ndquadrant.com> wrote: >>> One last observation: >>> >>> +/* >>> + * An IS NOT NULL

Re: [HACKERS] Making clausesel.c Smarter

On Mon, Apr 3, 2017 at 8:52 PM, David Rowley wrote: >> One last observation: >> >> +/* >> + * An IS NOT NULL test is a no-op if there's any other strict quals, >> + * so if that's the case, then we'll only apply this, otherwise >> we'll >> +

Re: [HACKERS] Making clausesel.c Smarter

On Mon, Apr 3, 2017 at 5:59 AM, David Rowley wrote: >> +static void addCachedSelectivityRangeVar(CachedSelectivityClause >> **cslist, Node *var, >> bool varonleft, bool isLTsel, Selectivity s2); >> >> You're changing clause -> var throughout the code

Re: [HACKERS] Making clausesel.c Smarter

On Mon, Apr 3, 2017 at 6:22 PM, David Rowley wrote: > On 4 April 2017 at 08:24, Andres Freund wrote: >> On 2017-04-03 20:59:42 +1200, David Rowley wrote: >>> Updated patch attached. >>> >>> Thanks for reviewing it. >> >> Given the time in the

Re: [HACKERS] Making clausesel.c Smarter

On Mon, Apr 3, 2017 at 5:24 PM, Andres Freund wrote: > On 2017-04-03 20:59:42 +1200, David Rowley wrote: >> Updated patch attached. >> >> Thanks for reviewing it. > > Given the time in the release cycle I'm afraid that this it's too late > to get this into v10. Does anybody

Re: [HACKERS] Making clausesel.c Smarter

On Fri, Feb 24, 2017 at 7:00 AM, David Rowley wrote: > I've stumbled over an interesting query out in the wild where the > query was testing a nullable timestamptz column was earlier than some > point in time, and also checking the column IS NOT NULL. > > The use

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Wed, Feb 1, 2017 at 7:55 PM, Claudio Freire <klaussfre...@gmail.com> wrote: > On Wed, Feb 1, 2017 at 6:13 PM, Masahiko Sawada <sawada.m...@gmail.com> wrote: >> On Wed, Feb 1, 2017 at 10:02 PM, Claudio Freire <klaussfre...@gmail.com> >> wrote: >>> On We

Re: ParallelFinish-hook of FDW/CSP (Re: [HACKERS] Steps inside ExecEndGather)

On Fri, Feb 24, 2017 at 1:24 PM, Robert Haas wrote: > On Fri, Feb 24, 2017 at 7:29 PM, Kohei KaiGai wrote: >> This attached patch re-designed the previous v2 according to Robert's >> comment. >> All I had to do is putting entrypoint for

Re: [HACKERS] Sum aggregate calculation for single precsion real

On Wed, Feb 15, 2017 at 9:52 AM, Robert Haas wrote: > On Tue, Feb 14, 2017 at 11:45 PM, Tom Lane wrote: >> You could perhaps make an argument that sum(float4) would have less risk >> of overflow if it accumulated in and returned float8, but frankly that

Re: [HACKERS] Improve OR conditions on joined columns (common star schema problem)

On Thu, Feb 9, 2017 at 9:50 PM, Jim Nasby wrote: > WHERE t1 IN ('a','b') OR t2 IN ('c','d') > > into > > WHERE f1 IN (1,2) OR f2 IN (3,4) > > (assuming a,b,c,d maps to 1,2,3,4) > > BTW, there's an important caveat here: users generally do NOT want duplicate > rows from

Re: ParallelFinish-hook of FDW/CSP (Re: [HACKERS] Steps inside ExecEndGather)

On Mon, Feb 6, 2017 at 1:42 AM, Kouhei Kaigai wrote: > I also had thought an idea to have extra space to Instrumentation structure, > however, it needs to make Instrumentation flexible-length structure according > to the custom format by CSP/FDW. Likely, it is not a good

Re: ParallelFinish-hook of FDW/CSP (Re: [HACKERS] Steps inside ExecEndGather)

On Mon, Feb 6, 2017 at 1:00 AM, Claudio Freire <klaussfre...@gmail.com> wrote: > On Sun, Feb 5, 2017 at 9:19 PM, Kouhei Kaigai <kai...@ak.jp.nec.com> wrote: >>> If the use case for this is to gather instrumentation, I'd suggest calling >>> the hook in Ret

Re: ParallelFinish-hook of FDW/CSP (Re: [HACKERS] Steps inside ExecEndGather)

On Sun, Feb 5, 2017 at 9:19 PM, Kouhei Kaigai wrote: >> If the use case for this is to gather instrumentation, I'd suggest calling >> the hook in RetrieveInstrumentation, and calling it appropriately. It would >> make the intended use far clearer than it is now. >> >> And if

Re: ParallelFinish-hook of FDW/CSP (Re: [HACKERS] Steps inside ExecEndGather)

On Mon, Oct 31, 2016 at 11:33 AM, Kouhei Kaigai wrote: > Hello, > > The attached patch implements the suggestion by Amit before. > > What I'm motivated is to collect extra run-time statistics specific > to a particular ForeignScan/CustomScan, not only the standard >

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Wed, Feb 1, 2017 at 6:13 PM, Masahiko Sawada <sawada.m...@gmail.com> wrote: > On Wed, Feb 1, 2017 at 10:02 PM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> On Wed, Feb 1, 2017 at 5:47 PM, Masahiko Sawada <sawada.m...@gmail.com> >> wrote:

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Wed, Feb 1, 2017 at 5:47 PM, Masahiko Sawada wrote: > Thank you for updating the patch. > > Whole patch looks good to me except for the following one comment. > This is the final comment from me. > > /* > * lazy_tid_reaped() -- is a particular tid deletable? > * > *

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

one that found the second bug in lazy_clear_dead_tuples, so clearly it's not without merit. I'll rearrange the comments as you ask though. Updated and rebased v7 attached. [1] https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=776671 From d32610b0ad6b9413aa4b2d808019d3c67d578f3c Mon Sep 17 00:00:00 2001 From: Claudio Freire <kl

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

); > > Because seg is always set later I think the first line starting with > "seg = ..." is not necessary. Thought? That's correct. Attached a v6 with those changes (and rebased). Make check still passes. From c89019089a71517befac0920f22ed75577dda6c6 Mon Sep 17 00:00:00 2001 From: Claudio Freire <klau

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Fri, Jan 20, 2017 at 6:24 AM, Masahiko Sawada <sawada.m...@gmail.com> wrote: > On Thu, Jan 19, 2017 at 8:31 PM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> On Thu, Jan 19, 2017 at 6:33 AM, Anastasia Lubennikova >> <a.lubennik...@postgrespro.ru>

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Thu, Jan 19, 2017 at 6:33 AM, Anastasia Lubennikova <a.lubennik...@postgrespro.ru> wrote: > 28.12.2016 23:43, Claudio Freire: > > Attached v4 patches with the requested fixes. > > > Sorry for being late, but the tests took a lot of time. I know. Takes me several day

Re: [HACKERS] Block level parallel vacuum WIP

On Tue, Jan 10, 2017 at 6:42 AM, Masahiko Sawada wrote: >> Does this work negate the other work to allow VACUUM to use > >> 1GB memory? > > Partly yes. Because memory space for dead TIDs needs to be allocated > in DSM before vacuum worker launches, parallel lazy vacuum

Re: [HACKERS] Block level parallel vacuum WIP

On Tue, Jan 10, 2017 at 6:42 AM, Masahiko Sawada wrote: > Attached result of performance test with scale factor = 500 and the > test script I used. I measured each test at four times and plot > average of last three execution times to sf_500.png file. When table > has

Re: [HACKERS] Block level parallel vacuum WIP

On Fri, Jan 6, 2017 at 2:38 PM, Masahiko Sawada wrote: > table_size | indexes | parallel_degree | time > +-+-+-- > 6.5GB | 0 | 1 | 00:00:14 > 6.5GB | 0 | 2 | 00:00:02 > 6.5GB

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Wed, Dec 28, 2016 at 3:41 PM, Claudio Freire <klaussfre...@gmail.com> wrote: >> Anyway, I found the problem that had caused segfault. >> >> for (segindex = 0; segindex <= vacrelstats->dead_tuples.last_seg; tupindex = >> 0, segindex++) >> { >&g

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Wed, Dec 28, 2016 at 10:26 AM, Anastasia Lubennikova <a.lubennik...@postgrespro.ru> wrote: > 27.12.2016 20:14, Claudio Freire: > > On Tue, Dec 27, 2016 at 10:41 AM, Anastasia Lubennikova > <a.lubennik...@postgrespro.ru> wrote: > > Program terminated with signal SIG

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Tue, Dec 27, 2016 at 10:41 AM, Anastasia Lubennikova wrote: > Program terminated with signal SIGSEGV, Segmentation fault. > #0 0x006941e7 in lazy_vacuum_heap (onerel=0x1ec2360, > vacrelstats=0x1ef6e00) at vacuumlazy.c:1417 > 1417tblk = >

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

he current segment, rather than global index). On Tue, Dec 27, 2016 at 10:41 AM, Anastasia Lubennikova <a.lubennik...@postgrespro.ru> wrote: > 23.12.2016 22:54, Claudio Freire: > > On Fri, Dec 23, 2016 at 1:39 PM, Anastasia Lubennikova > <a.lubennik...@postgrespro.ru> wrote: >

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Fri, Dec 23, 2016 at 1:39 PM, Anastasia Lubennikova <a.lubennik...@postgrespro.ru> wrote: >> On Thu, Dec 22, 2016 at 12:22 PM, Claudio Freire <klaussfre...@gmail.com> >> wrote: >>> >>> On Thu, Dec 22, 2016 at 12:15 PM, Anastasia Luben

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Thu, Dec 22, 2016 at 12:22 PM, Claudio Freire <klaussfre...@gmail.com> wrote: > On Thu, Dec 22, 2016 at 12:15 PM, Anastasia Lubennikova > <lubennikov...@gmail.com> wrote: >> The following review has been posted through the commitfest application: >> make insta

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Thu, Dec 22, 2016 at 12:15 PM, Anastasia Lubennikova wrote: > The following review has been posted through the commitfest application: > make installcheck-world: tested, failed > Implements feature: not tested > Spec compliant: not tested >

Re: [HACKERS] [WIP] [B-Tree] Keep indexes sorted by heap physical location

On Mon, Nov 21, 2016 at 5:42 PM, Peter Geoghegan wrote: >>> Or, you might want to make >>> sure that B-Tree tuple insertions still find that "first page" to >>> check, despite still generally treating heap item pointer as part of >>> the key proper (in unique indexes, it can

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Mon, Nov 21, 2016 at 2:15 PM, Masahiko Sawada <sawada.m...@gmail.com> wrote: > On Fri, Nov 18, 2016 at 6:54 AM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> On Thu, Nov 17, 2016 at 6:34 PM, Robert Haas <robertmh...@gmail.com> wrote: >>> On Thu,

Re: [HACKERS] [WIP] [B-Tree] Keep indexes sorted by heap physical location

On Fri, Nov 18, 2016 at 11:09 PM, Peter Geoghegan <p...@heroku.com> wrote: > On Fri, Nov 18, 2016 at 5:27 PM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> I've been changing the on-disk format considerably, to one that makes >> more sense. > > I can

Re: [HACKERS] [WIP] [B-Tree] Keep indexes sorted by heap physical location

On Fri, Nov 18, 2016 at 10:05 PM, Peter Geoghegan wrote: > On Thu, Aug 18, 2016 at 2:15 PM, Peter Geoghegan wrote: >> I think that this is a bad idea. We need to implement suffix >> truncation of internal page index tuples at some point, to make them >> contain

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Thu, Nov 17, 2016 at 6:34 PM, Robert Haas <robertmh...@gmail.com> wrote: > On Thu, Nov 17, 2016 at 1:42 PM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> Attached is patch 0002 with pgindent applied over it >> >> I don't think there's any other formatt

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Thu, Nov 17, 2016 at 2:51 PM, Claudio Freire <klaussfre...@gmail.com> wrote: > On Thu, Nov 17, 2016 at 2:34 PM, Masahiko Sawada <sawada.m...@gmail.com> > wrote: >> I glanced at the patches but the both patches don't obey the coding >> style of PostgreSQL. &g

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Thu, Nov 17, 2016 at 2:34 PM, Masahiko Sawada wrote: > I glanced at the patches but the both patches don't obey the coding > style of PostgreSQL. > Please refer to [1]. > > [1] >

Re: [HACKERS] CLUSTER, reform_and_rewrite_tuple(), and parallelism

On Wed, Oct 26, 2016 at 9:33 PM, Peter Geoghegan wrote: > Besides, parallel CREATE INDEX alone will probably > be quite effective at speeding up CLUSTER in practice, simply because > that's often where most wall clock time is spent during a CLUSTER > operation. Creating all

Re: [HACKERS] Indirect indexes

On Thu, Oct 20, 2016 at 1:08 PM, Pavan Deolasee <pavan.deola...@gmail.com> wrote: > On Thu, Oct 20, 2016 at 9:20 PM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> >> >> >> With indirect indexes, since you don't need to insert a tid, you

Re: [HACKERS] Indirect indexes

On Thu, Oct 20, 2016 at 12:30 PM, Pavan Deolasee wrote: > > > On Thu, Oct 20, 2016 at 8:44 PM, Petr Jelinek wrote: >> >> >> >> WARM can do WARM update 50% of time, indirect index can do HOT update >> 100% of time (provided the column is not

  1   2   3   4   5   >