Hi, everyone. I'm working on an application that needs to perform a
query twice -- once to retrieve the total number of rows in a table, and
a second that then retrieves chunks of rows. The current implementation
executes the query twice, once with COUNT(*) and another with OFFSET and
LIMIT being passed to a pl/pgsql function. Needless to say, the
performance isn't so hot.

I've managed to improve things quite a bit by using cursors, but I've
been stumped in trying to find a replacement for the COUNT(*). I wrote
a function that works great on 9.0:

CREATE OR REPLACE FUNCTION count_the_rows() RETURNS INTEGER AS $$
DECLARE
mycursor CURSOR FOR SELECT * FROM test_table;
number_of_rows integer := 0;
BEGIN
OPEN mycursor;
MOVE ALL IN mycursor;
GET DIAGNOSTICS number_of_rows := ROW_COUNT;
RETURN number_of_rows;
END;
$$ LANGUAGE 'plpgsql';

Unfortunately, the project is using 8.3, and the function refuses to
even compile, due to the "MOVE ALL". When we change it to something
else (such as MOVE 20000000, which returns the actual number of rows
skipped to psql), or ABSOLUTE -1, we get 0 back from ROW_COUNT.

I know (from the recent discussion on pgsql-performance, among other
places) that count(*) is inherently slow. I'm excited to have found a
solution that at least allows us to avoid the execution of a complex
query twice in a row. But is there any way for me to get, in 8.3, the
number of rows over which a cursor has skipped? Keep in mind that after
this count has executed, we're then going to rewind the cursor, chunking
through the result set with a separate function.

Thanks in advance,

Reuven

--
Reuven M. Lerner -- Web development, consulting, and training
Mobile: +972-54-496-8405 * US phone: 847-230-9795
Skype/AIM: reuvenlerner

Search Discussions

  • Peter Eisentraut at Oct 24, 2010 at 6:25 pm

    On sön, 2010-10-24 at 15:41 +0200, Reuven M. Lerner wrote:
    But is there any way for me to get, in 8.3, the
    number of rows over which a cursor has skipped? Keep in mind that
    after
    this count has executed, we're then going to rewind the cursor,
    chunking
    through the result set with a separate function.
    You could store the query result in a temporary table.
  • Reuven M. Lerner at Oct 24, 2010 at 8:25 pm

    Hi, Peter. You wrote:

    But is there any way for me to get, in 8.3, the
    number of rows over which a cursor has skipped? Keep in mind that
    after
    this count has executed, we're then going to rewind the cursor,
    chunking
    through the result set with a separate function.
    You could store the query result in a temporary table.
    Yeah, that's what we're doing now. But (a) I'm hoping that move will be
    faster, because we're not copying data, but just moving through it, and
    (b) I wanted to benchmark the different options. But I'm glad to know
    that this isn't a bad way to go, given the lack of information.

    That said, I'm sort of surprised that the psql client is able to show us
    the number of rows through which MOVE operated, while we're unable to
    access that number in pl/pgsql. But hey, that's yet another welcome
    addition to 9.0...

    Reuven

    --
    Reuven M. Lerner -- Web development, consulting, and training
    Mobile: +972-54-496-8405 * US phone: 847-230-9795
    Skype/AIM: reuvenlerner
  • Alban Hertroys at Oct 25, 2010 at 7:30 am

    On 24 Oct 2010, at 15:41, Reuven M. Lerner wrote:

    I've managed to improve things quite a bit by using cursors, but I've been stumped in trying to find a replacement for the COUNT(*). I wrote a function that works great on 9.0:

    CREATE OR REPLACE FUNCTION count_the_rows() RETURNS INTEGER AS $$
    DECLARE
    mycursor CURSOR FOR SELECT * FROM test_table;
    number_of_rows integer := 0;
    BEGIN
    OPEN mycursor;
    MOVE ALL IN mycursor;
    GET DIAGNOSTICS number_of_rows := ROW_COUNT;
    RETURN number_of_rows;
    END;
    $$ LANGUAGE 'plpgsql';

    Unfortunately, the project is using 8.3, and the function refuses to even compile, due to the "MOVE ALL". When we change it to something else (such as MOVE 20000000, which returns the actual number of rows skipped to psql), or ABSOLUTE -1, we get 0 back from ROW_COUNT.

    I recall movable cursors weren't supported in pl/pgsql until 8.4.

    You can use (movable) cursors in SQL though. That probably requires some client-side code, as things like GET DIAGNOSTICS aren't available in sql functions, but it may be of use to you. I've done this in a PHP web-application a couple of times.

    Alban Hertroys

    --
    Screwing up is an excellent way to attach something to the ceiling.


    !DSPAM:737,4cc531ef10289398819850!
  • Alban Hertroys at Oct 25, 2010 at 12:26 pm
    Please leave the ML in the reply-list, so that others might benefit from the answers, comment on the solution, etc etc. ;)
    On 25 Oct 2010, at 13:55, AI Rumman wrote:

    I am using Postgresql 8.1 and facing the similar problem.
    Can you provide the link where I get the information what you did with PHP?
    Not really, as I wrote it myself at a company that no longer exists; There is no link.

    You can however look at the documentation, specifically:
    http://www.postgresql.org/docs/8.1/interactive/sql-declare.html and
    http://www.postgresql.org/docs/8.1/interactive/sql-fetch.html

    You can retrieve the number of rows after MOVE FORWARD ALL (similar to the below posting) either from pg_num_rows() or by reading out the return value (not the result set!) of the query.
    I don't remember exactly how I did it, I used this method several years ago and have since dropped PHP as a programming language I support.

    On Mon, Oct 25, 2010 at 1:29 PM, Alban Hertroys wrote:
    On 24 Oct 2010, at 15:41, Reuven M. Lerner wrote:

    I've managed to improve things quite a bit by using cursors, but I've been stumped in trying to find a replacement for the COUNT(*). I wrote a function that works great on 9.0:

    CREATE OR REPLACE FUNCTION count_the_rows() RETURNS INTEGER AS $$
    DECLARE
    mycursor CURSOR FOR SELECT * FROM test_table;
    number_of_rows integer := 0;
    BEGIN
    OPEN mycursor;
    MOVE ALL IN mycursor;
    GET DIAGNOSTICS number_of_rows := ROW_COUNT;
    RETURN number_of_rows;
    END;
    $$ LANGUAGE 'plpgsql';

    Unfortunately, the project is using 8.3, and the function refuses to even compile, due to the "MOVE ALL". When we change it to something else (such as MOVE 20000000, which returns the actual number of rows skipped to psql), or ABSOLUTE -1, we get 0 back from ROW_COUNT.

    I recall movable cursors weren't supported in pl/pgsql until 8.4.

    You can use (movable) cursors in SQL though. That probably requires some client-side code, as things like GET DIAGNOSTICS aren't available in sql functions, but it may be of use to you. I've done this in a PHP web-application a couple of times.

    Alban Hertroys

    --
    Screwing up is an excellent way to attach something to the ceiling.






    --
    Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-general
    Alban Hertroys

    --
    Screwing up is an excellent way to attach something to the ceiling.


    !DSPAM:737,4cc5778810291816532262!

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedOct 24, '10 at 1:42p
activeOct 25, '10 at 12:26p
posts5
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase