Re: much slower query in production

2020-02-26 Thread Jeff Janes
On Wed, Feb 26, 2020 at 1:02 PM Guillaume Cottenceau wrote: > > It is actually consistent with using a restored backup on the dev > computer, as my understanding is this comes out without any > garbage and like a perfectly vacuumed database. I think I got that backwards in my previous email. I

Re: much slower query in production

2020-02-26 Thread Jeff Janes
On Wed, Feb 26, 2020 at 11:17 AM Guillaume Cottenceau wrote: > Dear all, > > I am facing a much, much slower query in production than on my > development computer using a restored production backup, and I > don't understand why nor I see what I could do to speedup the > query on production :/ >

Re: much slower query in production

2020-02-26 Thread Guillaume Cottenceau
Michael Lewis writes: > UPDATE multicards > SET defacements = COALESCE( count, 0 ) > FROM ( SELECT multicard_uid, COUNT(*) AS count FROM tickets GROUP BY > multicard_uid ) AS sub > WHERE uid = multicard_uid OR multicard_uid is null; > > I expect this should work. Not sure of performance of c

Re: much slower query in production

2020-02-26 Thread michael...@sqlexec.com
Vacuum everything that you restored Sent from my iPhone > On Feb 26, 2020, at 1:19 PM, Michael Lewis wrote: > >  >> UPDATE multicards >>SET defacements = COALESCE( count, 0 ) >> FROM ( SELECT multicard_uid, COUNT(*) AS count FROM tickets GROUP BY >> multicard_uid ) AS sub >> WHERE uid

Re: much slower query in production

2020-02-26 Thread Michael Lewis
> > UPDATE multicards >SET defacements = COALESCE( count, 0 ) > FROM ( SELECT multicard_uid, COUNT(*) AS count FROM tickets GROUP BY > multicard_uid ) AS sub > WHERE uid = multicard_uid OR multicard_uid is null; > I expect this should work. Not sure of performance of course.

Re: much slower query in production

2020-02-26 Thread Guillaume Cottenceau
Michael Lewis writes: > By the way, I expect the time is cut in half while heap fetches stays similar > because the index is now in OS cache on the > second run and didn't need to be fetched from disk. Definitely need to check > on vacuuming as Justin says. If you have a fairly > active system,

Re: much slower query in production

2020-02-26 Thread Guillaume Cottenceau
Justin Pryzby writes: > On Wed, Feb 26, 2020 at 05:17:21PM +0100, Guillaume Cottenceau wrote: >> On production: >> >> # EXPLAIN ANALYZE SELECT transaction_uid, (SELECT COUNT(*) FROM tickets >> WHERE multicard_uid = multicards.uid) from multicards; >>

Re: much slower query in production

2020-02-26 Thread Michael Lewis
By the way, I expect the time is cut in half while heap fetches stays similar because the index is now in OS cache on the second run and didn't need to be fetched from disk. Definitely need to check on vacuuming as Justin says. If you have a fairly active system, you would need to run this query ma

Re: much slower query in production

2020-02-26 Thread Justin Pryzby
On Wed, Feb 26, 2020 at 05:17:21PM +0100, Guillaume Cottenceau wrote: > On production: > > # EXPLAIN ANALYZE SELECT transaction_uid, (SELECT COUNT(*) FROM tickets WHERE > multicard_uid = multicards.uid) from multicards; >

much slower query in production

2020-02-26 Thread Guillaume Cottenceau
Dear all, I am facing a much, much slower query in production than on my development computer using a restored production backup, and I don't understand why nor I see what I could do to speedup the query on production :/ Short data model explanation: one table stores tickets, one table stores mul