Re: [HACKERS] possible optimizations - pushing filter before aggregation

2016-11-18 Thread Douglas Doole
On Fri, Nov 18, 2016 at 12:47 AM Pavel Stehule wrote: Isn't possible in this case push equivalence before aggregation? If I'm understanding you correctly, that would lead to wrong results. Here's a simple example: CREATE VIEW v AS SELECT MIN(a) m FROM t; and table T

Re: [HACKERS] possible optimizations - pushing filter before aggregation

2016-11-19 Thread Douglas Doole
> > In above case wondering if we could do this > > Min(a) = 2 is the condition, generate condition *"a <= 2"* and push it > down as scan key. Since pushed down condition is lossy one for us ( it > gives values < 2), finally do a recheck of *"Min(a) = 2"*. > > For Max(a) = 2 we can have *"a >=2"*,

Re: [HACKERS] WIP: Faster Expression Processing v4

2017-03-14 Thread Douglas Doole
Andres, sorry I haven't had a chance to look at this great stuff you've been doing. I've wanted to get to it, but work keeps getting in the way. ;-) I do have one observation based on my experiments with your first version of the code. In my tests, I found that expression init becomes a lot more

Re: [HACKERS] WIP: Faster Expression Processing v4

2017-03-14 Thread Douglas Doole
On Tue, Mar 14, 2017 at 3:16 PM Andres Freund wrote: > Hm. Right now ExprState's are allocated in several places - but we > could easily move that to a central place. Have a bit of a hard time > seing that that branch during *initialization* time is that expensive, >

[HACKERS] Table collision in join.sql and aggregates.sql

2017-03-31 Thread Douglas Doole
As we've been merging our code with 9.6, a couple developers have had one-off failures in the join.sql and aggregates.sql test because the tables T1, T2 and T3 have the wrong definitions. Digging into it, I found that both files create the tables T1, T2, and T3 for a short period of time and then

Re: [HACKERS] Table collision in join.sql and aggregates.sql

2017-03-31 Thread Douglas Doole
D'oh! The "temp" declaration had been removed from our test since we don't use temp tables. I missed that when applying it to the community code. You can ignore me now. On Fri, Mar 31, 2017 at 4:01 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > Douglas Doole <dougd

Re: [HACKERS] [PATCH] Push limit to sort through a subquery

2017-04-19 Thread Douglas Doole
call pass_down_bound() recursively on subqueryScanState->subplan. > > Please add this to the next commitfest. > > On Wed, Apr 19, 2017 at 6:09 AM, Douglas Doole <dougdo...@gmail.com> > wrote: > > We've hit a case where pass_down_bound() isn't pushing the row count > limit >

[HACKERS] [PATCH] Push limit to sort through a subquery

2017-04-18 Thread Douglas Doole
We've hit a case where pass_down_bound() isn't pushing the row count limit from limit into sort. The issue is that we're getting a subquery scan node between the limit and the sort. The subquery is only doing column projection and has no quals or SRFs so it should be safe to push the limit into

[HACKERS] Inconsistencies in from_char_parse_int_len()

2017-07-31 Thread Douglas Doole
I was playing with TO_TIMESTAMP() and I noticed a weird result: postgres=# select to_timestamp('20170-07-24 21:59:57.12345678', '-mm-dd hh24:mi:ss.us'); to_timestamp 20170-07-24 22:00:09.345678+00 (1 row) Even though the "us" token is supposed to

Re: [HACKERS] [PATCH] Push limit to sort through a subquery

2017-04-28 Thread Douglas Doole
> > If you add this to the commitfest app, more people might look at it when > the next commitfest opens. I have added it. https://commitfest.postgresql.org/14/1119/ Also, it might help if you can provide a query/ies with numbers where this > optimization shows improvement. > I can't provide

Re: [HACKERS] [PATCH] Push limit to sort through a subquery

2017-08-18 Thread Douglas Doole
> > 1. The header comment for pass_down_bound() could mention "one or more > levels of subqueries" rather than "a subquery". > Fixed 2. The first of the comments in the function body appears to have a > whitespace issue that needs to be fixed manually or, better yet, > addressed by pgindent. >

Re: [HACKERS] [PATCH] Push limit to sort through a subquery

