From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: venu madhav <venutaurus539@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Performance issues when the number of records are around 10 Million
Date: Tue, 11 May 2010 16:50:19 -0500
Message-ID: <4BE98ACB020000250003158C@gw.wicourts.gov> (raw)
In-Reply-To: <AANLkTin1f-9M0iNmIEtU7YlvEsVAt6TmHE1vDk3iUmQq@mail.gmail.com>
References: <AANLkTin1f-9M0iNmIEtU7YlvEsVAt6TmHE1vDk3iUmQq@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, venutaurus539@gmail.com
Subject: Re: Performance issues when the number of records are around 10 Million
In-Reply-To: <4BE98ACB020000250003158C@gw.wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox