Re: FullTransactionIdAdvance question

Hi Andres: > On 2024-09-20 17:38:40 +0800, Andy Fan wrote: >> static inline void >> FullTransactionIdAdvance(FullTransactionId *dest) >> { .. >> } >> >> I understand this functiona as: 'dest->value++' increases the epoch when >> necessar

FullTransactionIdAdvance question

r than FirstNormalTransactionId looks strange as well. IIUC, should we remove it to save a prediction on each GetNewTransactionId call? -- Best Regards Andy Fan

Re: detoast datum into the given buffer as a optimization.

Jubilee Young writes: > On Wed, Sep 18, 2024 at 2:23 PM Nathan Bossart > wrote: >> >> On Wed, Sep 18, 2024 at 05:35:56PM +0800, Andy Fan wrote: >> > Currently detoast_attr always detoast the data into a palloc-ed memory >> > and then if user wants the deto

Re: detoast datum into the given buffer as a optimization.

Thank you all for the double check. > Andy Fan writes: >> * Note if caller provides a non-NULL buffer, it is the duty of caller >> * to make sure it has enough room for the detoasted format (Usually >> * they can use toast_raw_datum_size to get the size) > > ...

detoast datum into the given buffer as a optimization.

om [3] https://www.postgresql.org/message-id/6718759c-2dac-48e4-bf18-282de4d82204%40enterprisedb.com -- Best Regards Andy Fan

Re: Make printtup a bit faster

ose). so I want some of you can have a double check on these function bodies, if anything wrong, I can change it easlier (vs I made the same efforts on all the type function). does it make sense? Patch 0001 ~ 0003 is something related and can be reviewed or committed seperately. and 0004 is the main

Re: Extract numeric filed in JSONB more effectively

,4,8}/float{4,8} in pg_proc for '->' operator, not only numeric. 2. user may use OpExpr, like (jb->'x')::numeric, user may also use FuncExpr, like (jsonb_object_field(a, 'x'))::numeric. -- Best Regards Andy Fan

Re: Make printtup a bit faster

Hello David & Andreas, > On 8/29/24 1:51 PM, David Rowley wrote: >> I had planned to work on this for PG18, but I'd be happy for some >> assistance if you're willing. > > I am interested in working on this, unless Andy Fan wants to do this > work. :) I b

Re: Make printtup a bit faster

Andy Fan writes: > The attached is PoC of this idea, not matter which method are adopted > (rewrite all the outfunction or a optional print function), I think the > benefit will be similar. In the blew test case, it shows us 10%+ > improvements. (0.134ms vs 0.110ms) After working o

Re: Make printtup a bit faster

m back again for > output strings larger than L1. The attached is PoC of this idea, not matter which method are adopted (rewrite all the outfunction or a optional print function), I think the benefit will be similar. In the blew test case, it shows us 10%+ improvements. (0.134ms vs 0

Re: Make printtup a bit faster

David Rowley writes: > On Fri, 30 Aug 2024 at 13:04, Andy Fan wrote: >> >> David Rowley writes: >> > If there's anywhere we call output functions >> > where the resulting value isn't directly appended to a StringInfo, >> > then we could j

Re: Make printtup a bit faster

David Rowley writes: > On Fri, 30 Aug 2024 at 12:10, Andy Fan wrote: >> What would be the extra benefit we redesign all the out functions? > > If I've understood your proposal correctly, it sounds like you want to > invent a new "print" output function for each

Re: Make printtup a bit faster

PG18, but I'd be happy for some > assistance if you're willing. I see you did many amazing work with cache-line-frindly data struct design, branch predition optimization and SIMD optimization. I'd like to try one myself. I'm not sure if I can meet the target, what if we handle the out/in function separately (can be by different people)? -- Best Regards Andy Fan

Make printtup a bit faster

some infrastructure changes. the memcpy in step 4 is: "1.27% __memcpy_avx_unaligned_erms" in my above case. What do you think? -- Best Regards Andy Fan

Re: New function normal_rand_array function to contrib/tablefunc.

Japin Li writes: > On Wed, 28 Aug 2024 at 12:27, Andy Fan wrote: >> Japin Li writes: >> > Nitpick, the minlen is smaller than maxlen, so the maxlen cannot be zero. > > After giving it some more thought, it would also be helpful if maxlen is > equal to minlen. > &

Re: Parallel CREATE INDEX for GIN indexes

where the patch doesn't help otherwise). But then in other cases it > doesn't help at all, and 0010 helps. Yes, I'd like to see these improvements both 0008 and 0010 as a dedicated improvement. -- Best Regards Andy Fan

Re: New function normal_rand_array function to contrib/tablefunc.

errmsg("minlen and maxlen must be greater than > zero."))); > > Here the minlen might be zero, so the error message is incorrect. > How about use "minlen must be greater than or equal to zero"? Yes, you are right. A new version is attached, thanks for

