Re: [PERFORM] Speed with offset clause
> I just ran this query > > select p.* from tblPrintjobs p , (select oid from tblPrintjobs limit 25 > offset 622825) as subset where p.oid = subset.oid > I'm just curious here, from a social point of view. How often do you think someone will paginate over say 300K rows in steps of 25 ? The way I see things, pagination is only meant for humans. If someone really looks at 300K rows then it's really cheaper and makes more sense to download them/import into spreadsheet program instead of clicking next 12.000 times. If it's not intended for humans then there's better ways of doing this. Regards, -- Radu-Adrian Popescu CSA, DBA, Developer Aldrapay MD Aldratech Ltd. +40213212243 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Speed with offset clause
I just ran this query select p.* from tblPrintjobs p , (select oid from tblPrintjobs limit 25 offset 622825) as subset where p.oid = subset.oid And it seems to be a bit faster than without the subselect, probably because I'm only getting one column. The speed gain is not that high though On 24 Jun 2005, at 22:19, Yves Vindevogel wrote: Hmm, I can't do this, i'm afraid. Or it would be rather difficult My query is executed through a webpage (link to the page in a navigation bar) I do not know how many records there are (data is changing, and currently is 600k records) The only thing I could do, is doing this in a function where I first get the page, and then decide whether to use the normal sort order or the reversed order That would put my weak point right in the middle, which is not that bad, but I would like to find an easier way, if that is possible Huge memory would help ? On 24 Jun 2005, at 20:54, hubert depesz lubaczewski wrote: On 6/24/05, Yves Vindevogel <[EMAIL PROTECTED]> wrote: So, when I want the last page, which is: 600k / 25 = page 24000 - 1 = 23999, I issue the offset of 23999 * 25 improving this is hard, but not impossible. if you have right index created, try to reverse the order and fetch first adverts, and then resort it (just the 25 adverts) in correct order. it will be faster. depesz Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi.Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi.---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Speed with offset clause
Hi, Indeed, I would have to do it through a function, where I check the number of pages, It puts my weakest point in the middle then. I could simply rewrite my query like you state, just to check. I think all my queries are on one table only. (I report in a website on one table, that has been denormalized into other smaller tables for speed) But the problem is on the big table. I'm currently looking at another possibility, and that is generating XML files based upon my database. This would increase disk space enormously, but limit my problems with the database. Since I am using Cocoon for the website, this is not such a problematic decision, disks are cheap and I need only a few modifications to my code. On 24 Jun 2005, at 21:22, John A Meinel wrote: Yves Vindevogel wrote: Hi again all, My queries are now optimised. They all use the indexes like they should. However, there's still a slight problem when I issue the "offset" clause. We have a table that contains 600.000 records We display them by 25 in the webpage. So, when I want the last page, which is: 600k / 25 = page 24000 - 1 = 23999, I issue the offset of 23999 * 25 This take a long time to run, about 5-10 seconds whereas offset below 100 take less than a second. Can I speed this up ? Met vriendelijke groeten, Bien à vous, Kind regards, *Yves Vindevogel* *Implements* Postgres has the optimization that it will plan a query, and once it reaches the limit, it can stop even though there is more data available. The problem you are having is that it has to go through "offset" rows first, before it can apply the limit. If you can, (as mentioned in the other post), try to refine your index so that you can reverse it for the second half of the data. This is probably tricky, as you may not know how many rows you have (or the amount might be changing). A potentially better thing, is if you have an index you are using, you could use a subselect so that the only portion that needs to have 60k rows is a single column. Maybe an example: Instead of saying: SELECT * FROM table1, table2 WHERE table1.id = table2.id ORDER BY table1.date OFFSET x LIMIT 25; You could do: SELECT * FROM (SELECT id FROM table1 OFFSET x LIMIT 25) as subselect JOIN table1 ON subselect.id = table1.id , table2 WHERE table1.id = table2.id; That means that the culling process is done on only a few rows of one table, and the rest of the real merging work is done on only a few rows. It really depends on you query, though, as what rows you are sorting on has a big influence on how well this will work. John =:-> Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Speed with offset clause
Yves Vindevogel wrote: Hi again all, My queries are now optimised. They all use the indexes like they should. However, there's still a slight problem when I issue the "offset" clause. We have a table that contains 600.000 records We display them by 25 in the webpage. So, when I want the last page, which is: 600k / 25 = page 24000 - 1 = 23999, I issue the offset of 23999 * 25 This take a long time to run, about 5-10 seconds whereas offset below 100 take less than a second. Can I speed this up ? Met vriendelijke groeten, Bien à vous, Kind regards, *Yves Vindevogel* *Implements* Postgres has the optimization that it will plan a query, and once it reaches the limit, it can stop even though there is more data available. The problem you are having is that it has to go through "offset" rows first, before it can apply the limit. If you can, (as mentioned in the other post), try to refine your index so that you can reverse it for the second half of the data. This is probably tricky, as you may not know how many rows you have (or the amount might be changing). A potentially better thing, is if you have an index you are using, you could use a subselect so that the only portion that needs to have 60k rows is a single column. Maybe an example: Instead of saying: SELECT * FROM table1, table2 WHERE table1.id = table2.id ORDER BY table1.date OFFSET x LIMIT 25; You could do: SELECT * FROM (SELECT id FROM table1 OFFSET x LIMIT 25) as subselect JOIN table1 ON subselect.id = table1.id , table2 WHERE table1.id = table2.id; That means that the culling process is done on only a few rows of one table, and the rest of the real merging work is done on only a few rows. It really depends on you query, though, as what rows you are sorting on has a big influence on how well this will work. John =:-> signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Speed with offset clause
On 6/24/05, Yves Vindevogel <[EMAIL PROTECTED]> wrote: > So, when I want the last page, which is: 600k / 25 = page 24000 - 1 = > 23999, I issue the offset of 23999 * 25 improving this is hard, but not impossible. if you have right index created, try to reverse the order and fetch first adverts, and then resort it (just the 25 adverts) in correct order. it will be faster. depesz ---(end of broadcast)--- TIP 8: explain analyze is your friend
