Re: the s_lock_stuck on perform_spin_delay

2024-01-10 Thread Andy Fan
Hi Matthias, Thanks for the review! Matthias van de Meent writes: > On Wed, 10 Jan 2024 at 02:44, Andy Fan wrote: >> Hi, >> >> I want to know if Andres or you have plan >> to do some code review. I don't expect this would happen very soon, just >> want

Re: the s_lock_stuck on perform_spin_delay

2024-01-09 Thread Andy Fan
Hi, Robert Haas writes: > On Mon, Jan 8, 2024 at 9:40 PM Andy Fan wrote: >> The singler handler I was refering to is 'CHECK_FOR_INTERRUPTS', Based >> on this, spin_lock and lwlock are acted pretty differently. > > CHECK_FOR_INTERRUPTS() is not a signal handler, hmm, I

Re: the s_lock_stuck on perform_spin_delay

2024-01-08 Thread Andy Fan
Hi, Robert Haas writes: > On Sun, Jan 7, 2024 at 9:52 PM Andy Fan wrote: >> > I think we should add cassert-only infrastructure tracking whether we >> > currently hold spinlocks, are in a signal handler and perhaps a few other >> > states. That'd a

Re: Shared detoast Datum proposal

2024-01-08 Thread Andy Fan
Andy Fan writes: >> >> One of the tests was aborted at CFBOT [1] with: >> [09:47:00.735] dumping /tmp/cores/postgres-11-28182.core for >> /tmp/cirrus-ci-build/build/tmp_install//usr/local/pgsql/bin/postgres >> [09:47:01.035] [New LWP 28182] > > There wa

Re: the s_lock_stuck on perform_spin_delay

2024-01-07 Thread Andy Fan
R_INTERRUPTS(); LWLockRelease(); Even we got ERROR/FATAL in the CHECK_FOR_INTERRUPTS, I think the LWLock are suppose to be released because of the above statement. Am I missing anything? -- Best Regards Andy Fan

Re: Extract numeric filed in JSONB more effectively

2024-01-06 Thread Andy Fan
igured to capture this during commit. After we reach an agreement about the 'catversion.h' stuff, the next version of patch should fix this issue. -- Best Regards Andy Fan

Re: the s_lock_stuck on perform_spin_delay

2024-01-06 Thread Andy Fan
'make check-world'. > - make sure that interrupts can't trigger the stuck lock much quicker, which > afaict can happen today I can't follow this, do you mind explain more about this a bit? -- Best Regards Andy Fan >From 80cf987d1abe2cdae195bd5eea520e28142885b4 Mon Sep 17 00:00:00 200

Re: Shared detoast Datum proposal

2024-01-06 Thread Andy Fan
Hi, vignesh C writes: > On Mon, 1 Jan 2024 at 19:26, Andy Fan wrote: >> >> >> Andy Fan writes: >> >> > >> > Some Known issues: >> > -- >> > >> > 1. Currently only Scan & Join nodes are considered

Re: the s_lock_stuck on perform_spin_delay

2024-01-04 Thread Andy Fan
Hi, Andres Freund writes: > > On 2024-01-04 14:59:06 +0800, Andy Fan wrote: >> My question is if someone doesn't obey the rule by mistake (everyone >> can make mistake), shall we PANIC on a production environment? IMO I >> think it can be a WARNING on a production en

Re: the s_lock_stuck on perform_spin_delay

2024-01-04 Thread Andy Fan
Hi Matthias and Robert, Matthias van de Meent writes: > On Thu, 4 Jan 2024 at 08:09, Andy Fan wrote: >> >> My question is if someone doesn't obey the rule by mistake (everyone >> can make mistake), shall we PANIC on a production environment? IMO I >> think it can b

the s_lock_stuck on perform_spin_delay

2024-01-03 Thread Andy Fan
tgresql.org/message-id/attachment/123659/v5-0001-WIP-Track-relation-sizes-in-shared-memory.patch -- Best Regards Andy Fan >From 7d7fd0f0e9b13a290bfffaec0ad40773191155f2 Mon Sep 17 00:00:00 2001 From: "yizhi.fzh" Date: Thu, 4 Jan 2024 14:33:37 +0800 Subject: [PATCH v1 1/1]

Re: Shared detoast Datum proposal