Re: Parallel CREATE INDEX for GIN indexes

) we'll be able to see a meaningfully larger performance > improvement. Personally I am more fans of your "buffer writetup" idea, but not the same interests with the tuplesort_beginsortedrun / tuplesort_endsortedrun. I said the '3%' is for the later one and I guess you understand it as the former one. > >> So my option is if we can have agreement on 0008, then we can final >> review/test on the existing code (including 0009), and leave further >> improvement as a dedicated patch. > > As mentioned above, I think I could update the patch for a btree > implementation that also has immediate benefits, if so desired? If you are saying about the buffered-writetup in tuplesort, then I think it is great, and in a dedicated thread for better exposure. -- Best Regards Andy Fan

Re: Parallel CREATE INDEX for GIN indexes

-sort tuples into tape directly rather than inserting them into tuplesort's memory and dump them into tape without a sort. However I can't define a clean API for the former case. c). create-index is a maintenance work, improving it by 30% would be good, but if we just improve it by <3, it looks not very charming in practice. So my option is if we can have agreement on 0008, then we can final review/test on the existing code (including 0009), and leave further improvement as a dedicated patch. What do you think? [1] https://www.postgresql.org/message-id/87le0iqrsu.fsf%40163.com -- Best Regards Andy Fan

Re: New function normal_rand_array function to contrib/tablefunc.

Andy Fan writes: >>> My suggestion would be to mirror the signatures of the core random() >>> functions more closely, and have this: >>> >>> 1). rand_array(numvals int, minlen int, maxlen int) >>> returns setof float8[] >>> > .

Re: New function normal_rand_array function to contrib/tablefunc.

gestion in the new attached version. They are not only some cleaner APIs for user and but also some cleaner implementation in core, Thank for this suggestion as well. Sorry for the late response, just my new posistion is bit of busy that I don't have enough time on community work. -- Best Regards

Measure the servers's IO performance

thers. pg_read_binary_file is better, but file system cache still there. should we expose a direct-io option for pg_read_binary_file? -- Best Regards Andy Fan

Re: Seq scan instead of index scan querying single row from primary key on large table

tartup_cost in cost_seqscan, I must be wrong now, but I want to know where is it. > and I'm far from sure that it would not have any negative > side-effects. Yes, I think it is a semantics correct than before however. -- Best Regards Andy Fan

Re: Comment in portal.h

les for a held cursor or a PORTAL_ONE_RETURNING, > PORTAL_ONE_MOD_WITH, or PORTAL_UTIL_SELECT query.". Attached is a > patch for that. Patch looks good to me. All the codes of PortalRun & FillPortalStore & PortalRunSelect are consistent with this idea. -- Best Regards Andy Fan

Re: Optimize WindowAgg's use of tuplestores

key' change can cause noticeable change, especially there is just one function call in the 'if-statement' (I am thinking more instrucments in the if-statement body, more changes it can cause). + if (unlikely(winstate->buffer == NULL)) + prepare_tuplestore(winstate); -- Best Regards Andy Fan

Re: New function normal_rand_array function to contrib/tablefunc.

Andy Fan writes: (just noticed this reply is sent to Jim privately, re-sent it to public.) > Hi Jim, > >> >> When either minval or maxval exceeds int4 the function cannot be >> executed/found >> >> SELECT * FROM normal_rand_array(5, 10, 8, 42::bigint); >

Re: New function normal_rand_array function to contrib/tablefunc.

is doing. OK, you are right, your new names should be better. > Also, the function accepts float8 minval and maxval arguments, and > then simply ignores them and returns random float8 values in the range > [0,1), which is highly counterintuitive. This is a obvious bug and it only exists in float8 case IIUC, will fix it in the next version. -- Best Regards Andy Fan

Re: Parallel CREATE INDEX for GIN indexes

Andy Fan writes: I just realize all my replies is replied to sender only recently, probably because I upgraded the email cient and the short-cut changed sliently, resent the lastest one only >>> Suppose RBTree's output is: >>> >>> batch-1 at RBTree: &

Re: Make tuple deformation faster

d of CPUs. a). Intel Xeon Processor (Icelake) for my ECS b). Intel(R) Core(TM) i5-8259U CPU @ 2.30GHz at Mac. My ECS reports " branch-misses", probabaly because it runs in virtualization software , and Mac doesn't support perf yet :( -- Best Regards Andy Fan

Re: Parallel CREATE INDEX for GIN indexes

find anything wrong in the currrent patch, and the above stuff can be categoried into "furture improvement" even it is worthy to. -- Best Regards Andy Fan >From 48c2e03fd854c8f88f781adc944f37b004db0721 Mon Sep 17 00:00:00 2001 From: Andy Fan Date: Sat, 8 Jun 2024 13:21:08 +080

Re: Make tuple deformation faster

much it can improve in an ideal case, is it possible to forecast it somehow? I ask it here because both cases are optimizing for CPU cache.. -- Best Regards Andy Fan

Re: Question about maxTapes & selectnewtape & dumptuples

Heikki Linnakangas writes: > On 30/06/2024 12:48, Andy Fan wrote: >> for example, at the first use of outputTapes[x], it stores (1, 3, 5, >> 7), >> and later (2, 4, 6, 8) are put into it. so the overall of (1, 3, 5, 7, >> 2, 4, 6, 8) are not sorted? Where di

Question about maxTapes & selectnewtape & dumptuples

the first use of outputTapes[x], it stores (1, 3, 5, 7), and later (2, 4, 6, 8) are put into it. so the overall of (1, 3, 5, 7, 2, 4, 6, 8) are not sorted? Where did I go wrong? -- Best Regards Andy Fan

Re: cost delay brainstorming

Andy Fan writes: > >> - Longrunning transaction prevents increasing relfrozenxid, we run autovacuum >> over and over on the same relation, using up the whole cost budget. This is >> particularly bad because often we'll not autovacuum anything else, building &

Re: cost delay brainstorming

les > having been cleaned up by on-access pruning. Good to know this case. if we update the pg_stats_xx metrics when on-access pruning, would it is helpful on this? > - Larger tables with occasional lock conflicts cause autovacuum to be > cancelled and restarting from scratch over and over. If that happens before > the second table scan, this can easily eat up the whole cost budget without > making forward progress. Off-peak time + manual vacuum should be helpful I think. -- Best Regards Andy Fan

Re: Shared detoast Datum proposal

Andres doesn't have such time so far:( > Robert Haas writes: > >> On Wed, May 22, 2024 at 9:46 PM Andy Fan wrote: >>> Please give me one more chance to explain this. What I mean is: >>> >>> Take SELECT f(a) FROM t1 join t2...; for example, >>>

configure error when CFLAGS='-Wall -Werror

t support '-qlanglvl' all the time, why removing the CFLAGS matters. 2. If you are using clang as well, what CFLAGS you use and it works? for example: IIRC, clang doesn't report error when a variable is set but no used by default, we have to add some extra flags to make it. -- Best Regards Andy Fan

New function normal_rand_array function to contrib/tablefunc.

5 means it needs to produce 5 rows in total and the 10 is the average array length, and 1.8 is the minvalue for the random function and 3.5 is the maxvalue. -- Best Regards Andy Fan >From 397dcaf67f29057b80aebbb6116b49ac8344547c Mon Sep 17 00:00:00 2001 From: Andy Fan Date: Sat, 8 Jun 2024

differential test coverage when working on a patch

