is it possible to replace the window function with custom function

Question

I have a pagination sql with PostgreSQL 13 like this:

select
    *,
    COUNT(*) OVER ()
from
    (
    select
        "article"."id",
        "article"."user_id",
        "article"."title",
        "article"."author",
        "article"."guid",
        "article"."created_time",
        "article"."updated_time",
        "article"."link",
        "article"."pub_time",
        "article"."sub_source_id",
        "article"."cover_image",
        "article"."channel_reputation",
        "article"."editor_pick"
    from
        "article"
    where
        "article"."id" > $1) t
limit $2 offset $3

now the article table have 2000000 rows that makes the sql very slow. I want to optimize this sql, because I do not need to get the 100% corrent row number. So I define a custom function to get the evaluation rows of article table like this:

CREATE FUNCTION count_estimate(query text) RETURNS INTEGER AS
$func$
DECLARE
    rec   record;
    ROWS  INTEGER;
BEGIN
    FOR rec IN EXECUTE 'EXPLAIN ' || query LOOP
        ROWS := SUBSTRING(rec."QUERY PLAN" FROM ' rows=([[:digit:]]+)');
        EXIT WHEN ROWS IS NOT NULL;
    END LOOP;
RETURN ROWS;

END
$func$ LANGUAGE plpgsql;

and get the row number quickly like this:

select count_estimate('select * from article');

how to replace the window function with my custom function? is it possible to replace the window function with my custom function? what should I do? I have tried this way but it seems did not work:

select
    *,
    count_estimate('select * from article') OVER ()
from
    (
    select
        "article"."id",
        "article"."user_id",
        "article"."title",
        "article"."author",
        "article"."guid",
        "article"."created_time",
        "article"."updated_time",
        "article"."link",
        "article"."pub_time",
        "article"."sub_source_id",
        "article"."cover_image",
        "article"."channel_reputation",
        "article"."editor_pick"
    from
        "article"
    where
        "article"."id" > $1) t
limit $2 offset $3

this is the table DDL:

-- Drop table
-- DROP TABLE public.article;
CREATE TABLE public.article (
    id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
    user_id int8 NOT NULL,
    title varchar(512) NOT NULL,
    author varchar(256) NOT NULL,
    guid varchar(512) NOT NULL,
    created_time int8 NOT NULL,
    updated_time int8 NOT NULL,
    link varchar(512) NULL,
    pub_time timestamptz NULL,
    sub_source_id int8 NOT NULL,
    cover_image varchar(1024) NULL,
    channel_reputation int4 NOT NULL DEFAULT 0,
    editor_pick int4 NULL DEFAULT 0,
    CONSTRAINT article_id_seq_pk PRIMARY KEY (id),
    CONSTRAINT article_title_sub_source_id_key UNIQUE (title, sub_source_id)
);

score 0 · Answer 1 · answered Mar 22 '22 at 16:58

using this sql works:

select
    *,
    count_estimate('select * from article')
from
    (
    select
        "article"."id",
        "article"."user_id",
        "article"."title",
        "article"."author",
        "article"."guid",
        "article"."created_time",
        "article"."updated_time",
        "article"."link",
        "article"."pub_time",
        "article"."sub_source_id",
        "article"."cover_image",
        "article"."channel_reputation",
        "article"."editor_pick"
    from
        "article"
    where
        "article"."id" > $1) t
limit $2 offset $3

is it possible to replace the window function with custom function

1 Answers1