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
