Re: Binary search in ScalarArrayOpExpr for OR'd constant arrays

to see how all the work over the years to improve Hash > Joins compares to the bsearch with and without the bloom filter. It would be interesting. It also makes one wonder about optimizing these into to hash joins...which I'd thought about over at [1]. I think it'd be a very significant

Re: Binary search in ScalarArrayOpExpr for OR'd constant arrays

On Sat, Apr 25, 2020 at 8:31 PM Tomas Vondra wrote: > > On Sat, Apr 25, 2020 at 06:47:41PM -0400, James Coleman wrote: > >On Sat, Apr 25, 2020 at 5:41 PM David Rowley wrote: > >> > >> On Sun, 26 Apr 2020 at 00:40, Tomas Vondra > >> wrote: > >

Re: Binary search in ScalarArrayOpExpr for OR'd constant arrays

On Sun, Apr 26, 2020 at 4:49 PM Tomas Vondra wrote: > > On Sun, Apr 26, 2020 at 02:46:19PM -0400, James Coleman wrote: > >On Sat, Apr 25, 2020 at 8:31 PM Tomas Vondra > > wrote: > >> > >> On Sat, Apr 25, 2020 at 06:47:41PM -0400, James Coleman wrote: > &g

Re: doc review for v13

d is marked NO INHERIT, the command will > fail; > > But not the hybrid: "If any OF THE .. is .." "any of the...are" sounds more natural to my ears, and some searching yielded some grammar sites that agree (specifically that "any of" is only used with singular verbs if the construction is uncountable or negative). However there are also multiple claims by grammarians that either singular or plural verbs are acceptable with the "any of" construction. So that's not all that helpful. James

Binary search in ScalarArrayOpExpr for OR'd constant arrays

On Sun, Apr 26, 2020 at 7:41 PM James Coleman wrote: > > On Sun, Apr 26, 2020 at 4:49 PM Tomas Vondra > wrote: > > > > On Sun, Apr 26, 2020 at 02:46:19PM -0400, James Coleman wrote: > > >On Sat, Apr 25, 2020 at 8:31 PM Tomas Vondra > > > wrote: > >

Re: Binary search in ScalarArrayOpExpr for OR'd constant arrays

On Sun, Apr 26, 2020 at 11:44 PM David Rowley wrote: > > On Mon, 27 Apr 2020 at 15:12, James Coleman wrote: > > While working on this I noticed that dynahash.c line 499 has this assertion: > > > > Assert(info->entrysize >= info->keysize); > > > >

Re: Binary search in ScalarArrayOpExpr for OR'd constant arrays

On Sun, Apr 26, 2020 at 7:41 PM James Coleman wrote: > > On Sun, Apr 26, 2020 at 4:49 PM Tomas Vondra > wrote: > > > > On Sun, Apr 26, 2020 at 02:46:19PM -0400, James Coleman wrote: > > >On Sat, Apr 25, 2020 at 8:31 PM Tomas Vondra > > > wrote: > >

Re: Binary search in ScalarArrayOpExpr for OR'd constant arrays

On Tue, Apr 28, 2020 at 8:25 AM Tomas Vondra wrote: > > On Mon, Apr 27, 2020 at 09:04:09PM -0400, James Coleman wrote: > >On Sun, Apr 26, 2020 at 7:41 PM James Coleman wrote: > >> > >> On Sun, Apr 26, 2020 at 4:49 PM Tomas Vondra > >> wrote: > >

Re: pg_rewind docs correction

On Tue, Apr 28, 2020 at 12:31 AM Michael Paquier wrote: > > On Mon, Mar 09, 2020 at 09:26:17AM -0400, James Coleman wrote: > >> - pg_stat_tmp/, and > >> - pg_subtrans/ are omitted from the data copied > >> - from the source cluster. Any

Re: Binary search in ScalarArrayOpExpr for OR'd constant arrays

or all executions. But maybe that's not >> possible or maybe it's pointless for other reasons. It sure looks a bit >> like trying to build a query engine from FOR loop. > > > Theoretically it is possible, not now. But I don't think so it is necessary. > I cannot to remember this pattern in any plpgsql code and I never seen any > request on this feature. > > I don't think so this is task for plpgsql engine. Maybe for JIT sometimes. Agreed. I'd thought about this kind of scenario when I brought this up, but I think solving it would the responsibility of the pg/pgsql compiler rather than the expression evaluation code, because it'd have to recognize the situation and setup a shared expression evaluation context to be reused each time through the loop. James

