Re: 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>80000000')
> ->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