Re: [PERFORM] More speed counting rows
On Mon, Jul 27, 2009 at 3:06 AM, Developerdev...@pas-world.com wrote: Hello, I am trying to optimize the count of files when I am using filters (select by some row/s parameter/s) In this case I think that postgresql really count all files. Resulting in unacceptable times of 4 seconds in http server response. Triggers+store in this case do not see very acceptable, because I need store 1.5 millions of counting possibilities. My question is: Any method for indirect count like ordered indexes + quadratic count? Any module? Any suggestion? Postgres cannot just use indexes, it has tot hit the tables. Rather than suspecting what pgsql is doing, use explain analyze select ... to see what your query is actually doing. If it is having to scan the table each time, then faster IO or a different table layout may be in order. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] More speed counting rows
Developer wrote: Hello, I am trying to optimize the count of files when I am using filters (select by some row/s parameter/s) In this case I think that postgresql really count all files. Resulting in unacceptable times of 4 seconds in http server response. Triggers+store in this case do not see very acceptable, because I need store 1.5 millions of counting possibilities. My question is: Any method for indirect count like ordered indexes + quadratic count? Any module? Any suggestion? I had a similar problem where HTTP requests triggered a count(*) over a table that was growing rapidly. The bigger the table got, the longer the count took. In my case, however, the counts only have to be a reasonable estimate of the current state, so I solved this problem with a count_sums table that gets updated every 30 minutes using a simple perl script in a cron job. The HTTP requests now trigger a very fast select from a tiny, 9 row, 2 column table. How up to date do the counts need to be? If the count takes 4 seconds, can you run it every minute and store the counts in a table for retrieval by the HTTP requests? Or does it absolutely have to be the exact count at the moment of the request? If it needs to be more real-time, you could expand on this by adding post insert/delete triggers that automatically update the counts table to keep it current. In my case it just wasn't necessary. - Chris -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] More speed counting rows
On Mon, Jul 27, 2009 at 5:06 AM, Developerdev...@pas-world.com wrote: Hello, I am trying to optimize the count of files when I am using filters (select by some row/s parameter/s) My question is: Any method for indirect count like ordered indexes + quadratic count? Any module? Any suggestion? If all you need is a good-enough estimate, you can try reporting the tuples count out of the stats tables. It'll only be as up-to-date as autovac makes it, though. I do this in one app to give me ballpark figures for some constantly-growing tables. -- - David T. Wilson david.t.wil...@gmail.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] More speed counting rows
How up to date do the counts need to be? If the count takes 4 seconds, can you run it every minute and store the counts in a table for retrieval by the HTTP requests? Now, I am storing integer value for filter in memory with timeout, but in busy server, system sure crash without system memory (700MB for all combinations, if combinations counted deleted memory count by timeout). Or does it absolutely have to be the exact count at the moment of the request? Some applications could fail, without exact number. -- -- -- Publicidad y Servicios http://www.pas-world.com Directorio http://www.precioventa.com Tienda http://informatica.precioventa.com/es/ Autoridad certificadora http://ca.precioventa.com/es/ -- -- -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
