Re: Performance issues when the number of records are around 10 Million

First, are you sure you are getting autovacuum to run hourly? Autovacuum will only vacuum when certain configuration thresholds are reached. You can set it to only check for those thresholds every so often, but no vacuuming or analyzing will be done unless they are hit, regardless of how often autovacuum checks the tables. Whenever you are dealing with time series, the default thresholds are often insufficient, especially when you are especially interested in the last few records on a large table.

What are your autovacuum configuration parameters?
When were the two tables last autovacuum and analyzed, according to pg_stat_user_tables?
Could you post the output of explain analyze of your query?
Which default statistic collection parameters do you use? Have you changed them specifically for the tables you are using?
Which version of Postgres are you running? Which OS?

>>> venu madhav <venutaurus539(at)gmail(dot)com> 05/11/10 3:47 AM >>>
Hi all,
In my database application, I've a table whose records can reach 10M and insertions can happen at a faster rate like 100 insertions per second in the peak times. I configured postgres to do auto vacuum on hourly basis. I have frontend GUI application in CGI which displays the data from the database. When I try to get the last twenty records from the database, it takes around 10-15 mins to complete the operation.This is the query which is used:

select e.cid, timestamp, s.sig_class, s.sig_priority, s.sig_name, e.sniff_ip, e.sniff_channel, s.sig_config, e.wifi_addr_1,
e.wifi_addr_2, e.view_status, bssid FROM event e, signature s WHERE s.sig_id = e.signature AND e.timestamp >= '1270449180' AND e.timestamp < '1273473180' ORDER BY e.cid DESC, e.cid DESC limit 21 offset 10539780;

Can any one suggest me a better solution to improve the performance.

Please let me know if you've any further queries.

Thank you,
Venu