Re: Binary search in ScalarArrayOpExpr for OR'd constant arrays

On Tue, Apr 28, 2020 at 1:40 PM Pavel Stehule wrote: > > > > út 28. 4. 2020 v 18:17 odesílatel James Coleman napsal: >> >> On Tue, Apr 28, 2020 at 11:18 AM Pavel Stehule >> wrote: >> > >> > >> > >> > út 28. 4. 2020 v 16:48 odesí

Re: Binary search in ScalarArrayOpExpr for OR'd constant arrays

I cc'd Andres given his commit introduced simplehash, so I figured he'd probably have a few pointers on when each one might be useful. On Tue, Apr 28, 2020 at 8:39 AM James Coleman wrote: ... > > Any particular reasons to pick dynahash over simplehash? ISTM we're > >

Re: Binary search in ScalarArrayOpExpr for OR'd constant arrays

On Tue, Apr 28, 2020 at 7:05 PM Andres Freund wrote: > > Hi, > > On 2020-04-28 18:22:20 -0400, James Coleman wrote: > > I cc'd Andres given his commit introduced simplehash, so I figured > > he'd probably have a few pointers on when each one might be useful. >

Re: Binary search in ScalarArrayOpExpr for OR'd constant arrays

On Wed, Apr 29, 2020 at 11:17 AM Tomas Vondra wrote: > > On Wed, Apr 29, 2020 at 10:26:12AM -0400, James Coleman wrote: > >On Tue, Apr 28, 2020 at 7:05 PM Andres Freund wrote: > >> > >> Hi, > >> > >> On 2020-04-28 18:22:20 -0400, James Cole

Comment simplehash/dynahash trade-offs

templating" particularly for seeing what the available API is and so added sample method signatures in comments to the macro generated method signature defines. James [1]: https://www.postgresql.org/message-id/CAAaqYe956a-zbm1qR8pqz%3DiLbF8LW5vBrQGrzXVHXdLk3at5_Q%40mail.gmail.com From 36f6b6

Re: Binary search in ScalarArrayOpExpr for OR'd constant arrays

ust collapse all of these patches, but it's perhaps interesting to see the changes required to use each style (binary search, dynahash, simplehash). As before, there are clearly comments and naming things to be addressed, but the implementation should be reasonably clean. James From d455a577

Re: pg_rewind docs correction

d applied after an extra lookup. Thanks for the discussion, James. Yep. Thanks for pushing to make sure it was as correct as possible while improving it. James

Re: Incremental sorts and EXEC_FLAG_REWIND

On Thu, May 7, 2020 at 2:57 PM Jonathan S. Katz wrote: > > On 4/24/20 6:57 PM, Tomas Vondra wrote: > > On Fri, Apr 24, 2020 at 04:35:02PM -0400, James Coleman wrote: > >> On Sun, Apr 19, 2020 at 12:14 PM James Coleman wrote: > >>> > >>> On Wed,

Re: [PATCH] Fix division by zero (explain.c)

On Fri, May 8, 2020 at 7:20 PM Tomas Vondra wrote: > > On Fri, May 08, 2020 at 07:25:36PM -0300, Ranier Vilela wrote: > >Em sex., 8 de mai. de 2020 às 19:02, Tomas Vondra < > >tomas.von...@2ndquadrant.com> escreveu: > > > >> On Thu, Apr 23, 2020 at 04:12:34

Re: Incremental sorts and EXEC_FLAG_REWIND

On Fri, May 8, 2020 at 7:14 PM Tomas Vondra wrote: > > On Fri, Apr 24, 2020 at 04:35:02PM -0400, James Coleman wrote: > >On Sun, Apr 19, 2020 at 12:14 PM James Coleman wrote: > >> > >> On Wed, Apr 15, 2020 at 2:04 PM James Coleman wrote: > >> > >

Re: Proving IS NOT NULL inference for ScalarArrayOpExpr's

bit in the attached patch to start). > I think the name clause_proved_for_null_test() is a bit weird, being in > the past tense. I'd maybe change "proved" to "proves". Changed. > s/exppresions/expresions/ in the test files. Fixed. James Coleman saop_is_not_null-v8.patch Description: Binary data

Re: Proving IS NOT NULL inference for ScalarArrayOpExpr's

false) but > false for B, so that definition doesn't hold. So I think the comment > is accurate, but I can reword if you have an idea of what you'd like > to see (I've tweaked a bit in the attached patch to start). I forgot to update in v8 so attaching v9. James Coleman saop_is_not_null-v9.patch Description: Binary data

Re: Index Skip Scan

hen skip scanning would be a dramatic improvement. I suppose that both requirements could be met by incorporating it into the existing index scanning code and also modifying to costing to (only when we have high confidence?) account for the optimization. I'm not sure if that makes things better than the current state of the patch or not. James Coleman

Reaping Temp tables to avoid XID wraparound

ow I can map that to a PID - any thoughts? Cheers, James Sewell, Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009 *P *(+61) 2 8099 9000 <(+61)%202%208099%209000> *W* www.jirotech.com *F * (+61) 2 8099 9099 <(+61)%202%208099%209000> -- The contents of this email

Re: Reaping Temp tables to avoid XID wraparound

ay I could find to currently solve the problem. Cheers, James Sewell, Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009 *P *(+61) 2 8099 9000 <(+61)%202%208099%209000> *W* www.jirotech.com *F * (+61) 2 8099 9099 <(+61)%202%208099%209000> On Thu, 14 Feb 2019 at

Re: 2019-03 CF Summary / Review - Tranche #2

"learn as you go", but since there's not a lot of information directly written on it I figured asking explicitly is the best way to learn the process better. Thanks, James Coleman

Re: Reaping Temp tables to avoid XID wraparound

> Yeah, possibly. I think that it could be tricky though to get that at >> a global level in a cheap way. It makes also little sense to only >> show the temp namespace OID if that information is not enough. >> > > We could I guess add a field specifically for temp_namespace_xid or such. > The que

Re: Reaping Temp tables to avoid XID wraparound

mated or manual way of solving it (apart from randomly terminating backends (you have to search via user and hope there is only one, and that it matches the temp table owner) or restarting Postgres). I suppose an in-core way of disconnecting idle sessions after x time would work too - but that seems

Re: Proving IS NOT NULL inference for ScalarArrayOpExpr's

WHAT, exactly?" --- and as these > examples show, being crystal clear on what it proves is essential. Given the new argument, I've reverted the name change. I also updated the tests with a new helper function "opaque_array" to make it easy to test cases where the planner can&#

printf ordering issues?

realized instead that the output from the executor is getting flushed on the *subsequent* query. Is there some kind of weird behavior that most hackers already know about and I'm missing? (Also I realize I can use elog(LOG...), but I'm still curious.) Thanks, James Coleman

Re: Proving IS NOT NULL inference for ScalarArrayOpExpr's

Is there anything I can do to solicit reviewers for the current CF? The patch is quite small and should be fairly simple to review. - James

Using Btree to Provide Sorting on Suffix Keys with LIMIT

e worth showing yet, but I wanted to start the discussion on the design considerations while I continue work on the patch. - James Coleman

Re: Using Btree to Provide Sorting on Suffix Keys with LIMIT

On Sat, Dec 29, 2018 at 9:50 PM David Rowley wrote: > > On Sun, 30 Dec 2018 at 13:00, James Coleman wrote: > > Note that the index scan (or bitmap scan) nodes return all 1500 rows > > matching `bar_fk IN (1,2,3)`. After all rows are returned, that total > > set is ordere

Re: Using vim for developing porstres wiki article

a loss. I noticed this particularly since since just 2 or 3 days ago I myself had edited this section to add the softtabstop=0 option (the Vim default) so that if soft tabs are enabled in someone's general Vim config then hitting the tab key won't result in inserting 2 spaces while working in the Postgres source. Thanks, James Coleman

Arrays of domain returned to client as non-builtin oid describing the array, not the base array type's oid

++-+- 1009 | _text | A | 25 2392140 | _required_text | A | 2392141 So -- do others find this inconsistent, or is it just me and I should work on having psycopg2 be able to learn the type mapping itself if I don't want to do SQL-side casts? I'll argue that if scalar projections erase the domain's oid, then array projections ought to as well. Thanks! James - James Robinson ja...@jlr-photo.com http://jlr-photo.com/

Re: Proving IS NOT NULL inference for ScalarArrayOpExpr's

e sad path test in addition to my original happy path test. Patch v3 attached. James Coleman saop_is_not_null-v3.patch Description: Binary data

Re: Proving IS NOT NULL inference for ScalarArrayOpExpr's

uot;, but I figured it was worth getting this logically correct before attempting to optimize that especially since it already exists in one place. > Minor stylistic quibble: I don't like where you inserted the new code in > clause_is_strict_for, because it renders the comment a few lines abo

Re: Proving IS NOT NULL inference for ScalarArrayOpExpr's

On Mon, Jan 14, 2019 at 11:08 AM Tom Lane wrote: > > James Coleman writes: > > On Sun, Jan 13, 2019 at 3:06 PM Tom Lane wrote: > >> There's still a logical problem here, which is that in order to > >> prove that the ScalarArrayOpExpr yields NULL when its LH

Re: Proving IS NOT NULL inference for ScalarArrayOpExpr's

On Mon, Jan 14, 2019 at 11:34 AM Tom Lane wrote: > > James Coleman writes: > > On Mon, Jan 14, 2019 at 11:08 AM Tom Lane wrote: > >> I think these test cases don't actually prove much about the behavior > >> of your patch. Wouldn't they be handled by the

Re: Proving IS NOT NULL inference for ScalarArrayOpExpr's

;ve also added the "parallel" case in predicate_refuted_by_simple_clause, but I haven't added a test for that yet. I also would like to add a test for the sad path to parallel the happy path computed array/cte test. While I add that though I wanted to get this updated version out to get feedback on the approach. James Coleman saop_is_not_null-v5.patch Description: Binary data

Re: Proving IS NOT NULL inference for ScalarArrayOpExpr's

ell as s_r_holds. I'd only expected the latter, since only "strongly_refuted_by = t". 3. The tests I have for refuting "(x + x) is null" show that both s_r_holds and w_r_holds. I'd expected these to be false. I'm attaching the current version of the patch with

Re: Proving IS NOT NULL inference for ScalarArrayOpExpr's

On Tue, Jan 15, 2019 at 3:53 PM Tom Lane wrote: > > James Coleman writes: > > [ saop_is_not_null-v6.patch ] > > I quite dislike taking the responsibility out of clause_is_strict_for > and putting it in the callers. Aside from requiring duplicative code, > it means

Re: Proving IS NOT NULL inference for ScalarArrayOpExpr's

, then it must imply the addition of itself is not null also? This is the root of the questions I had: James Coleman writes: > 1. The current code doesn't result in "strongly_implied_by = t" for > the "(x + x) is not null" case, but it does result in "s_i_hol

Re: Proving IS NOT NULL inference for ScalarArrayOpExpr's

On Tue, Jan 15, 2019 at 8:14 PM David Rowley wrote: > > On Wed, 16 Jan 2019 at 14:05, James Coleman wrote: > > At the risk of missing something obvious, I'm not sure I see a case > > where "x is not null" does not imply "(x + x) is not null", at le

Re: Proving IS NOT NULL inference for ScalarArrayOpExpr's

with array constants of > 100 values)? I dislike that it's not as obvious what's going on, but given that the code shouldn't care about array size anyway...so if there's an inclination to fewer tests that's the first place I'd look at cutting them. James Coleman

Re: Proving IS NOT NULL inference for ScalarArrayOpExpr's

On Wed, Jan 16, 2019 at 8:49 AM James Coleman wrote: > > On Tue, Jan 15, 2019 at 11:37 PM Tom Lane wrote: > > Well, as I said upthread, it seems like we need to think a bit more > > carefully about what it is that clause_is_strict_for is testing --- > > and if that e

Re: Using Btree to Provide Sorting on Suffix Keys with LIMIT

; > > similar derivations with increasing numbers of equality quals). > > > > I don't quite understand this the above paragraph, but I assume this > > would be possible to do with some new index am routine which allowed > > multiple different values for the initial

Re: Minimal logical decoding on standbys

Hi all, This is great stuff! My understanding is that this patch guarantees 0 data loss for a logical replication stream if the primary crashes and a standby which was marked as sync at failure time is promoted. Is this correct? James -- James Sewell, Chief Architect Suite 112, Jones Bay

Re: Index Skip Scan

but I can easily imagine that it's still not enough > in some extreme situations. This is almost certainly rehashing already covered ground, but since I doubt it's been discussed recently, would you be able to summarize that choice (not to always get the next tuple by scanning from the top of the tree again) and the performance/complexity tradeoffs? Thanks, James

Re: [DOC] Document concurrent index builds waiting on each other

I went ahead and registered this in the current only CF as https://commitfest.postgresql.org/27/2454/ Alvaro: Would you like to be added as a reviewer? James

Re: [DOC] Document concurrent index builds waiting on each other

gh1...@paquier.xyz Michael, Thanks for the cross-link. Do you think this would be valuable to document at the same time? Or did you just want to ensure we were also aware of this particular downfall? If the latter, I appreciate it, it's helpful info. If the latter, let me know, and I'll try to update the patch. Thanks, James

Re: [DOC] Document auto vacuum interruption

On Thu, Sep 19, 2019 at 5:34 AM Amit Kapila wrote: > > On Wed, Sep 18, 2019 at 10:25 AM Amit Kapila wrote: > > > > On Tue, Sep 17, 2019 at 5:48 PM James Coleman wrote: > > > > > > On Tue, Sep 17, 2019 at 2:21 AM Amit Kapila > > > wrote: > >

Re: [PATCH] Incremental sort

On Tue, Mar 3, 2020 at 1:43 PM Tomas Vondra wrote: > On Tue, Mar 03, 2020 at 12:17:22PM -0500, David Steele wrote: > >James and Tomas, > > > >On 1/21/20 10:03 AM, James Coleman wrote: > >>On Tue, Jan 21, 2020 at 9:58 AM Tomas Vondra > >> wrote: > >>

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

On Tue, Jan 21, 2020 at 9:37 AM James Coleman wrote: > That being said, the patch also needs some more work on improving > EXPLAIN ANALYZE output (perhaps min/max/mean or median of > memory usage number of groups in each sort mode), and I think it's far > more feasible that

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

On Thu, Mar 5, 2020 at 5:53 PM Tom Lane wrote: > James Coleman writes: > > I'm looking at this now, and realized that at least for parallel plans > the > > current patch tracks the tuplesort instrumentation whether or not an > > EXPLAIN ANALYZE is in process. >

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

On Thu, Mar 5, 2020 at 6:45 PM Tom Lane wrote: > James Coleman writes: > > That's what I figured, but as I mentioned I've having trouble figuring > out > > how the fact that an analyze is in flight is determined. I assume it's > > something that live

Re: Allow to_date() and to_timestamp() to accept localized names

er place, so none from me, and thank you. > > Pushed then. > On master with a clean build (and configure re-run) and a fresh init-db, I'm seeing the collate.linux.utf8 test fail with the attached diff. Is there something I need to reconfigure, install on my machine (elementary os, so

Re: Allow to_date() and to_timestamp() to accept localized names

On Sat, Mar 7, 2020 at 9:31 PM Tom Lane wrote: > James Coleman writes: > > On master with a clean build (and configure re-run) and a fresh init-db, > > I'm seeing the collate.linux.utf8 test fail with the attached diff. > > -- to_char > SET lc_time TO 'tr

Re: Allow to_date() and to_timestamp() to accept localized names

On Sat, Mar 7, 2020 at 9:48 PM Tom Lane wrote: > James Coleman writes: > > On Sat, Mar 7, 2020 at 9:31 PM Tom Lane wrote: > >> Looks like you may not have Turkish locale installed? Try > >> locale -a | grep tr_TR > > > Hmm, when I grep the locales I see

Re: Allow to_date() and to_timestamp() to accept localized names

On Sun, Mar 8, 2020 at 7:17 AM Juan José Santamaría Flecha < juanjo.santama...@gmail.com> wrote: > > > On Sun, Mar 8, 2020 at 3:48 AM Tom Lane wrote: > >> James Coleman writes: >> > On Sat, Mar 7, 2020 at 9:31 PM Tom Lane wrote: >> >> Looks like yo

Re: Fastpath while arranging the changes in LSN order in logical decoding

; > IMHO, if we conclude that because there is no performance gain so we > don't want to add one extra path in the code then we might want to > remove that TODO from the code so that we don't spend time for > optimizing this in the future. > Would you be able to share your test setup? It seems like it’d helpful to get a larger sample size to better determine if it’s measurable or not. Visually those 4 runs look to me like it’s possible, but objectively I’m not sure we can yet conclude one way or the other. James

Re: Allow to_date() and to_timestamp() to accept localized names

On Sun, Mar 8, 2020 at 10:42 AM James Coleman wrote: > On Sun, Mar 8, 2020 at 7:17 AM Juan José Santamaría Flecha < > juanjo.santama...@gmail.com> wrote: > >> >> >> On Sun, Mar 8, 2020 at 3:48 AM Tom Lane wrote: >> >>> James Coleman writes: >

Re: pg_rewind docs correction

On Tue, Sep 17, 2019 at 9:41 PM Michael Paquier wrote: > On Tue, Sep 17, 2019 at 08:38:18AM -0400, James Coleman wrote: > > I don't agree that that's a valid equivalency. I myself spent a lot of > > time trying to understand how this could possibly be true a while >

Re: pg_rewind docs correction

On Sun, Mar 8, 2020 at 5:13 PM James Coleman wrote: > > I realized I didn't previously add this to the CF; since it's not a new > patch I've added it to the current CF, but if this is incorrect please let > me know. > Hmm, looks like I can't add it to the

Re: Allow to_date() and to_timestamp() to accept localized names

On Sun, Mar 8, 2020 at 2:19 PM Tom Lane wrote: > I wrote: > > James Coleman writes: > >> I'm still interested in understanding why we're using the ISO locale > >> instead of the utf8 one in a utf8-labeled test though. > > > We are not. My und

Re: Allow to_date() and to_timestamp() to accept localized names

On Sun, Mar 8, 2020 at 6:03 PM Tom Lane wrote: > James Coleman writes: > > So just to confirm I understand, that implies that the issue is solely > that > > only the utf8 tr_TR set is installed by default on this machine, and the > > iso-8859-9 set is a hard requireme

Nicer error when connecting to standby with hot_standby=off

changing this value would result in a wire protocol change/something the client wants to know about? If so, I assume it's not reasonable to change the value, but would it still be reasonable to change the error text? Thanks, James Coleman

Re: pg_rewind docs correction

On Mon, Mar 9, 2020 at 2:59 AM Michael Paquier wrote: > On Sun, Mar 08, 2020 at 05:13:21PM -0400, James Coleman wrote: > > I've added the information about how the backup label control file is > > written, and updated the How It Works steps to refer to that separat

Re: Index Skip Scan

ged from using > EquivalenceClasses to use Exprs instead. But I can't say I'm being overly helpful by pointing that out, since I don't have my head in the code enough to understand how you'd accomplish that :) James

Re: Nicer error when connecting to standby with hot_standby=off

On Mon, Mar 9, 2020 at 6:28 PM Andres Freund wrote: > > Hi, > > On 2020-03-08 20:12:21 -0400, James Coleman wrote: > > I recently noticed while setting up a test environment that attempting to > > connect to a standby running without hot_standby=on results in a fairly >

Re: Nicer error when connecting to standby with hot_standby=off

On Mon, Mar 9, 2020 at 8:06 PM Andres Freund wrote: > > Hi, > > On 2020-03-09 18:40:32 -0400, James Coleman wrote: > > On Mon, Mar 9, 2020 at 6:28 PM Andres Freund wrote: > > > > I wanted to get some initial feedback on the idea before writing a > > >

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

s here, and I realized that my test case from far upthread is actually a useful setup to see how much overhead is involved in sorting each batch individually, since it sets up data with each batch only containing 1 tuple. That particular case is one we could easily optimize anyway in the code and skip sorting altogether -- might be a useful enhancement. I hope to do some more testing and then report back with the results. James Coleman

Re: Index Skip Scan

also, but likely won't get to it until later this week or next week at the earliest. Jesper: Is there anything still on your list of things to change about the patch? Or would now be a good time to look hard at the code? James Coleman

Misleading comment in tuplesort_set_bound

e: this also means the function header comment "Must be called before inserting any tuples" is a condition that isn't actually validated, but I think that's fine given it's not a new problem and even more so since the same comment goes on to say that

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

ith more detailed comments on my current benchmarking work. James Coleman incremental-sort-28.patch Description: Binary data

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

On Thu, Jun 13, 2019 at 11:38:12PM -0400, James Coleman wrote: >I think the first thing to do is get some concrete numbers on performance if >we: > >1. Only sort one group at a time. >2. Update the costing to prefer traditional sort unless we have very >high confidence we'

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

On Mon, Jun 24, 2019 at 12:56 PM Simon Riggs wrote: > > On Mon, 24 Jun 2019 at 16:10, James Coleman wrote: >> >> On Thu, Jun 13, 2019 at 11:38:12PM -0400, James Coleman wrote: >> >I think the first thing to do is get some concrete numbers on performance >>

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

On Mon, Jun 24, 2019 at 4:16 PM Tomas Vondra wrote: > > On Mon, Jun 24, 2019 at 01:00:50PM -0400, James Coleman wrote: > >On Mon, Jun 24, 2019 at 12:56 PM Simon Riggs wrote: > >> > >> On Mon, 24 Jun 2019 at 16:10, James Coleman wrote: > >>> > >&g

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

On Tue, Jun 25, 2019 at 12:02 PM Tomas Vondra wrote: > > On Mon, Jun 24, 2019 at 07:34:19PM -0400, James Coleman wrote: > >On Mon, Jun 24, 2019 at 4:16 PM Tomas Vondra > > wrote: > >> > >> On Mon, Jun 24, 2019 at 01:00:50PM -0400, James Coleman wrote: > >

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

On Tue, Jun 25, 2019 at 1:13 PM Peter Geoghegan wrote: > > On Tue, Jun 25, 2019 at 9:53 AM James Coleman wrote: > > Given the patch contents I don't see any obviously reason why either > > of those possibilities (calling comparetup_heap less often, or it's >

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

On Tue, Jun 25, 2019 at 4:32 PM Tomas Vondra wrote: > > On Tue, Jun 25, 2019 at 12:13:01PM -0700, Peter Geoghegan wrote: > >On Tue, Jun 25, 2019 at 11:03 AM James Coleman wrote: > >> No, I haven't confirmed that it's called less frequently, and I'd be >

Re: Index Skip Scan

g something obvious, and in that case, please > ignore. I think that example is the opposite direction of what David (Rowley) is saying. Unique on {a, b} implies unique on {a, b, c} while you're correct that the inverse doesn't hold. Unique on {a, b} also implies unique on {b, a} as well as on {b, a, c} and {c, a, b} and {c, b, a} and {a, c, b}, which is what makes this different from pathkeys. James Coleman

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

On Tue, Jun 25, 2019 at 7:22 PM Tomas Vondra wrote: > > On Tue, Jun 25, 2019 at 04:53:40PM -0400, James Coleman wrote: > > > >Unrelated: if you or someone else you know that's more familiar with > >the parallel code, I'd be interested in their looking at the patc

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

On Thu, Jul 4, 2019 at 10:46 AM Tomas Vondra wrote: > > On Thu, Jul 04, 2019 at 09:29:49AM -0400, James Coleman wrote: > >On Tue, Jun 25, 2019 at 7:22 PM Tomas Vondra > > wrote: > >> > >> On Tue, Jun 25, 2019 at 04:53:40PM -0400, James Coleman wrote: > >&

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

On Sun, Jul 7, 2019 at 8:34 AM Tomas Vondra wrote: > > On Thu, Jul 04, 2019 at 09:29:49AM -0400, James Coleman wrote: > >On Tue, Jun 25, 2019 at 7:22 PM Tomas Vondra > > wrote: > >> > >> On Tue, Jun 25, 2019 at 04:53:40PM -0400, James Coleman wrote: > >&

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

we're discussing, but it seems to me that it at least fits more with what the comments imply. I'll try to look at it a bit more later also, but at the moment other work calls. James Coleman

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

On Mon, Jul 8, 2019 at 9:59 AM Tomas Vondra wrote: > > On Mon, Jul 08, 2019 at 09:22:39AM -0400, James Coleman wrote: > >On Sun, Jul 7, 2019 at 5:02 PM Tomas Vondra > > wrote: > >> We're running query like this: > >> > >> SELECT a, sum(b), coun

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

On Mon, Jul 8, 2019 at 10:58 AM Tomas Vondra wrote: > > On Mon, Jul 08, 2019 at 10:32:18AM -0400, James Coleman wrote: > >On Mon, Jul 8, 2019 at 9:59 AM Tomas Vondra > > wrote: > >> > >> On Mon, Jul 08, 2019 at 09:22:39AM -0400, James Coleman wrote: > &

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

apture the debugging process for reference. On Mon, Jul 8, 2019 at 12:07 PM James Coleman wrote: > > On Mon, Jul 8, 2019 at 10:58 AM Tomas Vondra > wrote: > > > > On Mon, Jul 08, 2019 at 10:32:18AM -0400, James Coleman wrote: > > >On Mon, Jul 8, 2019 at 9:59 AM Tomas Vondr

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

On Mon, Jul 8, 2019 at 9:37 PM Tomas Vondra wrote: > > On Mon, Jul 08, 2019 at 12:07:06PM -0400, James Coleman wrote: > >On Mon, Jul 8, 2019 at 10:58 AM Tomas Vondra > > wrote: > >> > >> On Mon, Jul 08, 2019 at 10:32:18AM -0400, James Coleman wrote: > &

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

On Mon, Jul 8, 2019 at 6:37 PM Alexander Korotkov wrote: > > On Thu, Jul 4, 2019 at 4:25 PM James Coleman wrote: > > Process questions: > > - Do I need to explicitly move the patch somehow to the next CF? > > We didn't manage to register it on current (July) commit

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

On Tue, Jul 9, 2019 at 10:54 AM Tomas Vondra wrote: > > On Tue, Jul 09, 2019 at 09:28:42AM -0400, James Coleman wrote: > >On Mon, Jul 8, 2019 at 9:37 PM Tomas Vondra > > wrote: > >> > >> On Mon, Jul 08, 2019 at 12:07:06PM -0400, James Coleman wrote: > >

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

> > >Tangentially: I'd almost expect enable_incremental_sort to act as a > >hard disable (and not even generate the paths) rather than a soft > >cost-based disable, since while standard sort is the most basic > >operation that needs to always be available as a last resort the same > >is not true for incremental sort... > > > > Good point. It's somewhat similar to options like enable_parallel_hash > which also are "hard" switches (i.e. not cost-based penalties). I assume the proper approach here then is to check the GUC before building and adding the path? > >If we end up wanting to limit the number of places we consider > >incremental sort (whether for planning time or merely for size of the > >initial patch, do you have any thoughts on what general areas we > >should consider most valuable? Besides the obvious LIMIT case aother > >area that might benefit was min/max, though I'm not sure yet at the > >moment if that would really end up meaning considering it all over the > >place. > > > > OK, sounds like a plan! Did you have any thoughts on the question about where this is likely to be most valuable? James Coleman

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

Sort Key: a, b Presorted Key: a -> Parallel Index Scan using t_a_idx on t (cost=0.43..417703.85 rows=4166740 width=12) Is that something we should consider a bug at this stage? It's also not clear to mean (costing aside) which plan is intuitively preferable. James

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

Workers Planned: 4 -> Partial GroupAggregate Group Key: a, b -> Sort Sort Key: a, b -> Parallel Seq Scan on t (or if I disable seqscan then the sort+seq scan above becomes inc sort + index scan) To be honest, I don't think I understand how you would get a plan like that anyway since the index here only has "a" and so can't provide (pathkeys: a, b). Perhaps there's something I'm still missing though. James Coleman

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

On Sat, Jul 20, 2019 at 12:12 PM James Coleman wrote: > > On Sat, Jul 20, 2019 at 11:25 AM Tomas Vondra > wrote: > ... > > >My current line of investigation is whether we need to do anything in > > >the parallel portion of create_ordered_paths(). I noticed that th

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

On Sat, Jul 20, 2019 at 1:00 PM Tomas Vondra wrote: > > On Sat, Jul 20, 2019 at 12:21:01PM -0400, James Coleman wrote: > >On Sat, Jul 20, 2019 at 12:12 PM James Coleman wrote: > >> > >> On Sat, Jul 20, 2019 at 11:25 AM Tomas Vondra > >> wrote: > >&

Re: Binary search in ScalarArrayOpExpr for OR'd constant arrays

On Tue, Sep 8, 2020 at 4:37 PM Heikki Linnakangas wrote: > > On 08/09/2020 22:25, James Coleman wrote: > > On Wed, Aug 19, 2020 at 3:16 AM Heikki Linnakangas wrote: > >> > >> You could also apply the optimization for non-Const expressions, as lo

ST_AsMVTGeom AND table name as parameter in pl/psql

I'm trying to develop a plpgsql function that would extract mapbox vector tiles from a postgresql/post gis database. The database has multiple geospatial tables, so I want the function to be able to take a table name as a parameter. I've gotten the function to work using hard coded table names. Di

PGXS testing upgrade paths

t aware of an automatic or standard way to test all upgrade paths provided by the extension. Is there something I'm missing? Or an approach people like (I suppose the simplest way would be "manually" executing the upgrade files in a regress test, but that seems tedious). Thanks, James

<    1   2   3   4   5   6   7   >