is it possible to replace the window function with custom function



  • 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)
    );



  • 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
    

Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2