2024-01-01 Thread Andy Fan
Andy Fan writes: > > Some Known issues: > -- > > 1. Currently only Scan & Join nodes are considered for this feature. > 2. JIT is not adapted for this purpose yet. JIT is adapted for this feature in v2. Any feedback is welcome. -- Best R

Re: Removing const-false IS NULL quals and redundant IS NOT NULL quals

2023-12-28 Thread Andy Fan
ur current infrastructure, I still raise this up in case I missed anything. -- Best Regards Andy Fan

Re: Removing const-false IS NULL quals and redundant IS NOT NULL quals

2023-12-27 Thread Andy Fan
-it-for-mark-d.patch -- Best Regards Andy Fan

Shared detoast Datum proposal

2023-12-27 Thread Andy Fan
etoast_attrs bitmapset, however if someone changes the value directly, like "slot->tts_values[1] = 2;" but without touching the slot's pre_detoast_attrs then troubles comes. The good thing is the detoast can only happen on Scan/Join nodes. so any other slot is impossible

Re: Is a clearer memory lifespan for outerTuple and innerTuple useful?

2023-12-17 Thread Andy Fan
detoast values and but a dedicated memory context pays more costs on the setup, but a more efficient MemoryContextReset. > > On Sun, Dec 17, 2023 at 4:52 PM Andy Fan wrote: > > Andy Fan writes: > > > Andy Fan writes: > > > >> ..., I attached the 2 MemoryContext i

Re: Is a clearer memory lifespan for outerTuple and innerTuple useful?

2023-12-17 Thread Andy Fan
Andy Fan writes: > Andy Fan writes: > >> ..., I attached the 2 MemoryContext in >> JoinState rather than MergeJoinState, which is for the "shared detoast >> value"[0] more or less. >> In order to delimit the scope of this discussion, I attached the

Re: Is a clearer memory lifespan for outerTuple and innerTuple useful?

