Hi,
I was looking at this issue:
https://www.postgresql.org/message-id/flat/17888-f72930e6b5ce8c14%40postgresql.org
pfree call on contrib/intarray/_int_gist.c:345
```
if (in != (ArrayType *) DatumGetPointer(entry->key))
pfree(in);
```
leads to BogusFree function call and server
On 15/03/23 21:53, Nathan Bossart wrote:
Did you try building without SIMD support? This is what I see:
list.c: In function ‘list_member_ptr’:
list.c:697:2: warning: ISO C90 forbids mixed declarations and code
[-Wdeclaration-after-statement]
697 | const ListCell
Agree with your points Nathan. Just a headup.
On 14/03/23 03:10, Nathan Bossart wrote:
On Sat, Mar 11, 2023 at 09:41:18AM +, Ankit Kumar Pandey wrote:
1. In list_member_ptr, will it be okay to bring `const ListCell *cell` from
#ifdef USE_NO_SIMD
const ListCell *cell;
#endif
to
Attaching updated patch with a fix for an issue in window function.
I have also fixed naming convention of patch as last patch had
incompatible name.
Note:
1. Pending: Investigation of test cases failures.
Regards,
Ankit
diff --git a/contrib/amcheck/verify_heapam.c b/contrib/amcheck/verify
On 04/01/23 18:10, Ankit Kumar Pandey wrote:
On 29/12/22 20:58, Ankit Kumar Pandey wrote:
>
> On 24/12/22 18:22, Ankit Pandey wrote:
>> Hi,
>>
>> This is a PoC patch which implements distinct operation in window
>> aggregates (without order by and for si
> 7. Is it possible to add a benchmark which shows improvement against
HEAD ?
Please ignore this from my earlier mail, I just saw stats now.
Thanks,
Ankit
The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: not tested
Spec compliant: not tested
Documentation:not tested
Hello,
Adding some review comments:
1. In list_member_ptr, will it be
On 05/03/23 22:21, Tom Lane wrote:
Ankit Kumar Pandey writes:
> From my observation, we only account for data in cost computation but
> not number of columns sorted.
> Should we not account for number of columns in sort as well?
I'm not sure whether simply charging m
Hi Jacktby,
Did you try looking at HeapTupleSatisfiesVisibility function (in
src/backend/access/heap/heapam_visibility.c) ? I think it might give you
some idea.
Thanks,
Ankit
Hi,
This was noticed in
https://www.postgresql.org/message-id/caaphdvo2y9s2ao-bpyo7gmpyd0xe2lo-kflnqx80fcftqbc...@mail.gmail.com
I am bringing it up again.
Consider the following example:
Setup (tuple should be in memory to avoid overshadowing of disk I/O in
the experimentation):
work_me
Hi Andres,
I took a stab at naive version of this but been stuck for sometime now.
I have added logic to sort on first column at first pass,
realloc all tuples and do full sort at second pass, but I am not seeing
any benefit (it is actually regressing) at all.
Tried doing above both at bulk
On 12/02/23 01:59, Andres Freund wrote:
However, tuplesort.c completely breaks that for > 1 column
sorts. While spatial locality for accesses to the ->memtuples array is decent
during sorting, due to qsort's subdividing of the problem, the locality for
access to the tuples is *awful*.
The
Hi all,
While working on sort optimization for window function, it was seen that
performance of sort where
all tuples are in memory was bad when number of tuples were very large [1]
Eg: work_mem = 4 GB, sort on 4 int columns on table having 10 million
tuples.
Issues we saw were as follows
Hi,
I was looking at bug mentioned at
https://www.postgresql.org/message-id/flat/201010112055.o9BKtZf7011251%40wwwmaster.postgresql.org
Issue appears to be in gbt_inet_compress which doesn't store inet
details like ip_family and netmask details in inetKEY and
gbt_inet_consistent which does
> On 30/01/23 11:01, John Naylor wrote:
> Since David referred to L3 size as the starting point of a possible
configuration parameter, that's actually cache-conscious.
Okay, makes sense. I am correcting error on my part.
> I'm not close enough to this thread to guess at the right direction
> On 28/01/23 14:31, John Naylor wrote:
> I recently brought up this older thread (mostly about #1), partly
because of the issues discovered above, and partly because I hope to
make progress on it before feature freeze (likely early April):
>
https://www.postgresql.org/message-id/CAApHDvqXcmz
On 26/01/23 07:40, David Rowley wrote:
You can see from the results that the patched version is not looking
particularly great. I did a 1 million row test and a 10 million row
test. work_mem was 4GB for each, so the sorts never went to disk.
Yes, its lackluster gains are very limited (pret
I think more benchmarking is required
so we can figure out if this is a corner case or a common case
I did some more benchmarks:
#1. AIM: Pushdown column whose size is very high
create table test(a int, b int, c text);
insert into test select a,b,c from generate_series(1,1000)a,
generate_s
On 20/01/23 06:07, David Rowley wrote:
Looking at the patch, you've not added any additional tests. If the
existing tests are all passing then that just tells me that either the
code is not functioning as intended or we have no tests that look at
the EXPLAIN output which can make use of thi
On 19/01/23 18:49, David Rowley wrote:
I think you should write a function like:
bool pathkeys_count_contained_in_unordered(List *keys1, List *keys2,
List **reorderedkeys, int *n_common)
which works very similarly to pathkeys> _count_contained_in, but
populates *reorderedkeys so it cont
On 19/01/23 08:58, David Rowley wrote:
The problem is that the next item looked at is 1 and the value 2 is skipped.
Okay, I see the issue.
I think you are misinterpreting the results, but the main point
remains - it's slower. The explain analyze timing shows the time
between outputting
On 18/01/23 15:12, David Rowley wrote:
I also thought I'd better test that foreach_delete_current() works
with foreach_reverse(). I can confirm that it *does not* work
correctly. I guess maybe you only tested the fact that it deleted the
current item and not that the subsequent loop correct
Hi, this is extension of `teach planner to evaluate multiple windows in
the optimal order` work applied to distinct operation.
Based on discussions before
(https://www.postgresql.org/message-id/flat/CAApHDvr7rSCVXzGfVa1L9pLpkKj6-s8NynK8o%2B98X9sKjejnQQ%40mail.gmail.com#e01327a3053d9281c40f281ef
On 16/01/23 09:48, David Rowley wrote:
I don't think we should touch this. It could significantly increase
the number of indexes that we consider when generating paths on base
relations and therefore *significantly* increase the number of paths
we consider during the join search. What I had i
On 13/01/23 07:48, David Rowley wrote:
It would just care if the
pathkeys were present and return a list of pathkeys not contained so
that an incremental sort could be done only on the returned list and a
Unique on an empty returned list.
In create_final_distinct_paths, presorted keys are
2. Handle Pathkey.pk_strategy and pk_nulls comparison.
3. Test cases
Thanks
Regards,
Ankit Kumar Pandey
ing process and okay to
come up with ideas and fail.
Pghackers can possibly bring up issues in their approach (if discussion
for the issue is not mentioned under
todo item).
--
Regards,
Ankit Kumar Pandey
ve impact
and some benefit in few cases but as mentioned before, if hashagg removes
any sort (which happened due to push down), all gains will be lost
and we will be probably worse off than before.
--
Regards,
Ankit Kumar Pandey
onally,
the total cost is the same for both of these, but the execution time
seems to vary quite a bit.
Even if I pushdown one or two path keys, end result is same cost (which
isn't helping).
--
Regards,
Ankit Kumar Pandey
On 10/01/23 10:53, David Rowley wrote:
On Tue, 10 Jan 2023 at 06:15, Ankit Kumar Pandey wrote:
> Do we have any pending items for this patch now?
I'm just wondering if not trying this when the query has a DISTINCT
clause is a copout. What I wanted to avoid was doing additional
ttached patch with one extra test case (as mentioned above).
Rest of the changes are looking fine.
Ran pgbench again and optimized version still had a lead (168 tps vs 135
tps) in performance.
Do we have any pending items for this patch now?
--
Regards,
Ankit Kumar Pandey
diff --git a/src/bac
On 09/01/23 03:48, David Rowley wrote:
On Mon, 9 Jan 2023 at 06:17, Ankit Kumar Pandey wrote:
I have addressed all points 1-6 in the attached patch.
A few more things:
1. You're still using the 'i' variable in the foreach loop.
foreach_current_index() will work.
On 08/01/23 16:33, David Rowley wrote:
On Sun, 8 Jan 2023 at 23:21, Ankit Kumar Pandey wrote:
Please find attached patch with addressed issues mentioned before.
Here's a quick review:
1. You can use foreach_current_index(l) to obtain the index of the list element.
2. I'd rath
On 08/01/23 21:36, Vik Fearing wrote:
On 1/8/23 11:21, Ankit Kumar Pandey wrote:
Please find attached patch with addressed issues mentioned before.
I am curious about this plan:
+-- ORDER BY's in multiple Window functions can be combined into one
+-- if they are subset of QU
shing them down when it can
reduce the number of sorts. There's no reduction in the number of
sorts in the above plan.
Works as mentioned.
All test cases (newly added and existing ones) are green.
--
Regards,
Ankit Kumar Pandey
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend
On 08/01/23 04:06, David Rowley wrote:
On Sun, 8 Jan 2023 at 05:45, Ankit Kumar Pandey wrote:
Attached patch with test cases.
I can look at this in a bit more detail if you find a way to fix the
case you mentioned earlier. i.e, push the sort down to the deepest
WindowAgg that has
n, as I mentioned before, this is implementation issue. We shouldn't be
skipping optimization if pre-sorted keys are present.
--
Regards,
Ankit Kumar Pandey
On 07/01/23 21:57, Ankit Kumar Pandey wrote:
On 07/01/23 09:58, David Rowley wrote:
>
> The attached patch has no tests added. It's going to need some of
> those.
While writing test cases, I found that optimization do not happen for
case #1
(which is prime candidate for such o
(5 rows)
Patched version does (a) because of is_sorted condition.
If we remove both is_sorted and lnext(activeWindows, l) == NULL conditions,
we get correct results in these two cases.
--
Regards,
Ankit Kumar Pandey
un, 8 Jan 2023 at 00:10, Ankit Kumar Pandey wrote:
>
> On 07/01/23 09:58, David Rowley wrote:
> > You also don't seem to be considering the fact that the query might
> > have a DISTINCT clause.
>
> Major reason for this was that I am not exactly aware of what dist
On 07/01/23 07:59, David Rowley wrote:
On Thu, 5 Jan 2023 at 04:11, Ankit Kumar Pandey wrote:
Attaching test cases for this (+ small change in doc).
Tested this in one of WIP branch where I had modified
select_active_windows and it failed
as expected.
Please let me know if something can
Thanks for looking into this.
On 07/01/23 09:58, David Rowley wrote:
On Sat, 7 Jan 2023 at 02:11, Ankit Kumar Pandey wrote:
On 05/01/23 12:53, David Rowley wrote:
We *can* reuse Sorts where a more strict or equivalent sort order is
available. The question is how do we get the final
would have failed, if present).
Please let me know any feedbacks (I have added some my own concerns in
the comments)
Thanks
--
Regards,
Ankit Kumar Pandey
From 8fb4b6188eda111bee2e47d3e85289064b428702 Mon Sep 17 00:00:00 2001
From: Ankit Kumar Pandey
Date: Fri, 6 Jan 2023 14:30:38 +0530
Subje
o the WindowClause's orderClause.
This is much better solution. I will check
create_one_window_path for the same.
--
Regards,
Ankit Kumar Pandey
On 05/01/23 07:48, Vik Fearing wrote:
On 1/4/23 13:07, Ankit Kumar Pandey wrote:
Also, one thing, consider the following query:
explain analyze select row_number() over (order by a,b),count(*) over
(order by a) from abcd order by a,b,c;
In this case, sorting is done on (a,b) followed by
Attaching test cases for this (+ small change in doc).
Tested this in one of WIP branch where I had modified
select_active_windows and it failed
as expected.
Please let me know if something can be improved in this.
Regards,
Ankit Kumar Pandey
From 7647759eb92e1a0560bcff73b4169be8694f83d8
On 29/12/22 20:58, Ankit Kumar Pandey wrote:
On 24/12/22 18:22, Ankit Pandey wrote:
Hi,
This is a PoC patch which implements distinct operation in window
aggregates (without order by and for single column aggregation, final
version may vary wrt these limitations). Purpose of this PoC is to
a,b) + incremental sort(c)
because even though datum sort is fast, there can be n number of combos
where we won't be doing that.
I might be looking at extreme corner cases though but still wanted to share.
--
Regards,
Ankit Kumar Pandey
king off a TODO item is reason enough.
Make sense, not going further with this.
--
Regards,
Ankit Kumar Pandey
clause.
If they're a subset then we might be able to perform an Incremental
Sort, which is likely much cheaper than a full sort.
So question is, how current implementation is different from desired one?
--
Regards,
Ankit Kumar Pandey
On 03/01/23 08:21, David Rowley wrote:
On Mon, 26 Dec 2022 at 02:04, Ankit Kumar Pandey wrote:
Point #1
In the above query Oracle 10g performs 2 sorts, DB2 and Sybase perform 3
sorts. We also perform 3.
This shouldn't be too hard to do. See the code in
select_active_windows(). Y
stgresql.org/message-id/20120821.121611.501104647612634419.t-is...@sraoss.co.jp
+1. It would also be popular with our academic users.
There could be potential for this as well.
--
Regards,
Ankit Kumar Pandey
estion is welcomed.
Also, I did some search around this and there is general sense of
differentiation between overlap and touch of geometric figures. I am not
able to find any function which can determine if two geometric figures
touch each
other at a point (and if there is real use case of
On 31/12/22 23:32, Tom Lane wrote:
Ankit Kumar Pandey writes:
This is in reference to BUG #5705 and corresponding todo item: Fix
/contrib/btree_gist's implementation of inet indexing
I am not able to repro this issue.
You didn't test it right: the complaint is about the btree_gist
Index Cond: (a < '1.0.0.0'::inet)
Heap Fetches: 1
Planning Time: 0.056 ms
Execution Time: 0.044 ms
(5 rows)
Gist index works fine as opposed to issue reported in the bug. Bug
should be marked as resolved and todo item can be removed.
--
Regards,
Ankit Kumar Pandey
3.
This is my first-time contribution. Please let me know if anything can be
improved as I`m eager to learn.
Regards,
Ankit Kumar Pandey
Hi all,
I know everyone is busy with holidays (well, Happy Holidays!) but I will
be glad if someone can take a quick look at th
that would likely be too verbose to be very
helpful.
Maybe we could add verbose option too and this could be one of the
output. Are there any more relevant information
that you could think of which can be included here?
Also, inputs from other hackers are welcomed here.
--
Regards,
Ankit Kumar Pandey
eedback is appreciated.
--
Regards,
Ankit Kumar Pandey
From 420f0ed81d233bf63a10104d1164726b138bce83 Mon Sep 17 00:00:00 2001
From: Ankit Kumar Pandey
Date: Sun, 25 Dec 2022 21:26:32 +0530
Subject: [PATCH] Improve ability to display optimizer analysis using
OPTIMIZER_DEBUG
Currently optimizer logs are
if I understand this fully but does it means proposed todo
(to use indexes) should be refined to
teach planner to take into account of cost model while deciding to use
index or not in window functions? Meaning not always go with index route
(modify point #2)?
--
Regards,
Ankit Kumar Pandey
On 04/12/22 22:25, Vik Fearing wrote:
On 12/4/22 14:34, Ankit Kumar Pandey wrote:
This looks like way to go that would ensure main use case of
portability from Oracle.
The goal should not be portability from Oracle, but adherence to the
standard.
Yes, Vik. You are right. Wrong remark
BY,
I need to dig deeper into order by case.
--
Regards,
Ankit Kumar Pandey
only one distinct aggregate
instead of all, in case of multiple aggregates using distinct?
--
Regards,
Ankit Kumar Pandey
t sure how much
this extra memory and compute usage is considered as acceptable.
--
Regards,
Ankit Kumar Pandey
On 02/12/22 03:07, David Rowley wrote:
On Fri, 2 Dec 2022 at 08:10, Ankit Kumar Pandey wrote:
select avg(distinct id) over (partition by name) from mytable (in oracle db)
yields:
2
2
2
2
10
From this, it is seen distinct is taken across the all rows in the partition.
Due to the lack of
On 02/12/22 00:40, Ankit Kumar Pandey wrote:
On 25/11/22 11:00, Ankit Kumar Pandey wrote:
On 25/11/22 02:14, David Rowley wrote:
On Fri, 25 Nov 2022 at 06:57, Ankit Kumar Pandey
wrote:
Please let me know any opinions on this.
I think if you're planning on working on this then s
On 25/11/22 11:00, Ankit Kumar Pandey wrote:
On 25/11/22 02:14, David Rowley wrote:
On Fri, 25 Nov 2022 at 06:57, Ankit Kumar Pandey
wrote:
Please let me know any opinions on this.
I think if you're planning on working on this then step 1 would have
to be checking the SQL standard t
On 25/11/22 02:14, David Rowley wrote:
On Fri, 25 Nov 2022 at 06:57, Ankit Kumar Pandey wrote:
Please let me know any opinions on this.
I think if you're planning on working on this then step 1 would have
to be checking the SQL standard to see which set of rows it asks
implementatio
On 23/11/22 23:48, Ankit Kumar Pandey wrote:
Hello,
I have questions regarding distinct operation and would be glad if
someone could help me out.
Consider the following table (mytable):
id, name
1, A
1, A
2, B
3, A
1, A
If we do /select avg(id) over (partition by name) from mytable
--
Regards,
Ankit Kumar Pandey
On 16/11/22 00:26, Tom Lane wrote:
Ankit Kumar Pandey writes:
I am looking at todo item (#1) /Implement DISTINCT clause in window
aggregates/ and while looking at code, I found distinct tightly coupled
with Agg function. Looking at another todo item(#2) /Do we really need
so much duplicated
Hello,
I am looking at todo item (#1) /Implement DISTINCT clause in window
aggregates/ and while looking at code, I found distinct tightly coupled
with Agg function. Looking at another todo item(#2) /Do we really need
so much duplicated code between Agg and WindowAgg/? I was wondering
what
) and increase
pos to 22 (4 Mb). Don't forget to update MAXSTRLEN and MAXSTRPOS
accordingly.
I'm attaching a patch herewith. I will be glad to get some feedback on this.
Thanks,
Ankit
From 6eb6db71bd54c23ebfed545e730806229e67210e Mon Sep 17 00:00:00 2001
From: Ankit Kumar Pandey
Date: T
Hi David,
This is review of speed up releasing of locks patch.
Contents & Purpose:
Subject is missing in patch. It would have been easier to understand purpose
had it been included.
Included in the patch are change in README, but no new tests are included..
Initial Run:
The patch applies cleanl
The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: tested, passed
Documentation:tested, passed
Hi Peter,
This is a review of the pclose return value check p
74 matches
Mail list logo
