Re: insert and query performance on big string table with pg_trgm
Don't know if it would make PostgreSQL happier but how about adding a hash
value column and creating the unique index on that one? May block some
false duplicates but the unique index would be way smaller, speeding up
inserts.
2017. nov. 25. 7:35 ezt írta ("Jeff Janes" ):
>
>
> 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
>
>
Re: Query became very slow after 9.6 -> 10 upgrade
> Excellent, please follow up if you learn anything new. Sure. But my testing is over and something new might come out only incidentally now. Testing hasn't reveal anything interesting. > That will probably be in > early February, per our release policy: ok, thanks. That makes me kinda hope for some security problem :) Is it completely safe to use manually patched version in production? Dmitry Shalashov, relap.io & surfingbird.ru 2017-11-24 19:39 GMT+03:00 Tom Lane : > 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: Query became very slow after 9.6 -> 10 upgrade
On Sat, Nov 25, 2017 at 8:54 PM, Dmitry Shalashov wrote: > Is it completely safe to use manually patched version in production? Patching upstream PostgreSQL to fix a critical bug is something that can of course be done. And to reach a state where you think something is safe to use in production first be sure to test it thoroughly on a stage instance. The author is also working on Postgres for 20 years, so this gives some insurance. -- Michael
Re: Query became very slow after 9.6 -> 10 upgrade
> The author is also working on Postgres for 20 years, > so this gives some insurance. I know. Tom is a legend. But still I'd like to hear from him to be sure :) Dmitry Shalashov, relap.io & surfingbird.ru 2017-11-25 15:13 GMT+03:00 Michael Paquier : > On Sat, Nov 25, 2017 at 8:54 PM, Dmitry Shalashov > wrote: > > Is it completely safe to use manually patched version in production? > > Patching upstream PostgreSQL to fix a critical bug is something that > can of course be done. And to reach a state where you think something > is safe to use in production first be sure to test it thoroughly on a > stage instance. The author is also working on Postgres for 20 years, > so this gives some insurance. > -- > Michael >
Re: Query became very slow after 9.6 -> 10 upgrade
Michael Paquier writes: > On Sat, Nov 25, 2017 at 8:54 PM, Dmitry Shalashov wrote: >> Is it completely safe to use manually patched version in production? > Patching upstream PostgreSQL to fix a critical bug is something that > can of course be done. And to reach a state where you think something > is safe to use in production first be sure to test it thoroughly on a > stage instance. The author is also working on Postgres for 20 years, > so this gives some insurance. It's not like there's some magic dust that we sprinkle on the code at release time ;-). If there's a problem with that patch, it's much more likely that you'd discover it through field testing than that we would notice it during development (we missed the original problem after all). So you can do that field testing now, or after 10.2 comes out. The former seems preferable, if you are comfortable with building a patched copy at all. I don't know what your normal source of Postgres executables is, but all the common packaging technologies make it pretty easy to rebuild a package from source with patch(es) added. Modifying your vendor's SRPM (or equivalent concept if you're not on Red Hat) is a good skill to have. regards, tom lane
Re: Query became very slow after 9.6 -> 10 upgrade
Ok, understood :-) Dmitry Shalashov, relap.io & surfingbird.ru 2017-11-25 18:42 GMT+03:00 Tom Lane : > Michael Paquier writes: > > On Sat, Nov 25, 2017 at 8:54 PM, Dmitry Shalashov > wrote: > >> Is it completely safe to use manually patched version in production? > > > Patching upstream PostgreSQL to fix a critical bug is something that > > can of course be done. And to reach a state where you think something > > is safe to use in production first be sure to test it thoroughly on a > > stage instance. The author is also working on Postgres for 20 years, > > so this gives some insurance. > > It's not like there's some magic dust that we sprinkle on the code at > release time ;-). If there's a problem with that patch, it's much more > likely that you'd discover it through field testing than that we would > notice it during development (we missed the original problem after all). > So you can do that field testing now, or after 10.2 comes out. The > former seems preferable, if you are comfortable with building a patched > copy at all. I don't know what your normal source of Postgres executables > is, but all the common packaging technologies make it pretty easy to > rebuild a package from source with patch(es) added. Modifying your > vendor's SRPM (or equivalent concept if you're not on Red Hat) is a > good skill to have. > > regards, tom lane >
