Re: [PERFORM] Slow query after 9.3 to 9.6 migration

2017-07-27 Thread Scott Marlowe
On Tue, Dec 27, 2016 at 3:50 PM, Flávio Henrique wrote: > Hi there, fellow experts! > > I need an advice with query that became slower after 9.3 to 9.6 migration. > > First of all, I'm from the dev team. > > Before migration, we (programmers) made some modifications on query

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

2017-07-26 Thread Peter Geoghegan
On Thu, Jan 5, 2017 at 9:51 AM, Daniel Blanch Bataller wrote: > If just recreating the index now it uses it, it might mean that the index > was bloated, that is, it grew so big that it was cheaper a seq scan. > > I’ve seen another case recently where postgres 9.6

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

2017-01-06 Thread Filipe Oliveira
Thank you for the reply. I had been trying to find that option for awhile now. On Fri, Jan 6, 2017 at 12:51 PM, Michael Paquier wrote: > On Fri, Jan 6, 2017 at 6:14 AM, Filipe Oliveira > wrote: > > Can you remove me from your mailing list? > >

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

2017-01-06 Thread Michael Paquier
On Fri, Jan 6, 2017 at 6:14 AM, Filipe Oliveira wrote: > Can you remove me from your mailing list? There is an unsubscribe action here: https://www.postgresql.org/community/lists/subscribe/ -- Michael -- Sent via pgsql-performance mailing list

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

2017-01-05 Thread Filipe Oliveira
Can you remove me from your mailing list? Thanks.

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

2017-01-05 Thread Merlin Moncure
On Thu, Jan 5, 2017 at 10:51 AM, Flávio Henrique wrote: > @Merlin Moncure >> >> Big gains (if any) are likely due to indexing strategy. >> I do see some suspicious casting, for example: >> Join Filter: ((four_charlie.delta_tango)::integer = >> (six_quebec.golf_bravo)::integer)

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

2017-01-05 Thread Daniel Blanch Bataller
Hi, If just recreating the index now it uses it, it might mean that the index was bloated, that is, it grew so big that it was cheaper a seq scan. I’ve seen another case recently where postgres 9.6 wasn’t using the right index in a query, I was able to reproduce the issue crafting index

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

2017-01-05 Thread Kevin Grittner
On Thu, Jan 5, 2017 at 10:51 AM, Flávio Henrique wrote: > Replying your comment, I think they tunned the server: > effective_cache_size = 196GB > shared_buffers = 24GB (this shouldn't be higher?) Probably not, although it may be a good idea to try settings either side of

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

2017-01-05 Thread Flávio Henrique
Hi all! Sorry the delay (holidays). Well, the most expensive sequencial scan was solved. I asked the db team to drop the index and recreate it and guess what: now postgresql is using it and the time dropped. (thank you, @Gerardo Herzig!) I think there's still room for improvement, but the

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

2017-01-05 Thread Merlin Moncure
On Tue, Dec 27, 2016 at 5:50 PM, Flávio Henrique wrote: > Hi there, fellow experts! > > I need an advice with query that became slower after 9.3 to 9.6 migration. > > First of all, I'm from the dev team. > > Before migration, we (programmers) made some modifications on query

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

2017-01-04 Thread Kevin Grittner
On Tue, Dec 27, 2016 at 5:50 PM, Flávio Henrique wrote: > I can see some buffers written that tells me > that something is wrong. Try running VACUUM FREEZE ANALYZE on all tables involved in the query (or just run it as a superuser on the whole database). Do *not* use the

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

2016-12-28 Thread Daniel Blanch Bataller
The biggest impact on performance you can achieve is by using a materialized view. if it’s so heavily used as you said, even 2-3 seconds in a multiuser OLTP environment still unacceptable under my point of view. I don’t know if this is the case but if you have 1000 users connecting at 8 am all

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

2016-12-27 Thread Gerardo Herzig
> > Hi there, fellow experts! > > > I need an advice with query that became slower after 9.3 to 9.6 > migration. > > > First of all, I'm from the dev team. > > > Before migration, we (programmers) made some modifications on query > bring it's average time from 8s to 2-3s. > > > As this

[PERFORM] Slow query after 9.3 to 9.6 migration

2016-12-27 Thread Flávio Henrique
Hi there, fellow experts! I need an advice with query that became slower after 9.3 to 9.6 migration. First of all, I'm from the dev team. Before migration, we (programmers) made some modifications on query bring it's average time from 8s to 2-3s. As this query is the most executed on our