[GENERAL] Pagination count strategies
Hi, in one of our systems, we added a kind of pagination feature, that shows N records of Total records. To do this, we added a "count(*) over() as Total" field in our queries in replacement of doing two queries, one for fetching the records, and other for getting the count. This improved the performance, but we are't happy with the results yet, by removing the count, the query takes 200ms vs 2000ms with it. We are thinking of removing the count, but if we do that, the system will lack an interesting feature. What strategy for showing the total number of records returned do you recommend?. Regards, -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 Cel.: +54 9 (011) 40871877 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pagination count strategies
On Thu, Apr 03, 2014 at 10:34:32AM -0300, Leonardo M. Ramé wrote: > > What strategy for showing the total number of records returned do you > recommend?. The best answer for this I've ever seen is to limit the number of rows you're counting (at least at first) to some reasonably small number -- say 5000. This is usually reasonably fast for a well-indexed query, and your pagination can say something like "First n of at least 5000 results", unless you have fewer than 5000 results, in which case you know the number (and the count returned quickly anyway). As you're displaying those first 5000 results, you can work in the background getting a more accurate number. This is more work for your application, but it provides a much better user experience (and you can delay getting the detailed number until the user pages through to the second page of results, so you don't count everything needlessly in case the user just uses the first page, which IME happens a lot). Note that even Google doesn't give you an accurate number -- they just say "about ten trillion" or whatever. Hope that's useful, A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pagination count strategies
On 3 April 2014 15:34, Leonardo M. Ramé wrote: > Hi, in one of our systems, we added a kind of pagination feature, that > shows N records of Total records. > > To do this, we added a "count(*) over() as Total" field in our queries > in replacement of doing two queries, one for fetching the records, and > other for getting the count. This improved the performance, but we are't > happy with the results yet, by removing the count, the query takes > 200ms vs 2000ms with it. > > We are thinking of removing the count, but if we do that, the system > will lack an interesting feature. > > What strategy for showing the total number of records returned do you > recommend?. Assuming your results are unique (and what would be the point of showing duplicate results?) in a specific order, it should be possible to (uniquely) identify the last record shown on a previous page and display n results from there on. To add a result counter for displaying purposes to that, since you need to remember the last displayed result anyway, you might as well store the relative record number of that result with it and count from there on. This works well if you only have a next/previous results link, but not so well when people can pick arbitrary page numbers. It can work relative to the current page regardless of which page the user chose to navigate to next, but you'll have to go through all the results in between that page and the current page... That should still be faster than always counting from the start though (and you can be smart about from which end you start counting). -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pagination count strategies
Cache the total ? On Thu, Apr 3, 2014 at 3:34 PM, Leonardo M. Ramé wrote: > Hi, in one of our systems, we added a kind of pagination feature, that > shows N records of Total records. > > To do this, we added a "count(*) over() as Total" field in our queries > in replacement of doing two queries, one for fetching the records, and > other for getting the count. This improved the performance, but we are't > happy with the results yet, by removing the count, the query takes > 200ms vs 2000ms with it. > > We are thinking of removing the count, but if we do that, the system > will lack an interesting feature. > > What strategy for showing the total number of records returned do you > recommend?. > > Regards, > -- > Leonardo M. Ramé > Medical IT - Griensu S.A. > Av. Colón 636 - Piso 8 Of. A > X5000EPT -- Córdoba > Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 > Cel.: +54 9 (011) 40871877 > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] Pagination count strategies
On 03/04/14 15:34, Leonardo M. Ramé wrote: > Hi, in one of our systems, we added a kind of pagination feature, that > shows N records of Total records. > > To do this, we added a "count(*) over() as Total" field in our queries > in replacement of doing two queries, one for fetching the records, and > other for getting the count. This improved the performance, but we are't > happy with the results yet, by removing the count, the query takes > 200ms vs 2000ms with it. > > We are thinking of removing the count, but if we do that, the system > will lack an interesting feature. > > What strategy for showing the total number of records returned do you > recommend?. If you need only an estimated number and if your planner statistics are up to date, you can use the planner. Here is my implementation of the explain function. The COMMENT below shows how to use it: CREATE OR REPLACE FUNCTION explain(VARIADIC TEXT[]) RETURNS JSON AS $$ DECLARE tmp TEXT; BEGIN EXECUTE 'EXPLAIN (' || array_to_string(array_append($1[2:array_upper($1, 1)], 'FORMAT JSON'), ', ') || ') ' || $1[1] INTO tmp; RETURN tmp::JSON; END; $$ LANGUAGE plpgsql VOLATILE; COMMENT ON FUNCTION explain(VARIADIC TEXT[]) IS $def$ This function is a SQL interface to the planner. It returns the plan (result of EXPLAIN) of the query passed as TEXT string as the first parameter as JSON object. The remaining parameters are EXPLAIN-modifiers, like ANALYZE or BUFFERS. The function can be used to store plans in the database. Another interesting usage is when you need only an estimated row count for a query. You can use SELECT count(*) ... This gives you an exact number but is usually slow. If your planner statistics are up to date and the query is not too complicated, the planner usually gives a good estimate and is much faster. SELECT explain('SELECT 1 FROM tb WHERE id>8000') ->0->'Plan'->'Plan Rows'; $def$; Torsten -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pagination count strategies
On 2014-04-03 10:00:18 -0400, Andrew Sullivan wrote: > On Thu, Apr 03, 2014 at 10:34:32AM -0300, Leonardo M. Ramé wrote: > > > > What strategy for showing the total number of records returned do you > > recommend?. > > The best answer for this I've ever seen is to limit the number of rows > you're counting (at least at first) to some reasonably small number -- > say 5000. This is usually reasonably fast for a well-indexed query, > and your pagination can say something like "First n of at least 5000 > results", unless you have fewer than 5000 results, in which case you > know the number (and the count returned quickly anyway). As you're > displaying those first 5000 results, you can work in the background > getting a more accurate number. This is more work for your > application, but it provides a much better user experience (and you > can delay getting the detailed number until the user pages through to > the second page of results, so you don't count everything needlessly > in case the user just uses the first page, which IME happens a lot). > Note that even Google doesn't give you an accurate number -- they just > say "about ten trillion" or whatever. > > Hope that's useful, > > A > Sounds nice, is it possible to modify my "count(*) over()" to what you suggest?. -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 Cel.: +54 9 (011) 40871877 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pagination count strategies
On 2014-04-03 17:19:56 +0200, Torsten Förtsch wrote: > On 03/04/14 15:34, Leonardo M. Ramé wrote: > > Hi, in one of our systems, we added a kind of pagination feature, that > > shows N records of Total records. > > > > To do this, we added a "count(*) over() as Total" field in our queries > > in replacement of doing two queries, one for fetching the records, and > > other for getting the count. This improved the performance, but we are't > > happy with the results yet, by removing the count, the query takes > > 200ms vs 2000ms with it. > > > > We are thinking of removing the count, but if we do that, the system > > will lack an interesting feature. > > > > What strategy for showing the total number of records returned do you > > recommend?. > > If you need only an estimated number and if your planner statistics are > up to date, you can use the planner. > > Here is my implementation of the explain function. The COMMENT below > shows how to use it: > > CREATE OR REPLACE FUNCTION explain(VARIADIC TEXT[]) > RETURNS JSON AS $$ > DECLARE > tmp TEXT; > BEGIN > EXECUTE 'EXPLAIN (' > || array_to_string(array_append($1[2:array_upper($1, 1)], > 'FORMAT JSON'), ', ') > || ') ' > || $1[1] INTO tmp; > RETURN tmp::JSON; > END; > $$ LANGUAGE plpgsql VOLATILE; > > COMMENT ON FUNCTION explain(VARIADIC TEXT[]) > IS $def$ > This function is a SQL interface to the planner. It returns the plan > (result of EXPLAIN) of the query passed as TEXT string as the first > parameter as JSON object. > > The remaining parameters are EXPLAIN-modifiers, like ANALYZE or > BUFFERS. > > The function can be used to store plans in the database. > > Another interesting usage is when you need only an estimated row > count for a query. You can use > > SELECT count(*) ... > > This gives you an exact number but is usually slow. If your planner > statistics are up to date and the query is not too complicated, the > planner usually gives a good estimate and is much faster. > > SELECT explain('SELECT 1 FROM tb WHERE id>8000') >->0->'Plan'->'Plan Rows'; > $def$; > > > Torsten Nice!, do you know if this will work on 8.4?. -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 Cel.: +54 9 (011) 40871877 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pagination count strategies
On Thu, Apr 03, 2014 at 12:44:23PM -0300, Leonardo M. Ramé wrote: > Sounds nice, is it possible to modify my "count(*) over()" to what you > suggest?. I think the window_definition inside over() can contain a LIMIT, can't it? I didn't check just now, but I can't think any reason why not. (ISTR when I did this in the past we didn't have window functions, so I simulated it another way.) A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pagination count strategies
On 03/04/14 17:44, Leonardo M. Ramé wrote: > Nice!, do you know if this will work on 8.4?. > no way -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general