Re: Query became very slow after 9.6 -> 10 upgrade

[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

[Dmitry Shalashov]

> 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: insert and query performance on big string table with pg_trgm

[Gábor SZŰCS]

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
>
>