Re: Seqscan slowness and stored procedures

Hello

2012/5/26 Ivan Voras <ivoras@freebsd.org>:
> Hello,
>
> I have a SQL function (which I've pasted below) and while testing its
> code directly (outside a function), this is the "normal", default plan:
>
> http://explain.depesz.com/s/vfP (67 ms)
>
> and this is the plain with enable_seqscan turned off:
>
> http://explain.depesz.com/s/EFP (27 ms)
>
> Disabling seqscan results in almost 2.5x faster execution.
>
> However, when this code is wrapped in a function, the execution time is
> closer to the second case (which is great, I'm not complaining):
>

see http://archives.postgresql.org/pgsql-general/2009-12/msg01189.php

Regards

Pavel

> edem=> explain analyze select * from document_content_top_voted(36);
>                                                            QUERY PLAN
>
-----------------------------------------------------------------------------------------------------------------------------------
>  Function Scan on document_content_top_voted  (cost=0.25..10.25
> rows=1000 width=188) (actual time=20.644..20.821 rows=167 loops=1)
>  Total runtime: 21.236 ms
> (2 rows)
>
> I assume that the difference between the function execution time and the
> direct plan with seqscan disabled is due to SQL parsing and planning.
>
> Since the plan is compiled-in for stored procedures, is the planner in
> that case already running under the assumption that seqscans must be
> disabled (or something to that effect)?
>
> Would tweaking enable_seqscan and other planner functions during the
> CREATE FUNCTION have an effect on the stored plan?
>
> Do the functions need to be re-created when the database is fully
> populated, to adjust their stored plans with regards to new selectivity
> situation on the indexes?
>
> ----
>
> The SQL function is:
>
> -- Retrieves document chunks of a specified document which have the most
> votes
>
> DROP FUNCTION IF EXISTS document_content_top_voted(INTEGER);
> CREATE OR REPLACE FUNCTION document_content_top_voted(document_id INTEGER)
>    RETURNS TABLE
>        (chunk_id INTEGER, seq INTEGER, content TEXT, ctime INTEGER, log
> TEXT,
>        nr_chunk_upvotes INTEGER, nr_chunk_downvotes INTEGER,
> nr_seq_changes INTEGER, nr_seq_comments INTEGER,
>        user_login VARCHAR, user_public_name VARCHAR, user_email VARCHAR)
> AS $$
>    WITH documents_top_chunks AS (
>        SELECT
>            (SELECT
>                chunk_id
>            FROM
>                documents_chunks_votes_total
>            WHERE
>                documents_id=$1 AND
> documents_chunks_votes_total.seq=documents_seqs.seq AND votes=
>                    (SELECT
>                        max(votes)
>                    FROM
>                        documents_chunks_votes_total
>                    WHERE
>                        documents_id=$1 AND
> documents_chunks_votes_total.seq=documents_seqs.seq)
>            ORDER BY
>                chunk_id DESC
>            LIMIT 1) AS chunk_id, seq AS doc_seq
>        FROM
>            documents_seqs
>        WHERE
>            documents_id = $1
>        ORDER BY seq
>    ) SELECT
>        chunk_id, doc_seq, content, documents_chunks.ctime,
> documents_chunks.log,
>        COALESCE((SELECT SUM(vote) FROM documents_chunks_votes WHERE
> documents_chunks_id=chunk_id AND vote=1)::integer, 0) AS nr_chunk_upvotes,
>        COALESCE((SELECT SUM(vote) FROM documents_chunks_votes WHERE
> documents_chunks_id=chunk_id AND vote=-1)::integer, 0) AS
> nr_chunk_downvotes,
>        (SELECT COUNT(*) FROM documents_chunks WHERE documents_id=$1 AND
> seq=doc_seq)::integer AS nr_seq_changes,
>        (SELECT COUNT(*) FROM documents_seq_comments WHERE
> documents_seq_comments.documents_id=$1 AND seq=doc_seq)::integer AS
> nr_seq_comments,
>        users.login, users.public_name, users.email
>    FROM
>        documents_chunks
>        JOIN documents_top_chunks ON documents_chunks.id =
> documents_top_chunks.chunk_id
>        JOIN users ON users.id=creator_uid
>    ORDER BY doc_seq
> $$ LANGUAGE SQL;
>
> (comments on improving the efficiency of the SQL code are also appreciated)
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance