Message-Id: <4BE98ACB020000250003158C@gw.wicourts.gov>
Date: Tue, 11 May 2010 16:50:19 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "venu madhav" <venutaurus539@gmail.com>,
 <pgsql-performance@postgresql.org>
Subject: Re: Performance issues when the number of records
	are around 10 Million
References: <AANLkTin1f-9M0iNmIEtU7YlvEsVAt6TmHE1vDk3iUmQq@mail.gmail.com>
In-Reply-To: <AANLkTin1f-9M0iNmIEtU7YlvEsVAt6TmHE1vDk3iUmQq@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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