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
