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

On 11/05/2010, Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> wrote:
> On 11 May 2010 10:18, venu madhav <venutaurus539(at)gmail(dot)com> wrote:
>> 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.
>
> 1. Avoid using large OFFSETs. Do instead "... ORDER BY e.cid, e.cid LIMIT
> 21;"
> 2. What "EXPLAIN SELECT ..." shows?
> 3. What "\d event" prints?
>
> --
> Sergey Konoplev
>
> Blog: http://gray-hemp.blogspot.com /
> Linkedin: http://ru.linkedin.com/in/grayhemp /
> JID/GTalk: gray(dot)ru(at)gmail(dot)com / Skype: gray-hemp / ICQ: 29353802
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Have you checked if the indexes don't have lots of dead references?
Try to create new indexes, and then delete the old indexes (or just
use reindex if this is not an online database in production).

--
Brian Modra Land line: +27 23 5411 462
Mobile: +27 79 69 77 082
5 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.com/