Re: Performance issues when the number of records are around 10 Million - Mailing list pgsql-performance

venu madhav <venutaurus539@gmail.com> wrote:

> When I try to get the last twenty records from the database, it
> takes around 10-15  mins to complete the operation.

Making this a little easier to read (for me, at least) I get this:

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
;

Why the timestamp range, the order by, the limit, *and* the offset?
On the face of it, that seems a bit confused.  Not to mention that
your ORDER BY has the same column twice.

Perhaps that OFFSET is not needed?  It is telling PostgreSQL that
whatever results are generated based on the rest of the query, read
through and ignore the first ten and a half million.  Since you said
you had about ten million rows, you wanted the last 20, and the
ORDER by is DESCending, you're probably not going to get what you
want.

What, exactly, *is* it you want again?

-Kevin