On Wed, Apr 29, 2020 at 8:34 AM Andy Fan wrote:
>
>
> On Wed, Apr 29, 2020 at 8:29 AM David Rowley wrote:
>
>> On Thu, 16 Apr 2020 at 14:17, Andy Fan wrote:
>> > V6 also includes:
>> > 1. Fix the comment misleading you mentioned
ith UniqueKey respect.
search
"In the past we have some limited ability to detect the unqiueness after
join,
so that's would be ok. Since we have such ability now, this may be
another
opportunity to improve the join_is_removable function"
I'm checking it today and will have a feedback soon.
[1]
https://www.postgresql.org/message-id/CAKU4AWrGrs0Vk5OrZmS1gbTA2ijDH18NHKnXZTPZNuupn%2B%2Bing%40mail.gmail.com
Best Regards
Andy Fan
On Wed, Apr 29, 2020 at 8:29 AM David Rowley wrote:
> On Thu, 16 Apr 2020 at 14:17, Andy Fan wrote:
> > V6 also includes:
> > 1. Fix the comment misleading you mentioned above.
> > 2. Fixed a concern case for `relation_has_uniquekeys_for` function.
>
> Over
On Sun, Apr 26, 2020 at 5:49 PM David Rowley wrote:
> On Sun, 26 Apr 2020 at 19:08, Andy Fan wrote:
> > If we want to handle this case as well, one of the changes would
> > be it needs to cache multi records for one input parameter, or return
> > one row each time but retur
On Fri, Apr 24, 2020 at 8:10 PM Antonin Houska wrote:
> Andy Fan wrote:
>
> > The more tests on your patch, the more powerful I feel it is!
>
> Thanks for the appreciation. Given the poor progress it's increasingly hard
> for me to find motivation to work on it. I'll try to
nly if the datum really changed,
will it still be semantic correctly.
It's great to see someone working on this.
>
I'd like to have a try.
Best Regards
Andy Fan
On Fri, Apr 24, 2020 at 5:24 PM David Rowley wrote:
> On Fri, 24 Apr 2020 at 15:26, Andy Fan wrote:
> >
> > Actually I have a different opinion to handle this issue, to execute the
> > a > (select avg(a) from tinner where x = touer.x); The drawback of
> current
an that, like. (1, 2, 1) and (1, 1, 2) is same for your suggestion, but
they
are not different in this path. and we also may be think about if we can
get a lower cost if we add a new sort path.
Best Regards
Andy Fan
;
But when we take this action to production case, how to cost this strategy
is
challenge since it can neither reduce the total_cost nor result in a new
PathKey.
I will check other place to see how this kind can be added.
Best Regards
Andy Fan
test.sql
Description: Binary data
v1-0001-Add-a
precious :)Just FYI
[1] https://www.postgresql.org/message-id/9726.1542577...@sss.pgh.pa.us
Best Regards
Andy Fan
more tests on your patch, the more powerful I feel it is! At the same
time,
I think the most difficult part to understand your design is you can accept
any number of generic join clauses, so I guess more explanation on this
part
may be helpful.
At the code level, I did som
On Thu, Apr 16, 2020 at 8:36 PM Ashutosh Bapat
wrote:
> On Thu, Apr 16, 2020 at 7:47 AM Andy Fan wrote:
>
> > (9 rows)
> >
> > With this feature:
> > explain analyze select a, sum(c) from grp2 group by a;
> >
Hi David:
Thanks for your time.
> 1. Out of date comment in join.sql
>
> -- join removal is not possible when the GROUP BY contains a column that is
> -- not in the join condition. (Note: as of 9.6, we notice that b.id is a
> -- primary key and so drop b.c_id from the GROUP BY of the resulting
On Tue, Apr 14, 2020 at 5:12 PM Andy Fan wrote:
>
>
> On Tue, Apr 14, 2020 at 4:58 PM Amit Langote
> wrote:
>
>> On Tue, Apr 14, 2020 at 5:29 PM Andy Fan
>> wrote:
>> > On Tue, Apr 14, 2020 at 3:40 PM Amit Langote
>> wrote:
>> >>
On Tue, Apr 14, 2020 at 4:58 PM Amit Langote
wrote:
> On Tue, Apr 14, 2020 at 5:29 PM Andy Fan wrote:
> > On Tue, Apr 14, 2020 at 3:40 PM Amit Langote
> wrote:
> >> On Tue, Apr 14, 2020 at 4:13 PM Richard Guo
> wrote:
> >> > On Tue, Apr 14, 2020 at 2:44 PM
On Tue, Apr 14, 2020 at 3:40 PM Amit Langote
wrote:
> On Tue, Apr 14, 2020 at 4:13 PM Richard Guo
> wrote:
> > On Tue, Apr 14, 2020 at 2:44 PM Amit Langote
> wrote:
> >> Maybe I am missing something obvious, but is it intentional that
> >> enable_indexscan is checked by cost_index(), that is,
upposed in some past discussions.)
>
> Agreed for remove_useless_groupby_columns(), but we'd need it if we
> wanted to detect functional dependencies in
> check_functional_grouping() using unique indexes.
>
Thanks for the explanation. I will add the removal in the next version of
this
patch.
Best Regards
Andy Fan
The updated patch should fixed all the issues. See the comments below for
more
information.
On Tue, Mar 31, 2020 at 9:44 AM David Rowley wrote:
> On Sun, 29 Mar 2020 at 20:50, Andy Fan wrote:
> > Some other changes made in the new patch:
> > 1. Fixed bug for UniqueKey calcul
Thanks David for your time, I will acknowledge every item you mentioned
with the updated patch. Now I just talk about part of them.
> 1. There seem to be some cases where joins are no longer being
> detected as unique. This is evident in postgres_fdw.out. We shouldn't
> be regressing any of
3. Renamed the field "grantee" as "guarantee".
Best Regards
Andy Fan
v3-0001-Maintain-UniqueKey-at-each-RelOptInfo-this-inform.patch
Description: Binary data
Because I replied the old thread, cfbot run a test based on the old patch
on that thread. I have detached the old thread from commitfest. Reply
this
email again to wake up Mr. cfbot with the right information.
v2-0001-Maintain-the-uniqueness-of-a-Query-from-bottom-to.patch
Description:
I have started the new thread [1] to continue talking about this.
Mr. cfbot is happy now.
[1]
https://www.postgresql.org/message-id/flat/CAKU4AWrwZMAL%3DuaFUDMf4WGOVkEL3ONbatqju9nSXTUucpp_pw%40mail.gmail.com
Thanks
>
On Mon, Mar 23, 2020 at 6:21 PM Andy Fan wrote:
> Greetings.
>
> This thread is a follow-up thread for [1], where I submit a patch for
> erasing the
> distinct node if we have known the data is unique for sure. But since the
> implementation has changed a lot
On Wed, Mar 25, 2020 at 12:41 AM Dmitry Dolgov <9erthali...@gmail.com>
wrote:
> > On Wed, Mar 11, 2020 at 06:56:09PM +0800, Andy Fan wrote:
> >
> > There was a dedicated thread [1] where David explain his idea very
> > detailed, and you can also check tha
>
>
> On Mon, Mar 23, 2020 at 1:55 AM Floris Van Nee
> wrote:
> > I'm unsure which version number to give this patch (to continue with
> numbers from previous skip scan patches, or to start numbering from scratch
> again). It's a rather big change, so one could argue it's mostly a separate
>
-%2BL4L2%2B0xknsEqpfcs9FF7SeiO9TmpeZOg%40mail.gmail.com#f5d97cc66b9cd330add2fbb004a4d107
[2]
https://www.postgresql.org/message-id/CAKU4AWqOORqW900O-%2BL4L2%2B0xknsEqpfcs9FF7SeiO9TmpeZOg%40mail.gmail.com
Best regards
Andy Fan
v1-0001-Maintain-the-uniqueness-of-a-Query-from-bottom-to.patch
Description: Binary data
Hi David:
On Wed, Mar 18, 2020 at 12:13 PM David Rowley wrote:
> On Wed, 18 Mar 2020 at 15:57, Andy Fan wrote:
> > I'm now writing the code for partition index stuff, which
> > is a bit of boring, since every partition may have different unique
> index.
>
> Why i
Hi David:
Thanks for your time.
On Wed, Mar 18, 2020 at 9:56 AM David Rowley wrote:
> On Mon, 16 Mar 2020 at 06:01, Andy Fan wrote:
> >
> > Hi All:
> >
> > I have re-implemented the patch based on David's suggestion/code, Looks
> it
> > works well.
strategy sounds
awesome, but I didn't check the details so far.
5. more clearer commit message.
6. any more ?
Any feedback is welcome, Thanks for you for your any ideas, suggestions,
demo code!
Best Regards
Andy Fan
v4-0001-Patch-Bypass-distinctClause-groupbyClause-if-the-.patch
Descriptio
On Fri, Mar 13, 2020 at 11:46 AM David Rowley wrote:
> On Fri, 13 Mar 2020 at 14:47, Andy Fan wrote:
> > 1. for pupulate_baserel_uniquekeys, we need handle the "pk = Const"
> as well.
> > (relation_has_unqiue_for has a similar logic) currently the follow
Hi David:
On Thu, Mar 12, 2020 at 3:51 PM David Rowley wrote:
> On Wed, 11 Mar 2020 at 17:23, Andy Fan wrote:
> > Now I am convinced that we should maintain UniquePath on RelOptInfo,
> > I would see how to work with "Index Skip Scan" patch.
>
> I've attached a ve
On Tue, Mar 10, 2020 at 4:32 AM James Coleman wrote:
> On Mon, Mar 9, 2020 at 3:56 PM Dmitry Dolgov <9erthali...@gmail.com>
> wrote:
> >
> > Assuming we'll implement it in a way that we do not know about what kind
> > of path type is that in create_distinct_path, then it can also work for
> >
On Wed, Mar 11, 2020 at 6:49 AM David Rowley wrote:
> On Wed, 11 Mar 2020 at 02:50, Ashutosh Bapat
> wrote:
> >
> > On Tue, Mar 10, 2020 at 1:49 PM Andy Fan
> wrote:
> > > In my current implementation, it calculates the uniqueness for each
> > > BaseRe
>
>
> I think the UniqueKeys may need to be changed from using
> EquivalenceClasses to use Exprs instead.
>
When I try to understand why UniqueKeys needs EquivalenceClasses,
see your comments here. I feel that FuncExpr can't be
used to as a UniquePath even we can create unique index on f(a)
and
find this in your patch or in the code.
>
> This is a proposal from David, so not in current patch/code :)
Regards
Andy Fan
t looks at unique
> indexes and group by / distinct clauses.
>
> I can do this after we have agreement on the UniquePath.
For my cbbot failure, another strange thing is "A" appear ahead of "a" after
the order by.. Still didn't find out why.
[1]
https://ci.appveyor.com/project/postgresql-cfbot/postgresql/build/1.0.83298
Regards
Andy Fan
2433/
Regards Andy Fan.
. But since the current patch not tied
with this closely, so I would put this patch for review first.
On Wed, Mar 4, 2020 at 9:13 PM Andy Fan wrote:
>
>
>>
>>> * There are some changes in existing regression cases that aren't
>>> visibly related to the stated p
>
>
>> * There are some changes in existing regression cases that aren't
>> visibly related to the stated purpose of the patch, eg it now
>> notices that "select distinct max(unique2) from tenk1" doesn't
>> require an explicit DISTINCT step. That's not wrong, but I wonder
>> if maybe you should
On Tue, Mar 3, 2020 at 1:24 AM Andy Fan wrote:
> Thank you Tom for the review!
>
> On Mon, Mar 2, 2020 at 4:46 AM Tom Lane wrote:
>
>> Andy Fan writes:
>> > Please see if you have any comments. Thanks
>>
>> The cfbot isn't at all happy with this.
Thank you Tom for the review!
On Mon, Mar 2, 2020 at 4:46 AM Tom Lane wrote:
> Andy Fan writes:
> > Please see if you have any comments. Thanks
>
> The cfbot isn't at all happy with this. Its linux build is complaining
> about a possibly-uninitialized variable, and then gi
On Fri, Feb 28, 2020 at 2:35 PM Richard Guo wrote:
> Hi All,
>
> Currently we will not consider EXPR_SUBLINK when pulling up sublinks and
> this would cause performance issues for some queries with the form of:
> 'a > (SELECT agg(b) from ...)' as described in [1].
>
> So here is a patch as an
. Thanks
On Mon, Feb 24, 2020 at 8:38 PM Andy Fan wrote:
>
>
> On Wed, Feb 12, 2020 at 12:36 AM Ashutosh Bapat <
> ashutosh.bapat@gmail.com> wrote:
>
>>
>>
>> On Tue, Feb 11, 2020 at 8:27 AM Andy Fan
>> wrote:
>>
>>
On Wed, Feb 12, 2020 at 12:36 AM Ashutosh Bapat <
ashutosh.bapat@gmail.com> wrote:
>
>
> On Tue, Feb 11, 2020 at 8:27 AM Andy Fan wrote:
>
>>
>>
>> On Tue, Feb 11, 2020 at 12:22 AM Ashutosh Bapat <
>> ashutosh.bapat@gmail.com> wrote:
>&
On Thu, Feb 13, 2020 at 5:39 PM Julien Rouhaud wrote:
> On Tue, Feb 11, 2020 at 10:06:17PM +0530, Ashutosh Bapat wrote:
> > On Tue, Feb 11, 2020 at 8:27 AM Andy Fan
> wrote:
> >
> > > On Tue, Feb 11, 2020 at 12:22 AM Ashutosh Bapat <
> > &
On Tue, Feb 11, 2020 at 3:56 PM Julien Rouhaud wrote:
> >
> > and if we prepare sql outside a transaction, and execute it in the
> > transaction, the other session can't drop the constraint until the
> > transaction is ended.
>
> And what if you create a view on top of a query containing a
On Tue, Feb 11, 2020 at 3:56 PM Julien Rouhaud wrote:
> On Tue, Feb 11, 2020 at 10:57:26AM +0800, Andy Fan wrote:
> > On Tue, Feb 11, 2020 at 12:22 AM Ashutosh Bapat <
> > ashutosh.bapat@gmail.com> wrote:
> >
> > > I forgot to mention this in th
On Tue, Feb 11, 2020 at 12:22 AM Ashutosh Bapat <
ashutosh.bapat@gmail.com> wrote:
>
>
>>
>> [PATCH] Erase the distinctClause if the result is unique by
>> definition
>>
>
> I forgot to mention this in the last round of comments. Your patch was
> actually removing distictClause from the
en we create
a node for Unique/HashAggregate/Group, we can just create a dummy node?
> 5. Have you tested this OUTER joins, which can render inner side nullable?
>
Yes, that part was missed in the test case. I just added them.
On Thu, Feb 6, 2020 at 11:31 AM Andy Fan wrote:
>
>>
update the patch with considering the semi/anti join.
Can anyone help to review this patch?
Thanks
On Fri, Jan 31, 2020 at 8:39 PM Andy Fan wrote:
> Hi:
>
> I wrote a patch to erase the distinctClause if the result is unique by
> definition, I find this because a user switch th
Hi:
I wrote a patch to erase the distinctClause if the result is unique by
definition, I find this because a user switch this code from oracle
to PG and find the performance is bad due to this, so I adapt pg for
this as well.
This patch doesn't work for a well-written SQL, but some drawback
Hi Hackers:
This is a patch for unique elimination rewrite for distinct query.
it will cost much for a big result set and some times it is not
necessary. The basic idea is the unique node like in the following
can be eliminated.
1. select distinct pk, ... from t;
2. select distinct
On Mon, Jan 13, 2020 at 4:09 PM Peter Eisentraut <
peter.eisentr...@2ndquadrant.com> wrote:
> On 2020-01-13 08:29, Andy Fan wrote:
> > During one of my works for logical rewrite, I want to check if the expr
> > is a given Expr.
> >
> > so the simplest way is:
>
During one of my works for logical rewrite, I want to check if the expr is
a given Expr.
so the simplest way is:
if (expr->opno == 418 && nodeTag(linitial(expr->args)) == T_xxx &&
nodeTag(lsecond(expr->args)) == T_ )
{
..
}
if we write code like above, we may have issues if the oid
Hello Hackers:
I'm reading the code of optimizer and get confused about the 3
functions. add_path/set_cheapest/get_cheapest_fractional_path
add_(partial_)path:
For every relations, optimizer will build path for it and add then call
add_path to the rel->pathlist. during this stage, *it
ate_series(1, 1)i ;
> analyze t_small;
> analyze t_big;
> set max_parallel_workers_per_gather = 0;
>
> On Thu, Nov 28, 2019 at 5:46 PM Andy Fan wrote:
>
>>
>>
>> On Fri, Nov 22, 2019 at 6:51 PM Jinbao Chen wrote:
>>
>>> Hi hacke
On Fri, Nov 22, 2019 at 6:51 PM Jinbao Chen wrote:
> Hi hackers,
>
> I have made a patch to fix the problem.
>
> Added the selection rate of the inner table non-empty bucket
>
> The planner will use big table as inner table in hash join
> if small table have fewer unique values. But this plan is
The optimizer cost model usually needs 2 inputs, one is used to represent
data distribution and the other one is used to represent the capacity of
the hardware, like cpu/io let's call this one as system stats.
In Oracle database, the system stats can be gathered with
On Thu, Nov 21, 2019 at 6:12 PM Tomas Vondra
wrote:
> On Thu, Nov 21, 2019 at 08:30:51AM +0800, Andy Fan wrote:
> >>
> >>
> >> Hm. That actually raises the stakes a great deal, because if that's
> >> what you're expecting, it would requ
On Thu, Oct 31, 2019 at 11:07 PM Tom Lane wrote:
>
>
> Possibly this could be finessed by only trying to find duplicates of
> functions that have high cost estimates. Not sure how high is high
> enough.
can we just add a flag on pg_proc to show if the cost is high or not, if
user are not
>
>
> Hm. That actually raises the stakes a great deal, because if that's
> what you're expecting, it would require planning out both the transformed
> and untransformed versions of the query before you could make a cost
> comparison.
I don't know an official name, let's call it as "bloom
On Wed, Nov 20, 2019 at 8:15 PM Andy Fan wrote:
> Hi Hackers:
>
> First I found the following queries running bad on pg.
>
> select count(*) from part2 p1 where p_size > 40 and p_retailprice >
> (select avg(p_retailprice) from part2 p2 where p2.p_brand=p
Hi Hackers:
First I found the following queries running bad on pg.
select count(*) from part2 p1 where p_size > 40 and p_retailprice >
(select avg(p_retailprice) from part2 p2 where p2.p_brand=p1.p_brand);
the plan is
QUERY PLAN
On Thu, Apr 18, 2019 at 10:09 PM Tom Lane wrote:
> Andy Fan writes:
> > when I fetch from holdable cursor, I found the fact is more complex
> than I
> > expected.
> > ...
> > why the 3rd time is necessary and will the performance be bad due to this
> > desi
when I fetch from holdable cursor, I found the fact is more complex than I
expected.
suppose we fetched 20 rows.
1). It will fill a PortalStore, the dest is not the client, it is the
DestTupleStore, called ExecutePlan once and receiveSlot will be call 20
times.
2). the portal for client then
I find the dependency is complex among header files in PG. At the same
time, I find the existing code still can use the header file very
cleanly/alphabetically. so I probably missed some knowledge here.
for example, when I want the LOCKTAG in .c file, which is defined in
"storage/lock.h".
On Tue, Mar 12, 2019 at 2:36 PM Andy Fan wrote:
> On Tue, Mar 12, 2019 at 1:59 PM Andrey Lepikhov
> wrote:
>
>> On 11/03/2019 18:36, Andy Fan wrote:
>> > Hi:
>> >I need some function which requires some message exchange among
>> > different back
On Tue, Mar 12, 2019 at 1:59 PM Andrey Lepikhov
wrote:
> On 11/03/2019 18:36, Andy Fan wrote:
> > Hi:
> >I need some function which requires some message exchange among
> > different back-ends (connections).
> > specially I need a shared hash map and a message q
notes on the shared hash map: it needs multi writers and multi readers.
On Mon, Mar 11, 2019 at 9:36 PM Andy Fan wrote:
> Hi:
> I need some function which requires some message exchange among
> different back-ends (connections).
> specially I need a shared hash map and a m
Hi:
I need some function which requires some message exchange among different
back-ends (connections).
specially I need a shared hash map and a message queue.
Message queue: it should be many writers, 1 reader. Looks POSIX message
queue should be OK, but postgre doesn't use it. is there
Thanks for the clarification!
On Mon, Mar 11, 2019 at 5:02 PM Thomas Munro wrote:
> On Mon, Mar 11, 2019 at 9:35 PM Andy Fan wrote:
> > and whenever I run a simple query "SELECT test_shm_mq(1024, 'a');"
> >
> > I see the following log
> >
> &
and whenever I run a simple query "SELECT test_shm_mq(1024, 'a');"
I see the following log
2019-03-11 16:33:17.800 CST [65021] LOG: background worker "test_shm_mq"
(PID 65052) exited with exit code 1
does it indicates something wrong?
On Mon, Mar 11, 2019 at 4:3
Works, thank you Thomas! I have spent more than 2 hours on this. do you
know which document I miss for this question?
Thanks
On Mon, Mar 11, 2019 at 4:05 PM Thomas Munro wrote:
> On Mon, Mar 11, 2019 at 8:59 PM Andy Fan wrote:
> > 4. CREATE EXTENSION test_shm_mq; ==> . co
My code is based on commit
zhifan@zhifandeMacBook-Pro ~/g/polardb_clean> git log
commit d06fe6ce2c79420fd19ac89ace81b66579f08493
Author: Tom Lane
Date: Tue Nov 6 18:56:26 2018 -0500
what I did includes:
1. ./configure --enable-debug
2. make world // doesn't see the test_shm_mq on the
DECLARE cur CURSOR with hold FOR SELECT * FROM t;
the "with hold" is designed for this purpose. sorry for this
interruption.
On Sun, Mar 10, 2019 at 4:14 PM Andy Fan wrote:
> for example:
> begin;
> declare cur cursor for select * from t;
> insert into t2 values(
for example:
begin;
declare cur cursor for select * from t;
insert into t2 values(...);
fetch next cur;
commit;
// after this, I can't fetch cur any more.
My question are:
1. Is this must in principle? or it is easy to implement as this in PG?
2. Any bad thing would happen if I keep the
thank you for this information! takes 2 days to read the discussion..
On Wed, Mar 6, 2019 at 3:13 AM legrand legrand
wrote:
> There already are solutions regarding this feature in Postgres
> using "connection pooler" wording
>
> see
>
> pgpool:
currently there is one process per connection and it will not not very good
for some short time connection.In oracle database, it support shared
server which can serve more than 1 users at the same time.
See
https://docs.oracle.com/cd/B28359_01/server.111/b28310/manproc001.htm#ADMIN11166
quier wrote:
> On Fri, Mar 01, 2019 at 07:17:04PM +0800, Andy Fan wrote:
> > for a createStmt, it will call transformCreateStmt, and then
> > heap_create_with_catalog.
> > but looks it just check the if_not_exists in transformCreateStmt.
> >
> > is it designed
for a createStmt, it will call transformCreateStmt, and then
heap_create_with_catalog.
but looks it just check the if_not_exists in transformCreateStmt.
so there is a chance that when the transformCreateStmt is called, the table
is not created, but before the heap_create_with_catalog is called,
code definitely makes the debug much
quicker. Thank you very much!
On Wed, Feb 27, 2019 at 11:35 PM Dilip Kumar wrote:
> On Wed, Feb 27, 2019 at 4:42 PM Andy Fan wrote:
> >
> > actually I'm hacking pg for a function like :
> > 1. define a select query.
> >
actually I'm hacking pg for a function like :
1. define a select query.
2. client ask for some data. and server reply some data. server will do
NOTHING if client doesn't ask any more..
3. client ask some data more data with a batch and SERVER reply some data
then. then do NOTHING.
currently the
Thanks you Andres for your time! this context is free with AllocSetReset
rather than AllocSetDelete, that makes my breakpoint doesn't catch it.
On Wed, Feb 27, 2019 at 2:15 PM Andres Freund wrote:
> On 2019-02-27 14:08:47 +0800, Andy Fan wrote:
> > Hi :
> > I run a query like
Hi :
I run a query like "select * from t" and set the break like this:
break exec_simple_query
break MemoryContextDelete
commands
p context->name
c
end
I can see most of the MemoryContext is relased, but never MessageContext,
when will it be released?
/*
* Create the memory context
501 - 584 of 584 matches
Mail list logo