2017-08-17 Thread Douglas Doole
I completely agree. The further a limit can be pushed down, the better. The patch looks good to me. On Thu, Aug 17, 2017 at 8:27 AM Konstantin Knizhnik < k.knizh...@postgrespro.ru> wrote: > > > On 29.04.2017 00:13, Douglas Doole wrote: > > If you add this to the commit

Re: [HACKERS] [PATCH] Push limit to sort through a subquery

2017-08-18 Thread Douglas Doole
Thanks for the feedback on my original patch Robert. Here's an updated patch that will tunnel through multiple SubqueryScanStates. - Doug Salesforce On Thu, Aug 17, 2017 at 6:33 PM Robert Haas <robertmh...@gmail.com> wrote: > On Thu, Aug 17, 2017 at 11:36 AM, Douglas Doole <dougdo.

Re: [HACKERS] Cached plans and statement generalization

2017-05-11 Thread Douglas Doole
> > One interesting idea from Doug Doole was to do it between the tokenizer > and parser. I think they are glued together so you would need a way to run > the tokenizer separately and compare that to the tokens you stored for the > cached plan. > When I did this, we had the same problem that the

Re: [HACKERS] Add Roman numeral conversion to to_number

2017-09-15 Thread Douglas Doole
Ah. Sorry I missed them - I'll give them a look. (Won't be able to get to it until Saturday though.) On Thu, Sep 14, 2017 at 10:06 PM Oliver Ford wrote: > I'll fix the brace, but there are two other patches in the first email for > tests and docs. For some reason the commitfest

Re: [HACKERS] Trouble with amcheck

2017-09-14 Thread Douglas Doole
Thanks all. Making and installing the contribs got me rolling again. (I tried "make world" but ran into trouble with the XML docs. But that's pain and suffering for another day.) I'd agree that "make installcheck-world" should imply that all prereqs are met - that's certainsly the normal

Re: [HACKERS] Add Roman numeral conversion to to_number

2017-09-16 Thread Douglas Doole
Oliver, I took a look at your tests and they look thorough to me. One recommendation, instead of having 3999 separate selects to test every legal roman numeral, why not just do something like this: do $$ declare i int; rn text; rn_val int; begin for i in 1..3999 loop rn

Re: [HACKERS] Add Roman numeral conversion to to_number

2017-09-16 Thread Douglas Doole
I finally figured out why docs weren't building on my machine so I was able to take a look at your doc patch too. I think it's fine. Reading it did suggest a couple extra negative tests to confirm that when 'rn' is specified, all other elements are ignored: select to_number('vii7', 'rn9');

[HACKERS] Trouble with amcheck

2017-09-14 Thread Douglas Doole
I just cloned PostgreSQL to a new machine today (Ubuntu 17.04). "make install" and "make check-world" run fine but "make installcheck-world" is having trouble with amcheck: In contrib/amcheck/results: CREATE EXTENSION amcheck; ERROR: could not open extension control file

Re: [HACKERS] [PATCH] Push limit to sort through a subquery

2017-08-24 Thread Douglas Doole
> > No. You can't easily avoid recursion for the merge-append case, since > that has to descend to multiple children. Agreed. That's why it's not in the while loop in my sketch of the suggested rework. > TBH I dislike the fact that > you did the subquery case randomly differently from the

Re: [HACKERS] [PATCH] Push limit to sort through a subquery

2017-08-24 Thread Douglas Doole
> > I don't greatly like the way that the regression test case filters > the output; it hides too much IMO. I'd be inclined to try to return > the EXPLAIN output with as much detail preserved as possible. Maybe > we could use regex substitution on lines of the output to get rid of > stuff that

Re: [HACKERS] [PATCH] Push limit to sort through a subquery

2017-08-23 Thread Douglas Doole
> > Buildfarm members with force_parallel_mode=regress are failing now. I > haven't had a chance to investigate exactly what's going on here, but > I think there are probably several issues: > Not an auspicious beginning for my first patch :-( > 3. However, even if it did that, it would only

Re: [HACKERS] [PATCH] Push limit to sort through a subquery

2017-08-23 Thread Douglas Doole
> > Here's a not-really-tested draft patch for that. > I don't know the parallel code so I'm not going to comment on the overall patch, but a thought on ExecSetTupleBound(). That function is getting a number of cases where we're doing recursion for a single child (result, gather, gather-merge).