ov at [1] and the options like '--diff-file' or '--select-script' looks very promising, but all of them needs some time to try it out and then automate it. so I'd like to ask first.. [1] https://github.com/linux-test-project/lcov/blob/master/README -- Best Regards Andy Fan

Re: why memoize is not used for correlated subquery

e big improvement for planning a big number of partitioned table. -- Best Regards Andy Fan

Re: Parallel CREATE INDEX for GIN indexes

d enough for its purpose? If so, we can save the memory for OffsetNumber for each GinTuple. Item 5) and 6) needs some coding and testing. If it is OK to do, I'd like to take it as an exercise in this area. (also including the item 1~4.) -- Best Regards Andy Fan

Re: Shared detoast Datum proposal

Robert Haas writes: > On Wed, May 22, 2024 at 9:46 PM Andy Fan wrote: >> Please give me one more chance to explain this. What I mean is: >> >> Take SELECT f(a) FROM t1 join t2...; for example, >> >> When we read the Datum of a Var, we read it from tts->tts_

Re: using extended statistics to improve join estimates

Andrei Lepikhov writes: > On 20/5/2024 15:52, Andy Fan wrote: >> Hi Andrei, >> >>> On 4/3/24 01:22, Tomas Vondra wrote: >>>> Cool! There's obviously no chance to get this into v18, and I have stuff >>>> to do in this CF. But I'll take

Re: Shared detoast Datum proposal

Nikita Malakhov writes: > Hi, > Andy, glad you've not lost interest in this work, I'm looking > forward to your improvements! Thanks for your words, I've adjusted to the rhythm of the community and welcome more feedback:) -- Best Regards Andy Fan

Re: Shared detoast Datum proposal

Robert Haas writes: > On Tue, May 21, 2024 at 10:02 PM Andy Fan wrote: >> One more things I want to highlight it "syscache" is used for metadata >> and *detoast cache* is used for user data. user data is more >> likely bigger than metadata, so cache size contr

Re: Shared detoast Datum proposal

Andy Fan writes: > Hi Robert, > >> Andy Fan asked me off-list for some feedback about this proposal. I >> have hesitated to comment on it for lack of having studied the matter >> in any detail, but since I've been asked for my input, here goes: > > Thank

Re: Shared detoast Datum proposal

Hi Robert, > Andy Fan asked me off-list for some feedback about this proposal. I > have hesitated to comment on it for lack of having studied the matter > in any detail, but since I've been asked for my input, here goes: Thanks for doing this! Since we have two totally dif

Re: using extended statistics to improve join estimates

ot, clauses, varRelid, jointype, + rather than + if (clauselist_selectivity_hook) + *return* clauselist_selectivity_hook(root, clauses, ..) ? -- Best Regards Andy Fan

Re: UniqueKey v2

ast, this probably is my first non-trival patchs which has multiple authors, I don't want myself is the bottleneck for the coorperation, so if you need something to do done sooner, please don't hesitate to ask me for it explicitly. Here is my schedule about this. I can provide the next version based our discussion and your patches at the eariler of next week. and update the UniqueKey.README to make sure the overall design clearer. What I hope you to pay more attention is the UniqueKey.README besides the code. I hope the UniqueKey.README can reduce the effort for others to understand the overall design enormously. -- Best Regards Andy Fan

Re: UniqueKey v2

s any value (NULL > or NOT NULL) because it describes the whole relation rather than particular > row. I consider UniqueKey to be a set of expressions. How about > uniquekey_expression_nullable() ? uniquekey_expression_nullable() is a better name, I will use it in the next version. IIUC, we have reached to the agreement based on your latest response for the most of the questions. Please point me if I missed anything. >> > Of course one problem is that the number of combinations can grow >> > exponentially as new relations are joined. >> >> Yes, that's why "rule 1" needed and "How to reduce the overhead" in >> UniqueKey.README is introduced. > > What if we are interested in unique keys of a subquery, but the subquery has > no DISTINCT clause? I agree we should remove the prerequisite of "use_for_distinct". -- Best Regards Andy Fan

Re: First draft of PG 17 release notes

Bruce Momjian writes: > On Sat, May 11, 2024 at 01:27:25PM +0800, Andy Fan wrote: >> >> Hello Bruce, >> >> > I have committed the first draft of the PG 17 release notes; you can >> > see the results here: >> > >> >https://m

Re: Parallel CREATE INDEX for GIN indexes

ckTableScanWorkerData.phsw_chunk_size is designed for this. > The problem is that if the scan wraps around, then one of the TID lists > for a given worker will have the min TID and max TID, so it will overlap > with every other TID list for the same key in that worker. And when the > worker does the merging, this list will force a "full" merge sort for > all TID lists (for that key), which is very expensive. OK. Thanks for all the answers, they are pretty instructive! -- Best Regards Andy Fan

Re: First draft of PG 17 release notes

hink we need to add the following 2 items? - 9f133763961e280d8ba692bcad0b061b861e9138 this is an optimizer transform improvement. - a8a968a8212ee3ef7f22795c834b33d871fac262 this is an optimizer costing improvement. Both of them can generate a better plan on some cases. -- Best Regards Andy Fan

Re: Parallel CREATE INDEX for GIN indexes

llow this, I won't object ... Agree with this. I am more interested with understanding the whole design and the scope to fix in this patch, and then I can do some code review and testing, as for now, I still in the "understanding design and scope" stage. If I'm too slow about this patch, please feel free to commit it any time and I don't expect I can find any valueable improvement and bugs. I probably needs another 1 ~ 2 weeks to study this patch. -- Best Regards Andy Fan

Re: Parallel CREATE INDEX for GIN indexes

ker 1-block by 1-block, we will have a serious issue like here. If we can have N-block by N-block, and N-block is somehow fill the work_mem which makes the dedicated temp file, we can make things much better, can we? -- Best Regards Andy Fan

Re: Parallel CREATE INDEX for GIN indexes

he WORKER dump the tuples into Sharedsort struct and let the LEADER merge them directly. I think this aim of this design is it is potential to save a mergeruns. In the current patch, worker dump to local tuplesort and mergeruns it and then leader run the merges again. I admit the goal of this patch is reasonable, but I'm feeling we need to adapt this way conditionally somehow. and if we find the way, we can apply it to btbuild as well. -- Best Regards Andy Fan

Re: UniqueKey v2

heory is that relation is single-row if it has an UK such that each of > its ECs meets at least one of the following conditions: > > a) contains a constant True. > > b) contains a column of a relation which has already been proven single-row. True, not sure if it is easy to tell. > b) is referenced by an UK of a relation which has already been proven > single-row. I can't follow here... > > I think that in the example above, an EC {t1.e, t2.id} should exist. So when > checking whether 't2' is single-row, the condition b) cam be ised: the UK of > 't2' should reference the EC {t1.e, t2.id}, which in turn contains the > column t1.e. And 't1' is unique because its EC meets the condition a). (Of > course you need to check em_jdomain before you use particular EM.) I think the existing rule 1 for joinrel works well with the singlerow case naturally, what can be improved if we add the theory you suggested here? -- Best Regards Andy Fan

Re: using extended statistics to improve join estimates

Hello Justin, Thanks for showing interest on this! > On Sun, Apr 28, 2024 at 10:07:01AM +0800, Andy Fan wrote: >> 's/estimiatedcluases/estimatedclauses/' typo error in the >> commit message is not fixed since I have to regenerate all the commits > > Maybe y

Re: a wrong index choose when statistics is out of date

Andy Fan writes: > Hello everyone, > >> After some more thoughts about the diference of the two ideas, then I >> find we are resolving two different issues, just that in the wrong index >> choose cases, both of them should work generally. > > Here is the fo

Re: using extended statistics to improve join estimates

t > observing any errors or crashes. Good to know that. > I'll try to look harder at the next patch revision. Thank you! -- Best Regards Andy Fan

Re: New committers: Melanie Plageman, Richard Guo

much success and few reverts! > Congratulations to both, Well deserved! -- Best Regards Andy Fan

Re: UniqueKey v2

list if you do that. Thanks for your review suggestion, I will get to this very soon if once I get time, I hope it is in 4 weeks. [1] https://www.postgresql.org/message-id/7mlamswjp81p.fsf%40e18c07352.et15sqa -- Best Regards Andy Fan

Re: Extract numeric filed in JSONB more effectively

Andy Fan writes: > Here is latest version, nothing changed besides the rebase to the latest > master. The most recent 3 questions should be addressed. > > - The error message compatible issue [1] and the Peter's answer at [2]. > - Peter's new question at [2] and my answ

Re: Replace FunctionCall2Coll with FunctionCallInvoke

Hello Michael, > [[PGP Signed Part:Undecided]] > On Mon, Apr 08, 2024 at 12:25:00PM +0800, Andy Fan wrote: >> During the review of using extended statistics to improve join estimates >> [1], I found some code level optimization opportunities which apply to >> existi

Replace FunctionCall2Coll with FunctionCallInvoke

if such changes is necessary at the first place. [1] https://www.postgresql.org/message-id/c8c0ff31-3a8a-7562-bbd3-78b2ec65f16c%40enterprisedb.com -- Best Regards Andy Fan >From e852ce631f9348d5d29c8a53090ee71f7253767c Mon Sep 17 00:00:00 2001 From: "yizhi.fzh" Date: M

Re: [HACKERS] make async slave to wait for lsn to be replayed

replay_wait() and spreading words about it > at conferences would be highly appreciated. Sure, once it is committed, I promise I can doing a knowledge sharing in our organization and write a article in chinese language. -- Best Regards Andy Fan

Re: using extended statistics to improve join estimates

Tomas Vondra writes: > On 4/2/24 10:23, Andy Fan wrote: >> >>> On Wed, Mar 02, 2022 at 11:38:21AM -0600, Justin Pryzby wrote: >>>> Rebased over 269b532ae and muted compiler warnings. >> >> Thank you Justin for the rebase! >> >> Hello T

Re: [HACKERS] make async slave to wait for lsn to be replayed

how to make sure the "read your writes consistency" internally, and the soluation here looks good to me. Glad to know that this patch will be committed very soon. [1] https://www.postgresql.org/message-id/CAPpHfdtuiL1x4APTs7u1fCmxkVp2-ZruXcdCfprDMdnOzvdC%2BA%40mail.gmail.com -- Best Regards Andy Fan

Re: using extended statistics to improve join estimates

h, and I think it can be committed individually if you are OK with that. Hope this kind of review is helpful. -- Best Regards Andy Fan >From daa6c27bc7dd0631607f0f254cc15491633a9ccc Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Mon, 13 Dec 2021 14:05:17 +0100 Subject: [PATCH v1 1/8] Es

Re: [HACKERS] make async slave to wait for lsn to be replayed

now how long time it waits unless they check it in application side, I think such information will be useful for monitor purpose sometimes. select pg_wal_replay_wait(lsn, 1000); may just take 1ms in fact, in this case, I want this function return 1. -- Best Regards Andy Fan

Re: [HACKERS] make async slave to wait for lsn to be replayed

+pg_wal_replay_wait ( + target_lsn pg_lsn, + timeout bigint DEFAULT 0) +void + Should we return the millseconds of waiting time? I think this information may be useful for customer if they want to know how long time it waits for for minitor purpose. -- Best Regards Andy Fan

Re: Extract numeric filed in JSONB more effectively

message-id/8734t6c5rh.fsf%40163.com -- Best Regards Andy Fan >From fb38be5addb93d7c0b8c1a3e8376751c9b3be5f5 Mon Sep 17 00:00:00 2001 From: "yizhi.fzh" Date: Mon, 1 Apr 2024 09:36:08 +0800 Subject: [PATCH v17 1/1] Improve the performance of Jsonb numeric/bool extraction. JSO

Re: a wrong index choose when statistics is out of date

costs off) select * from t where a = 109 and b = 8; explain (costs off, analyze) select * from t join t2 on t.c = t2.id where t.a = 109; I will add this to our commitfest application, any feedback is welcome! -- Best Regards Andy Fan >From 0d842e39275710a544b11033f5eec476147daf06 Mon Sep 1

Re: a wrong index choose when statistics is out of date

1e2-8629-734e3c8ba613%40postgrespro.ru -- Best Regards Andy Fan

Re: Extract numeric filed in JSONB more effectively

me more generic purpose which has to pay some extra effort, and even if we have some chance to improve JSON_VALUE, I don't think it shoud not block the patch here (I'd like to learn more about this, it may takes some time!) So I think the my patch here can be go ahead again, what do you think? [1] https://www.postgresql.org/message-id/CACJufxGtetrn34Hwnb9D2if5D_HOPAh235MtEZ1meVYx-BiNtg%40mail.gmail.com -- Best Regards Andy Fan

Re: remaining sql/json patches

jian he writes: > On Tue, Mar 5, 2024 at 12:38 PM Andy Fan wrote: >> >> >> In the commit message of 0001, we have: >> >> """ >> Both JSON_VALUE() and JSON_QUERY() functions have options for >> handling EMPTY and ERROR conditions, whi

Re: a wrong index choose when statistics is out of date

imizer statistics cause the rows in outer relation to be 1, which make the Nest loop is choosed. I'm not sure your idea could help on this or can help on this than mine at this aspect. -- Best Regards Andy Fan

Re: a wrong index choose when statistics is out of date

David Rowley writes: > On Thu, 7 Mar 2024 at 23:40, Andy Fan wrote: >> >> David Rowley writes: >> > If you don't want the planner to use the statistics for the column why >> > not just do the following? >> >> Acutally I didn't want the

Re: a wrong index choose when statistics is out of date

Andrei Lepikhov writes: > On 5/3/2024 19:56, Andy Fan wrote: >> I think it is OK for a design review, for the implementaion side, the >> known issue includes: >> 1. Support grap such infromation from its parent for partitioned table >> if the child doesn't have

Re: a wrong index choose when statistics is out of date

David Rowley writes: > On Wed, 6 Mar 2024 at 02:09, Andy Fan wrote: >> This patch introduces a new attoptions like this: >> >> ALTER TABLE t ALTER COLUMN col set (force_generic=true); >> >> Then selfunc.c realizes this and ignore the special

Re: a wrong index choose when statistics is out of date

Any feedback is welcome. -- Best Regards Andy Fan >From f8cca472479c50ba73479ec387882db43d203522 Mon Sep 17 00:00:00 2001 From: "yizhi.fzh" Date: Tue, 5 Mar 2024 18:27:48 +0800 Subject: [PATCH v0 1/1] Add a "force_generic" attoptions for selfunc.c Sometime user just ca

Re: a wrong index choose when statistics is out of date

David Rowley writes: > On Tue, 5 Mar 2024 at 00:37, Andy Fan wrote: >> >> David Rowley writes: >> > I don't think it would be right to fudge the costs in any way, but I >> > think the risk factor for IndexPaths could take into account the >> >

Re: remaining sql/json patches

he case, the method in [1] can avoid the user to modify these queries for the using the new function. [1] https://www.postgresql.org/message-id/8734t6c5rh@163.com -- Best Regards Andy Fan

Re: Shared detoast Datum proposal

imit is hit. This would make a noticable difference when we want to set a high limit and we have some high active sessions, like 100 * 40MB = 4GB. > On 3/4/24 18:08, Andy Fan wrote: >> ... >>> >>>> I assumed that releasing all of the memory at the end of executor once

Re: Shared detoast Datum proposal

In any case, my concern is more about having to do this when creating > the plan at all, the code complexity etc. Not just because it might have > performance impact. I think the main trade-off is TOAST cache method is pretty non-invasive but can't control the eviction well, the impacts includes: 1. may evicting the datum we want and kept the datum we don't need. 2. more likely to use up all the memory which is allowed. for example: if we set the limit to 1MB, then we kept more data which will be not used and then consuming all of the 1MB. My method is resolving this with some helps from other modules (kind of invasive) but can control the eviction well and use the memory as less as possible. -- Best Regards Andy Fan

Re: Extract numeric filed in JSONB more effectively

Peter Eisentraut writes: > On 09.02.24 10:05, Andy Fan wrote: >> 2. Where is the current feature blocked for the past few months? >> It's error message compatible issue! Continue with above setup: >> master: >> select * from tb where (a->'b')::numer

Re: Shared detoast Datum proposal

e context is > passed as an argument, unless absolutely necessary. Hmm, in this case, if we don't add the new argument, we have to get the detoast datum in Context-1 and copy it to the desired memory context, which is the thing I want to avoid. I think we have a same decision to make on the TOAST cache method as well. -- Best Regards Andy Fan

Re: Eager aggregation, take 3

irst and hope we can really progress on this topic. Good luck! -- Best Regards Andy Fan

Re: a wrong index choose when statistics is out of date

Andrei Lepikhov writes: > On 3/3/2024 14:01, Andy Fan wrote: >> 1. We can let the user define the column as the value is increased day by >> day. the syntax may be: >> ALTER TABLE x_events ALTER COLUMN created_at ALWAYS_INCREASED. >> then when a query lik

Re: a wrong index choose when statistics is out of date

David Rowley writes: > On Sun, 3 Mar 2024 at 20:08, Andy Fan wrote: >> The issue can be reproduced with the following steps: >> >> create table x_events (.., created_at timestamp, a int, b int); >> >> create index idx_1 on t(created_at, a); >>

Re: Shared detoast Datum proposal

Tomas Vondra writes: > On 3/3/24 07:10, Andy Fan wrote: >> >> Hi, >> >> Here is a updated version, the main changes are: >> >> 1. an shared_detoast_datum.org file which shows the latest desgin and >> pending items during discussion. >> 2.

Re: Shared detoast Datum proposal

Tomas Vondra writes: > On 2/26/24 16:29, Andy Fan wrote: >> >> ...> >> I can understand the benefits of the TOAST cache, but the following >> issues looks not good to me IIUC: >> >> 1). we can't put the result to tts_values[*] since without the

Re: Shared detoast Datum proposal

Tomas Vondra writes: > On 3/3/24 02:52, Andy Fan wrote: >> >> Hi Nikita, >> >>> >>> Have you considered another one - to alter pg_detoast_datum >>> (actually, it would be detoast_attr function) and save >>> detoasted datums

a wrong index choose when statistics is out of date

ing 9:00 to 18:00 and there are 2 rows per seconds for other time range. For now, I think option 1 may be easier to happen. -- Best Regards Andy Fan

Re: Shared detoast Datum proposal

g, I added the alternative design part for the idea of TOAST cache. -- Best Regards Andy Fan >From 66c64c197a5dab97a563be5a291127e4c5d6841d Mon Sep 17 00:00:00 2001 From: "yizhi.fzh" Date: Sun, 3 Mar 2024 13:48:25 +0800 Subject: [PATCH v9 1/1] shared detoast datum See the overall

Re: Shared detoast Datum proposal

gresql.org/message-id/875xyb1a6q.fsf%40163.com -- Best Regards Andy Fan

Re: Better error messages for %TYPE and %ROWTYPE in plpgsql

"David G. Johnston" writes: > On Mon, Feb 26, 2024 at 5:46 PM Andy Fan wrote: > > > Per recent discussion[1], plpgsql returns fairly unhelpful "syntax > > error" messages when a %TYPE or %ROWTYPE construct references a > > nonexistent object.

Re: Better error messages for %TYPE and %ROWTYPE in plpgsql

d by me at the first and resolve it by adding a odd parameter. Then the odd parameter blocked the whole process. [1] https://www.postgresql.org/message-id/87r0hmvuvr....@163.com -- Best Regards Andy Fan

Re: Shared detoast Datum proposal

ve some different issues than the current patch. Just I have many implemetion questions in my mind. > Andy, thank you! I'll check the last patch set out and reply in a day > or two. Thank you for your attention! -- Best Regards Andy Fan

Re: Shared detoast Datum proposal

> On 2/26/24 14:22, Andy Fan wrote: >> >>... >> >>> Also, toasted values >>> are not always being used immediately and as a whole, i.e. jsonb values are >>> fully >>> detoasted (we're working on this right now) to extract the s

  1   2   3   4   5   6   7   >