2023-12-15 Thread Andy Fan
Andy Fan writes: > ..., I attached the 2 MemoryContext in > JoinState rather than MergeJoinState, which is for the "shared detoast > value"[0] more or less. > After thinking more, if it is designed for "shared detoast value" patch (happens on ExecInterpExp

Is a clearer memory lifespan for outerTuple and innerTuple useful?

2023-12-14 Thread Andy Fan
--- @@ -2064,8 +2066,6 @@ typedef struct MergeJoinState TupleTableSlot *mj_MarkedTupleSlot; TupleTableSlot *mj_NullOuterTupleSlot; TupleTableSlot *mj_NullInnerTupleSlot; - ExprContext *mj_OuterEContext; - ExprContext *mj_InnerEContext; } MergeJoinState; /* -- 2.34.1 -- Best Regards Andy Fan

Re: Avoid detoast overhead when possible

2023-12-05 Thread Andy Fan
gt.fsf%40163.com -- Best Regards Andy Fan

Re: A new strategy for pull-up correlated ANY_SUBLINK

2023-10-13 Thread Andy Fan
Hi Tom, Would you like to have a look at this? The change is not big and the optimization has also been asked for many times. The attached is the v5 version and I also try my best to write a good commit message. Here is the commit fest entry: https://commitfest.postgresql.org/45/4268/

Re: A new strategy for pull-up correlated ANY_SUBLINK

2023-10-13 Thread Andy Fan
some agreement > on the above discussion, I think v4 is good for committer to review! > > > Thank you!) I am ready to discuss it. > Actually I meant to discuss the "Unfortunately, I found a request..", looks we have reached an agreement there:) -- Best Regards Andy Fan

Re: A new strategy for pull-up correlated ANY_SUBLINK

2023-10-12 Thread Andy Fan
er replacing the "in" operator with > "=". > I didn't notice much difference between the 'in' and '=', maybe I missed something? > I took the liberty of adding this to your patch and added myself as > reviewer, if you don't mind. > Sure, the patch after your modification looks better than the original. I'm not sure how the test case around "because of got one row" is relevant to the current changes. After we reach to some agreement on the above discussion, I think v4 is good for committer to review! -- Best Regards Andy Fan

Re: Crash in add_paths_to_append_rel

2023-10-09 Thread Andy Fan
gt;param_info without first checking that we have a > > valid cheapest_startup_path. > > I pushed this with just minor adjustments to the comments you wrote. I > didn't feel the need to reiterate on the set_cheapest() comments. > > Thanks Richard for the report and also Thank

Re: Draft LIMIT pushdown to Append and MergeAppend patch

2023-10-08 Thread Andy Fan
On Mon, Oct 9, 2023 at 8:52 AM David Rowley wrote: > On Sun, 8 Oct 2023 at 18:32, Michał Kłeczek wrote: > > On 8 Oct 2023, at 03:33, Andy Fan wrote: > >> For the patches for performance improvement, it is better to provide > >> an example to show h

Re: Draft LIMIT pushdown to Append and MergeAppend patch

2023-10-07 Thread Andy Fan
it is better to provide an example to show how much benefits we can get. As for this case, I'm doubtful it can work as an improvement. 2. Enable LIMIT pushdown for FDW partitions. > The same as above, some testing is helpful. -- Best Regards Andy Fan

Re: Extract numeric filed in JSONB more effectively

2023-10-05 Thread Andy Fan
the commit message. 3. function naming issue. I think I can get it modified once after > all the other issues are addressed. > > [1] https://www.postgresql.org/message-id/d70280648894e56f9f0d12c75090c3d8%40anastigmatix.net -- Best Regards Andy Fan

Re: make add_paths_to_append_rel aware of startup cost

2023-10-04 Thread Andy Fan
On Wed, Oct 4, 2023 at 8:41 AM David Rowley wrote: > On Sun, 1 Oct 2023 at 21:26, Andy Fan wrote: > >> But overall, I'm more inclined to just go with the more simple "add a > >> cheap unordered startup append path if considering cheap startup > >> plans&quo

Re: make add_paths_to_append_rel aware of startup cost

2023-10-01 Thread Andy Fan
onal cost but no gain so far:( -- Best Regards Andy Fan

Re: Report planning memory in EXPLAIN ANALYZE

2023-09-24 Thread Andy Fan
Hi Ashutosh, On Fri, Sep 22, 2023 at 5:56 PM Ashutosh Bapat wrote: > Hi Andy, > Thanks for your feedback. > > On Fri, Sep 22, 2023 at 8:22 AM Andy Fan wrote: > > > > 1). The commit message of patch 1 just says how it does but doesn't > > say why it does. After rea

Re: Questioning an errcode and message in jsonb.c

2023-09-21 Thread Andy Fan
r doesn't match, I don't know. > > Do you mind providing the patch in your mind, and let's just ignore the compatible issue for now. I think that would be pretty helpful for further discussion. -- Best Regards Andy Fan

Re: Report planning memory in EXPLAIN ANALYZE

2023-09-21 Thread Andy Fan
ything clearer. -- Best Regards Andy Fan

Re: Questioning an errcode and message in jsonb.c

2023-09-21 Thread Andy Fan
ng to me, it would be good to see the proposal code as well. The only concern from me is that the new error from newer version is not compatible with the older versions, which may matters matters or doesn't match, I don't know. [0] https://www.postgresql.org/message-id/43a988594ac91a63dc4bb49a94303a42%40anastigmatix.net -- Best Regards Andy Fan

Re: Questioning an errcode and message in jsonb.c

2023-09-19 Thread Andy Fan
'd be pretty willing to see what some more experienced people say about this. I think just documenting the impatible behavior is an option as well. -- Best Regards Andy Fan

Re: make add_paths_to_append_rel aware of startup cost

2023-09-18 Thread Andy Fan
> subquery and so on, If we have more tables to join, we use > cheapest startup cost.". This is what is in my mind now. > > Here is an updated version to show what's in my mind. -- Best Regards Andy Fan v3-0001-make-add_paths_to_append_rel-aware-of-startup_cos.patch Description: Binary data

Re: make add_paths_to_append_rel aware of startup cost

2023-09-18 Thread Andy Fan
On Mon, Sep 18, 2023 at 11:58 AM David Rowley wrote: > On Mon, 18 Sept 2023 at 01:42, Andy Fan wrote: > > On Fri, Sep 15, 2023 at 3:15 PM David Rowley > wrote: > >> Instead of doing that, why don't you just create a completely new > >> AppendPath containing

Re: make add_paths_to_append_rel aware of startup cost

2023-09-17 Thread Andy Fan
Hi David, Thanks for taking a look at this! On Fri, Sep 15, 2023 at 3:15 PM David Rowley wrote: > On Thu, 7 Sept 2023 at 04:37, Andy Fan wrote: > > Currently add_paths_to_append_rel overlooked the startup cost for > creating > > append path, so it may have lost some op

Re: Extract numeric filed in JSONB more effectively

2023-09-14 Thread Andy Fan
hallow copy is removed as well. Things are not addressed yet: 1. the error message handling. 2. if we have chances to optimize _tz functions, I guess no. 3. function naming issue. I think I can get it modified once after all the other issues are addressed. -- Best Regards Andy Fan v14-0002

Re: Support prepared statement invalidation when result types change

2023-09-14 Thread Andy Fan
gh any of the drivers in detail, but I hope my concern is expressed correctly. -- Best Regards Andy Fan

Re: Is it possible to change wal_level online

2023-09-14 Thread Andy Fan
On Thu, Sep 14, 2023 at 9:22 PM Euler Taveira wrote: > On Thu, Sep 14, 2023, at 7:05 AM, Andy Fan wrote: > > Currently it is complained that wal_level changes require an instance > restart, I'm not familiar with this stuff so far and I didn't get any good > information from sear

Re: Buffer ReadMe Confuse

2023-09-14 Thread Andy Fan
read the one and the same buffer page. > > You are right that different transactions can pin the same buffer, but that does not conflict with what the README says, which is talking about once the transaction is completed, all the Pins are removed. -- Best Regards Andy Fan

Is it possible to change wal_level online

2023-09-14 Thread Andy Fan
be the key blocker for this. Basically I agree that changing the wal_level online will be a good experience for users. -- Best Regards Andy Fan

Re: Extract numeric filed in JSONB more effectively

2023-09-14 Thread Andy Fan
On Thu, Sep 14, 2023 at 5:18 AM Chapman Flack wrote: > On 2023-09-04 10:35, Andy Fan wrote: > > v13 attached. Changes includes: > > > > 1. fix the bug Jian provides. > > 2. reduce more code duplication without DirectFunctionCall. > > 3. add

Re: Redundant Unique plan node for table with a unique index

2023-09-13 Thread Andy Fan
so I will start working on UniqueKey stuff very soon, thank you David for remember of this feature! -- Best Regards Andy Fan

Re: make add_paths_to_append_rel aware of startup cost

2023-09-13 Thread Andy Fan
ctly and modify set_subquery_pathlist to do the same for consistency. -- Best Regards Andy Fan

Re: MergeJoin beats HashJoin in the case of multiple hash clauses

2023-09-10 Thread Andy Fan
ualbuckets; innerbucketsize is a fraction of rows in all the rows, so it is between 0.0 and 1.0. and virtualbuckets is the number of buckets in total (when considered the mutli batchs), how is it possible for 'innerbucketsize > virtualbuckets' ? Am I missing something? -- Best Regards Andy Fan

Re: A minor adjustment to get_cheapest_path_for_pathkeys

2023-09-06 Thread Andy Fan
On Wed, Sep 6, 2023 at 8:50 PM Robert Haas wrote: > On Wed, Sep 6, 2023 at 2:45 AM Andy Fan wrote: > > I guess the *valuable* sometimes means the effort we pay is greater > > than the benefit we get, As for this patch, the benefit is not huge (it > > is possible the

make add_paths_to_append_rel aware of startup cost

2023-09-06 Thread Andy Fan
e coverage purposes. Any feedback is welcome! [1] https://www.postgresql.org/message-id/flat/CAKU4AWqEnzhUTxopVhENC3vs6NnYV32+e6GSBtp1rAv0ZNX=m...@mail.gmail.com -- Best Regards Andy Fan v1-0001-make-add_paths_to_append_rel-aware-of-startup-cos.patch Description: Binary data

Re: A minor adjustment to get_cheapest_path_for_pathkeys

2023-09-06 Thread Andy Fan
tribution of the author should be a factor as well. Like Richard has provided lots of performance improvement, bug fix, code reviews, so I believe more attention from committers should be a reasonable request. -- Best Regards Andy Fan

Re: Extract numeric filed in JSONB more effectively

2023-09-06 Thread Andy Fan
RNALOID, 0, >InvalidOid, >COERCE_IMPLICIT_CAST); > > if no need, output typmod (usually -1), so here should be -1 rather than 0? I agree. -1 is better than 0. Thanks for the code level review again! I want to wait for some longer time to gather more feedback. I'm willing to name it better, but hope I didn't rename it to A and rename it back shortly. -- Best Regards Andy Fan

Re: A minor adjustment to get_cheapest_path_for_pathkeys

2023-09-06 Thread Andy Fan
On Wed, Sep 6, 2023 at 2:45 PM Andy Fan wrote:uld have a different conversation... > > I like this consultation, so +1 from me :) > s/consultation/conclusion. -- Best Regards Andy Fan

Re: A minor adjustment to get_cheapest_path_for_pathkeys

2023-09-06 Thread Andy Fan
also want to know what "Ready for Committer" is designed for, and when/who can mark a patch as "Ready for Committer" ? -- Best Regards Andy Fan

Re: Extract numeric filed in JSONB more effectively

2023-09-04 Thread Andy Fan
Hi, v13 attached. Changes includes: 1. fix the bug Jian provides. 2. reduce more code duplication without DirectFunctionCall. 3. add the overlooked jsonb_path_query and jsonb_path_query_first as candidates -- Best Regards Andy Fan v13-0001-optimize-casting-jsonb-to-a-given-type.patch

Re: Extract numeric filed in JSONB more effectively

2023-09-04 Thread Andy Fan
VARDATA_ANY(key),\ VARSIZE_ANY_EXHDR(key), NULL); I will send an updated version soon. -- Best Regards Andy Fan

Re: Extract numeric filed in JSONB more effectively

2023-08-31 Thread Andy Fan
o DirectFunctionCall1. Point b) is the key reason I am not willing to do it. Or do I miss other important reasons? -- Best Regards Andy Fan v12-0001-optimize-casting-jsonb-to-a-given-type.patch Description: Binary data

Re: Extract numeric filed in JSONB more effectively

2023-08-31 Thread Andy Fan
and it looks like a good experience and it may not make things too complicated even if the above things happen IMO. Any feedback is welcome. -- Best Regards Andy Fan v11-0001-optimize-casting-jsonb-to-a-given-type.patch Description: Binary data

Re: Extract numeric filed in JSONB more effectively

2023-08-29 Thread Andy Fan
(Sorry for leaving this discussion for such a long time, how times fly!) On Sun, Aug 27, 2023 at 6:28 AM Chapman Flack wrote: > On 2023-08-22 08:16, Chapman Flack wrote: > > On 2023-08-22 01:54, Andy Fan wrote: > >> After we label it, we will get error like this

Re: Support run-time partition pruning for hash join

2023-08-22 Thread Andy Fan
like we only care about '=' operator which is the most common case, it should be easier than the case here since we just need to know if only 1 partition will survive after pruning, but don't care about which one it is. I'd like to discuss in another thread, and leave this thread for Richard's patch only. -- Best Regards Andy Fan

Re: Support run-time partition pruning for hash join

2023-08-22 Thread Andy Fan
On Tue, Aug 22, 2023 at 5:43 PM Richard Guo wrote: > > On Mon, Aug 21, 2023 at 8:34 PM Andy Fan wrote: > >> This feature looks good, but is it possible to know if we can prune >> any subnodes before we pay the extra effort (building the Hash >> table, for each row

Re: Extract numeric filed in JSONB more effectively

2023-08-21 Thread Andy Fan
l data type. This is kind of in conflict with our goal. So currently the only choices are: PATCH 001 or PATCH 001 + 002. https://www.postgresql.org/message-id/CAKU4AWrs4Pzajm2_tgtUTf%3DCWfDJEx%3D3h45Lhqg7tNOVZw5YxA%40mail.gmail.com -- Best Regards Andy Fan

Re: Extract numeric filed in JSONB more effectively

2023-08-21 Thread Andy Fan
(Just relalized this was sent to chap in private, resent it again). On Mon, Aug 21, 2023 at 6:50 PM Andy Fan wrote: > > > On Mon, Aug 21, 2023 at 11:19 AM Chapman Flack > wrote: > >> On 2023-08-20 21:31, Andy Fan wrote: >> > Highlighting the user case of

Re: Support run-time partition pruning for hash join

2023-08-21 Thread Andy Fan
e inner table first for this optimization? so hash join and sort merge should be OK, but nestloop should be impossible unless I missed something. -- Best Regards Andy Fan

Re: Extract numeric filed in JSONB more effectively

2023-08-21 Thread Andy Fan
true for args in this case, checking the implementation of get_rule_expr, I found PG behavior like this in many places. -- Best Regards Andy Fan

Re: Extract numeric filed in JSONB more effectively

2023-08-20 Thread Andy Fan
this arg, so I put an OID const here", seems nothing is wrong. Compared with the makeRelableType method, I think the current method is more straightforward. Compared with anyelement, it avoids the creation of makeDummyConst which I'm not sure the implementation is alway correct. So I am pr

Re: Extract numeric filed in JSONB more effectively

2023-08-18 Thread Andy Fan
um("0")); + else if (!typByVal) + elog(ERROR, "create dummy const for type %u is not supported.", consttype); + + /* XXX: here I assume constvalue=0 is accessible for const by value type.*/ + c = makeConst(consttype, consttypmod, 0, (int) typLen, val, false,

Re: Extract numeric filed in JSONB more effectively

2023-08-17 Thread Andy Fan
a string. Datum(0) as the constvalue will crash in this sense. That's why makeDummyConst was introduced. > something like "assertion of > 'internal'-to-foo binary coercibility, vouched by a prosupport > function", would that be a bad thing? > I can't follow this as well. Could you provide the function prototype here? -- Best Regards Andy Fan

Re: Extract numeric filed in JSONB more effectively

2023-08-17 Thread Andy Fan
Hi jian: On Thu, Aug 17, 2023 at 12:32 AM jian he wrote: > On Wed, Aug 16, 2023 at 2:28 PM Andy Fan wrote: > > > > update with the correct patch.. > > regression=# select proname, pg_catalog.pg_get_function_arguments(oid) > from pg_proc > where proname

Re: Extract numeric filed in JSONB more effectively

2023-08-16 Thread Andy Fan
update with the correct patch.. v8-0001-optimize-casting-jsonb-to-a-given-type.patch Description: Binary data

Re: Extract numeric filed in JSONB more effectively

2023-08-16 Thread Andy Fan
On Tue, Aug 15, 2023 at 1:24 PM Pavel Stehule wrote: > Hi > > út 15. 8. 2023 v 5:24 odesílatel Andy Fan > napsal: > >> >>> jsonb_extract_xx_type just cares about the argtype, but >>> 'explain select xx' will still access the const->constvalu

Re: Avoid a potential unstable test case: xmlmap.sql

2023-08-15 Thread Andy Fan
ugh there for discussion. -- Best Regards Andy Fan

Re: Avoid a potential unstable test case: xmlmap.sql

2023-08-15 Thread Andy Fan
mespace = '28601'::oid) v2 attached. -- Best Regards Andy Fan v2-0001-Avoid-a-potential-unstable-testcase.patch Description: Binary data

Avoid a potential unstable test case: xmlmap.sql

2023-08-15 Thread Andy Fan
pg_class Output: oid, relname Index Cond: (pg_class.relnamespace = '28601'::oid) Filter: (has_table_privilege(pg_class.oid, 'SELECT'::text) AND (pg_class.relkind = ANY ('{r,m,v}'::"char"[]))) Patch is attached. -- Best Regards Andy Fan v1-0001-Avoid-a-potential-unstable-testcase.patch Description: Binary data

Re: Extract numeric filed in JSONB more effectively

2023-08-15 Thread Andy Fan
ting some thing new & heavy. However I'm open to see what others say. -- Best Regards Andy Fan

Re: Extract numeric filed in JSONB more effectively

2023-08-15 Thread Andy Fan
an > simply implement similar functions for other non atomic types. > What do you mean by "atomic type" here? If you want to introduce some new framework, I think we need a very clear benefit. -- Best Regards Andy Fan

Re: Extract numeric filed in JSONB more effectively

2023-08-14 Thread Andy Fan
/* Mock a valid datum for !constbyval type. */ + if (fexpr->funcresulttype == NUMERICOID) + target->constvalue = DirectFunctionCall1(numeric_in, CStringGetDatum("0")); -- Best Regards Andy Fan v7-0001-optimize-

Re: Regression test collate.icu.utf8 failed on REL_14_STABLE

2023-08-14 Thread Andy Fan
re this run. you can try 'make installcheck' with a pretty clean setup or run 'make check' directly to verify this. -- Best Regards Andy Fan

Re: Extract numeric filed in JSONB more effectively

2023-08-14 Thread Andy Fan
t I'm not sure about the future or if we still have a better solution. v6 is attached. any feedback is welcome! -- Best Regards Andy Fan v6-0001-optimize-casting-jsonb-to-a-given-type.patch Description: Binary data

Re: Extract numeric filed in JSONB more effectively

2023-08-14 Thread Andy Fan
? I guess no. IIUC, our goal will still be missed in this way. -- Best Regards Andy Fan

Re: Extract numeric filed in JSONB more effectively

2023-08-14 Thread Andy Fan
define jsonb_object_field_int2/int8 to avoid this? This is an unresolved issue at the latest patch. -- Best Regards Andy Fan

Re: Extract numeric [field] in JSONB more effectively

2023-08-10 Thread Andy Fan
there opportunities for a similar benefit if applied > over F_JSONB_ARRAY_ELEMENT and/or F_JSONB_EXTRACT_PATH? > Yes, we do have similar opportunities for both functions. v5 attached for this. -- Best Regards Andy Fan v5-0001-optimize-casting-jsonb-to-a-given-type.patch Description: Binary data

Re: Separate memory contexts for relcache and catcache

2023-08-09 Thread Andy Fan
and it doesn't cost much. It would be handy than estimating that by something like select count(*) from pg_class. I think, for example, if we find relcache using too much memory, it is a signal that the user may use too many partitioned tables. -- Best Regards Andy Fan

Re: Extract numeric filed in JSONB more effectively

2023-08-07 Thread Andy Fan
Hi: On Mon, Aug 7, 2023 at 7:51 PM Andy Fan wrote: > Hi Jian: > > Thanks for the review! > > compared with jsonb_numeric. I am wondering if you need a free *jb. >> elog(INFO,"jb=%p arg pointer=%p ", jb, PG_GETARG_POINTER(0)); >> says there two are not

Re: Extract numeric filed in JSONB more effectively

2023-08-07 Thread Andy Fan
e options from more experienced people, this issue also confused me before. and I'm neutral to this now. after we get an agreement on this, I will update the patch accordingly. -- Best Regards Andy Fan

Re: Extract numeric filed in JSONB more effectively

2023-08-07 Thread Andy Fan
; filtered by jsonb_cast_is_optimized. So the message should be changed or it > needs a comment. > Yes, the double check is not necessary, that is removed in the attached v4 patch. -- Best Regards Andy Fan v4-0001-Optimize-extracting-a-given-data-type-from-jsonb.patch Description: Binary data

Re: Extract numeric filed in JSONB more effectively

2023-08-06 Thread Andy Fan
1; Time: 16.847 ms select 1 from tb where (a->'b')::int4 = 1; Time: 17.105 ms select 1 from tb where (a->'b')::int8 = 1; Time: 16.720 ms select 1 from tb where (a->'b')::float4 = 1; Time: 33.409 ms select 1 from tb where (a->'b')::float8 = 1; Time: 34.660 ms -- Best Regards

Re: Extract numeric filed in JSONB more effectively

2023-08-03 Thread Andy Fan
Hi Tom: On Fri, Aug 4, 2023 at 3:13 AM Tom Lane wrote: > Andy Fan writes: > >> If you use explicit cast, then the code should not be hard, in the > >> rewrite stage all information should be known. > > > Can you point to me where the code is for the XML stuff? &g

Re: Extract numeric filed in JSONB more effectively

2023-08-03 Thread Andy Fan
numeric('{"a":11}'->'a'); --fail. > select "numeric"('{"a":11}'::jsonb->'a'); --ok > The double quotes look weird to me. but it looks like a common situation. select numeric('1'::int); -- failed. select "numeric"('1'::int); -- ok. -- Best Regards Andy Fan

Re: Extract numeric filed in JSONB more effectively

2023-08-03 Thread Andy Fan
ng like this or I misunderstood you? > -- Best Regards Andy Fan

