Re: [GENERAL] Optimizing select count query which often takes over 10 seconds

2013-01-31 Thread Alexander Farber
On Wed, Jan 30, 2013 at 2:06 PM, Kevin Grittner wrote: > update pref_users set medals = 0 where medals <> 0; Thank you all for your insightful comments This has cured my cronjob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://

Re: [GENERAL] Optimizing select count query which often takes over 10 seconds

2013-01-30 Thread Jeff Janes
On Tue, Jan 29, 2013 at 11:41 PM, Alexander Farber wrote: > > When programming other languages, I never do > if (x != 0) { x = 0; } but just set x = 0 straight away. Most other languages are not transactional and durable. Databases are different. Cheers, Jeff -- Sent via pgsql-general maili

Re: [GENERAL] Optimizing select count query which often takes over 10 seconds

2013-01-30 Thread Kevin Grittner
Alexander Farber wrote: > The cronjob gives me now occasionally: > > /* reset and then update medals count */ > update pref_users set medals = 0; > psql:/home/afarber/bin/clean-database.sql:63: ERROR:  deadlock detected > DETAIL:  Process 31072 waits for ShareLock on transaction 124735679; blocke

Re: [GENERAL] Optimizing select count query which often takes over 10 seconds

2013-01-30 Thread Kevin Grittner
Alexander Farber wrote: > Kevin Grittner wrote: >> Alexander Farber wrote: >> >>> update pref_users set medals = 0; >>> UPDATE 223456 >> >> You're probably going to like your performance a lot better if >> you modify that to: >> >> update pref_users set medals = 0 where medals <> 0; > > is it re

Re: [GENERAL] Optimizing select count query which often takes over 10 seconds

2013-01-29 Thread Alexander Farber
Hello Kevin, On Wed, Jan 30, 2013 at 12:09 AM, Kevin Grittner wrote: > Alexander Farber wrote: > >> update pref_users set medals = 0; >> UPDATE 223456 > > You're probably going to like your performance a lot better if you > modify that to: > > update pref_users set medals = 0 where medals <> 0;

Re: [GENERAL] Optimizing select count query which often takes over 10 seconds

2013-01-29 Thread Kevin Grittner
Alexander Farber wrote: > update pref_users set medals = 0; > UPDATE 223456 You're probably going to like your performance a lot better if you modify that to: update pref_users set medals = 0 where medals <> 0; -Kevin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To

Re: [GENERAL] Optimizing select count query which often takes over 10 seconds

2013-01-27 Thread Jeff Janes
On Sun, Jan 27, 2013 at 9:25 AM, Alexander Farber wrote: > Hello - > > On Fri, Jan 25, 2013 at 7:42 PM, Jeff Janes wrote: >> This sounds like a good idea. But if the tournament is weekly why >> would the job have to be hourly? Why do the results of a weekly >> tournament need to be 'live'? > >

Re: [GENERAL] Optimizing select count query which often takes over 10 seconds

2013-01-27 Thread Alexander Farber
Hello - On Fri, Jan 25, 2013 at 7:42 PM, Jeff Janes wrote: > On Thu, Jan 24, 2013 at 1:57 AM, Alexander Farber > wrote: >> >> LOG: duration: 12590.394 ms statement: >> select count(id) from ( >> select id, >>row_number() over(partition by yw order by mon

Re: [GENERAL] Optimizing select count query which often takes over 10 seconds

2013-01-25 Thread Jeff Janes
On Thu, Jan 24, 2013 at 1:57 AM, Alexander Farber wrote: > Hello, > > for a PostgreSQL 8.4.13 database + pgbouncer Using 8.4 is really going to limit your options. ... > > LOG: duration: 12590.394 ms statement: > select count(id) from ( > select id, >ro

Re: [GENERAL] Optimizing select count query which often takes over 10 seconds

2013-01-25 Thread Guillaume Lelarge
On Fri, 2013-01-25 at 16:13 +0100, Alexander Farber wrote: > Hi - > > On Thu, Jan 24, 2013 at 8:12 PM, Guillaume Lelarge > wrote: > > You should better create an index on pref_money(yw, money). It could > > help you get rid of the seqscan and sort operations. > > I've created an index with > >

Re: [GENERAL] Optimizing select count query which often takes over 10 seconds

2013-01-24 Thread Guillaume Lelarge
On Thu, 2013-01-24 at 15:45 +0100, Alexander Farber wrote: > Hello - > > On Thu, Jan 24, 2013 at 3:39 PM, Alban Hertroys wrote: > > On 24 January 2013 10:57, Alexander Farber > > wrote: > >> > >> # explain analyze select count(id) from ( > >> select id, > >>row_nu

Re: [GENERAL] Optimizing select count query which often takes over 10 seconds

2013-01-24 Thread Alban Hertroys
> > It's sorting on disk. That's not going to be fast. Indeed, it's taking > > nearly all the time the query takes (4.4s for this step out of 4.5s for > the > > query). > > I've noticed that too, but what > does "sorting on disk" mean? > > I have a lot of RAM (32 GB) , > should I increase work_mem

Re: [GENERAL] Optimizing select count query which often takes over 10 seconds

2013-01-24 Thread Alexander Farber
Hello - On Thu, Jan 24, 2013 at 3:39 PM, Alban Hertroys wrote: > On 24 January 2013 10:57, Alexander Farber > wrote: >> >> # explain analyze select count(id) from ( >> select id, >>row_number() over(partition by yw order by money >> desc) as ranking >>

Re: [GENERAL] Optimizing select count query which often takes over 10 seconds

2013-01-24 Thread Alban Hertroys
On 24 January 2013 10:57, Alexander Farber wrote: > # explain analyze select count(id) from ( > select id, >row_number() over(partition by yw order by money > desc) as ranking > from pref_money > ) x > where x.ranking = 1 and id='OK452217

Re: [GENERAL] Optimizing select count query which often takes over 10 seconds

2013-01-24 Thread Moshe Jacobson
On Thu, Jan 24, 2013 at 4:57 AM, Alexander Farber < alexander.far...@gmail.com> wrote: > select count(id) from ( > select id, >row_number() over(partition by yw order by money > desc) as ranking > from pref_money > ) x > where x.r