On Wed, May 12, 2010 at 5:25 PM, Kevin Grittner <Kevin.Grittner@xxxxxxxxxxxx> wrote:
venu madhav wrote:
>>> AND e.timestamp >= '1270449180'
>>> AND e.timestamp < '1273473180'
>>> ORDER BY.
>>> e.cid DESC,
>>> e.cid DESC
>>> limit 21
>>> offset 10539780
> The second column acts as a secondary key for sorting if theAny chance you could just leave the second one off in that case?
> primary sorting key is a different column. For this query both of
> them are same.
[Venu] Yes, that can be ignored. But am not sure that removing it would reduce the time drastically.
Which, of course, is going to affect the number of rows. Which
> This query is part of an application which allows user to select
> time ranges and retrieve the data in that interval. Hence the time
> stamp.
leaves me wondering how you know that once you select and sequence
the result set you need to read past and ignore exactly 10539780
rows to get to the last page.
[Venu]For Ex: My database has 10539793 records. My application first calculates the count of number of records in that interval. And then based on user request to display 10/20/30/40 records in one page, it calculates how many records to be displayed when the last link is clicked.
Which will affect which rows are at any particular offset.
> To have it in some particular order we're doing order by.
[Venu]Yes, by default it has the primary key for order by.
How do you know that before you run your query?
> If the records are more in the interval,
[Venu] I calculate the count first.
Have you considered alternative techniques for paging? You might
> we display in sets of 20/30 etc. The user also has the option to
> browse through any of those records hence the limit and offset.
use values at the edges of the page to run a small query (limit, no
offset) when they page. You might generate all the pages on the
first pass and cache them for a while.
[Venu] If generate all the pages at once, to retrieve all the 10 M records at once, it would take much longer time and since the request from the browser, there is a chance of browser getting timed out.
> When the user asks for the last set of 20 records, this query getsThe DESC on the ORDER BY makes it look like you're trying to use the
> executed.
ORDER BY to get to the end, but then your offset tells PostgreSQL to
skip the 10.5 million result rows with the highest keys. Is the
"last page" the one with the highest or lowest values for cid?
[Venu] The last page contains the lowest values of cid. By default we get the records in the decreasing order of cid and then get the last 10/20.
Thank you,
Venu.
Thank you,
Venu.
-Kevin