Re: Fix incorrect start up costs for WindowAgg paths (bug #17862)

2023-08-03 Thread Andy Fan
On Thu, Aug 3, 2023 at 7:29 PM David Rowley wrote: > Thanks for having a look at this. > > On Thu, 3 Aug 2023 at 18:49, Andy Fan wrote: > > 1. ORDER BY or PARTITION BY > > > > select *, count(two) over (order by unique1) from tenk1 limit 1; > > DEB

Re: Extract numeric filed in JSONB more effectively

2023-08-03 Thread Andy Fan
Hi: On Thu, Aug 3, 2023 at 8:34 PM Chapman Flack wrote: > On 2023-08-03 03:53, Andy Fan wrote: > > I didn't realize timetime types are binary compatible with SQL, > > so maybe we can have some similar optimization as well. > > (It is a pity that timestamp(tz) are not b

Re: Extract numeric filed in JSONB more effectively

2023-08-03 Thread Andy Fan
to know it. Naming operators is a complex task if we add four. > We already have the jsonb_extract_path and jsonb_extract_path_text > function. > I can't follow this. jsonb_extract_path returns a jsonb, which is far away from our goal: return a numeric effectively? I can imagine to usage "anyelement" type too. some like > `jsonb_extract_path_type(jsonb, anyelement, variadic text[] )` > Can you elaborate this please? -- Best Regards Andy Fan

Re: Extract numeric filed in JSONB more effectively

2023-08-03 Thread Andy Fan
@->'a') = 2; 15ms > > What's tb here? > This is my first post. Copy it here again. create table tb (a jsonb); insert into tb select '{"a": 1}'::jsonb from generate_series(1, 10)i; -- Best Regards Andy Fan

Re: Extract numeric filed in JSONB more effectively

2023-08-03 Thread Andy Fan
with just let users use the function directly, like jsonb_field_as_numeric(jsonb, 'filedname') jsonb_field_as_timestamp(jsonb, 'filedname'); jsonb_field_as_timestamptz(jsonb, 'filedname'); jsonb_field_as_date(jsonb, 'filedname'); it can save an operator and sloves the readable issue. -- Best Regards Andy Fan

Re: Fix incorrect start up costs for WindowAgg paths (bug #17862)

2023-08-03 Thread Andy Fan
think the startup_tuples should be independent with the physical path, maybe we can cache it somewhere to save some planning cycles? Thanks for the patch! -- Best Regards Andy Fan

Re: Extract numeric filed in JSONB more effectively

2023-08-02 Thread Andy Fan
with all the existing operators. select 1 from tb where (a->'a')::numeric = 2; 30.56ms select 1 from tb where (a->>'a')::numeric = 2; 29.43ms select 1 from tb where (a@->'a') = 2; 14.80ms [1] https://commitfest.postgresql.org/44/4476/ -- Best Regards Andy Fan v2-0001-Add-j

Re: Extract numeric filed in JSONB more effectively

2023-08-01 Thread Andy Fan
Hi Matthias: On Wed, Aug 2, 2023 at 7:33 AM Andy Fan wrote: > > > On Tue, Aug 1, 2023 at 7:03 PM Matthias van de Meent < > boekewurm+postg...@gmail.com> wrote: > >> On Tue, 1 Aug 2023 at 06:39, Andy Fan wrote: >> > >> > Hi: >> > >> &g

Re: Extract numeric filed in JSONB more effectively

2023-08-01 Thread Andy Fan
On Tue, Aug 1, 2023 at 7:03 PM Matthias van de Meent < boekewurm+postg...@gmail.com> wrote: > On Tue, 1 Aug 2023 at 06:39, Andy Fan wrote: > > > > Hi: > > > > Currently if we want to extract a numeric field in jsonb, we need to use > > the followin

Extract numeric filed in JSONB more effectively

2023-07-31 Thread Andy Fan
s. Is this the right way to go? Testcase, document and catalog version are updated. -- Best Regards Andy Fan v1-0001-Add-jsonb-operator-to-return-a-numeric-directly.patch Description: Binary data

Re: The same 2PC data maybe recovered twice

2023-07-12 Thread Andy Fan
e > same 2pc will cause panic. > > Is the above scenario reasonable, and do you have any good ideas for > fixing this bug? > > Thanks & Best Regard > > -- Best Regards Andy Fan

Re: Fix incorrect start up costs for WindowAgg paths (bug #17862)

2023-04-12 Thread Andy Fan
On Thu, Apr 13, 2023 at 6:09 AM David Rowley wrote: > .On Thu, 13 Apr 2023 at 02:28, Andy Fan wrote: > > The concept of startup_tuples for a WindowAgg looks good to me, but I > > can't follow up with the below line: > > > > + return clamp_row_est(partitio

<    1   2   3   4   5   6   >