Re: Query became very slow after 9.6 -> 10 upgrade
> The line number offsets are expected when applying to v10, but it looks > like you failed to transfer the attachment cleanly ... Yes, it was some mistake on our side. It looks that patch helps us. Tom, thank you! I'm still testing it though, just in case. What are PostgreSQL schedule on releasing fixes like this? Can I expect that it will be in 10.2 and when can I expect 10.2, approximately of course? Dmitry Shalashov, relap.io & surfingbird.ru 2017-11-23 20:00 GMT+03:00 Tom Lane : > Dmitry Shalashov writes: > > We tried to apply the patch on 10.1 source, but something is wrong it > seems: > > patch -p1 < ../1.patch > > (Stripping trailing CRs from patch; use --binary to disable.) > > patching file src/backend/optimizer/plan/analyzejoins.c > > (Stripping trailing CRs from patch; use --binary to disable.) > > patching file src/backend/utils/adt/selfuncs.c > > Hunk #1 succeeded at 3270 (offset -91 lines). > > Hunk #2 succeeded at 3304 (offset -91 lines). > > Hunk #3 succeeded at 3313 (offset -91 lines). > > Hunk #4 succeeded at 3393 (offset -91 lines). > > patch unexpectedly ends in middle of line > > Hunk #5 succeeded at 3570 with fuzz 1 (offset -91 lines). > > The line number offsets are expected when applying to v10, but it looks > like you failed to transfer the attachment cleanly ... there were > certainly not CRs in it when I mailed it. The output on v10 > should just look like > > patching file src/backend/optimizer/plan/analyzejoins.c > patching file src/backend/utils/adt/selfuncs.c > Hunk #1 succeeded at 3270 (offset -91 lines). > Hunk #2 succeeded at 3304 (offset -91 lines). > Hunk #3 succeeded at 3313 (offset -91 lines). > Hunk #4 succeeded at 3393 (offset -91 lines). > Hunk #5 succeeded at 3570 (offset -91 lines). > > regards, tom lane >
Re: Query became very slow after 9.6 -> 10 upgrade
Dmitry Shalashov writes: > It looks that patch helps us. Tom, thank you! > I'm still testing it though, just in case. Excellent, please follow up if you learn anything new. > What are PostgreSQL schedule on releasing fixes like this? Can I expect > that it will be in 10.2 and when can I expect 10.2, approximately of course? I haven't pushed it to the git repo yet, but I will shortly, and then it will be in the next minor release. That will probably be in early February, per our release policy: https://www.postgresql.org/developer/roadmap/ regards, tom lane
Re: insert and query performance on big string table with pg_trgm
On Nov 21, 2017 00:05, "Matthew Hall" wrote: > Are all indexes present at the time you insert? It will probably be much faster to insert without the gin index (at least) and build it after the load. There is some flexibility on the initial load, but the updates in the future will require the de-duplication capability. I'm willing to accept that might be somewhat slower on the load process, to get the accurate updates, provided we could try meeting the read-side goal I wrote about, or at least figure out why it's impossible, so I can understand what I need to fix to make it possible. As long as you don't let anyone use the table between the initial load and when the index build finishes, you don't have to compromise on correctness. But yeah, makes sense to worry about query speed first. > If you repeat the same query, is it then faster, or is it still slow? If you keep the expression exactly the same, it still takes a few seconds as could be expected for such a torture test query, but it's still WAY faster than the first such query. If you change it out to a different expression, it's longer again of course. There does seem to be a low-to-medium correlation between the number of rows found and the query completion time. To make this quick, you will need to get most of the table and most of the index cached into RAM. A good way to do that is with pg_prewarm. Of course that only works if you have enough RAM in the first place. What is the size of the table and the gin index? Cheers, Jeff
