I've noticed that if a client uses PQexecParams, the query plan
appears to be identical to the plan chosen for PQprepare/PQexecPrepared,
which might not be as optimal as a plan chosen for PQexec. I can
understand the PQprepare case since the planner doesn't know what
parameters will actually be used, but with PQexecParams shouldn't
the planner have all the information it needs to choose a plan based
on specific parameters? Is PQexecParams just shorthand for a prepare
followed by an execute?

--
Michael Fuhr

Search Discussions

  • Tom Lane at Nov 20, 2005 at 10:21 pm

    Michael Fuhr writes:
    I've noticed that if a client uses PQexecParams, the query plan
    appears to be identical to the plan chosen for PQprepare/PQexecPrepared,
    which might not be as optimal as a plan chosen for PQexec. I can
    understand the PQprepare case since the planner doesn't know what
    parameters will actually be used, but with PQexecParams shouldn't
    the planner have all the information it needs to choose a plan based
    on specific parameters? Is PQexecParams just shorthand for a prepare
    followed by an execute?
    Yes, but it uses the unnamed statement, so in recent server versions you
    should get a postponed plan that uses the Bind parameter values. What
    test case are you looking at exactly?

    regards, tom lane
  • Michael Fuhr at Nov 21, 2005 at 12:59 am

    On Sun, Nov 20, 2005 at 05:21:03PM -0500, Tom Lane wrote:
    Michael Fuhr <mike@fuhr.org> writes:
    Is PQexecParams just shorthand for a prepare followed by an execute?
    Yes, but it uses the unnamed statement, so in recent server versions you
    should get a postponed plan that uses the Bind parameter values. What
    test case are you looking at exactly?
    I'm using 8.1.0 from CVS. I have a table that contains city names;
    I can send you the SQL to create and populate a test table if
    necessary. Here's a simplified client program (the original has
    error checking but I've stripped it out for brevity; this simplified
    version behaves the same way):

    #include "libpq-fe.h"

    #include <stdio.h>
    #include <stdlib.h>
    #include <string.h>

    int
    main(void)
    {
    PGconn *conn;
    PGresult *res;
    const char *query_fixed;
    const char *query_param;
    char const *values[1];

    query_fixed = "SELECT * FROM city WHERE name = 'Fairview'";
    query_param = "SELECT * FROM city WHERE name = $1";

    values[1] = "Fairview";

    conn = PQconnectdb("dbname=test");
    res = PQexec(conn, "SET debug_print_plan TO on");
    res = PQexec(conn, "SET client_min_messages TO debug1");

    fprintf(stderr, "# PQexec\n");
    PQexec(conn, query_fixed);

    fprintf(stderr, "# PQexecParams\n");
    PQexecParams(conn, query_param, 1, NULL, values, NULL, NULL, 0);

    fprintf(stderr, "# PQprepare\n");
    PQprepare(conn, "stmt", query_param, 1, NULL);

    PQfinish(conn);
    return EXIT_SUCCESS;
    }

    When I run this program I see the following; you can see that
    plan_rows and the plan itself differ:

    % ./exectest | & egrep 'PQ|DETAIL'
    # PQexec
    DETAIL: {BITMAPHEAPSCAN :startup_cost 2.12 :total_cost 54.87 :plan_rows 35 :plan_width
    # PQexecParams
    DETAIL: {INDEXSCAN :startup_cost 0.00 :total_cost 6.01 :plan_rows 2 :plan_width 16
    # PQprepare
    DETAIL: {INDEXSCAN :startup_cost 0.00 :total_cost 6.01 :plan_rows 2 :plan_width 16

    If I insert tens of thousands of matching rows, re-analyze, and
    disable enable_bitmapscan, I get the following:

    % ./exectest | & egrep 'PQ|DETAIL'
    # PQexec
    DETAIL: {SEQSCAN :startup_cost 0.00 :total_cost 1396.90 :plan_rows 40220 :plan_width
    # PQexecParams
    DETAIL: {INDEXSCAN :startup_cost 0.00 :total_cost 7.89 :plan_rows 3 :plan_width 16
    # PQprepare
    DETAIL: {INDEXSCAN :startup_cost 0.00 :total_cost 13.73 :plan_rows 6 :plan_width 16

    pg_stat_user_tables show one new seq_scan and one new idx_scan,
    which corresponds to the plans shown (the program doesn't call
    PQexecPrepared so the third statement never gets executed). Also,
    this particular example shows a difference between PQexecParams and
    PQprepare that I hadn't noticed before.

    Is my test flawed? Have I overlooked something?

    --
    Michael Fuhr
  • Tom Lane at Nov 21, 2005 at 1:34 am

    Michael Fuhr writes:
    Here's a simplified client program (the original has
    error checking but I've stripped it out for brevity; this simplified
    version behaves the same way):
    I get the same results for all three after fixing the subscripting
    mistake:

    - values[1] = "Fairview";
    + values[0] = "Fairview";

    Is your original program making the same mistake?

    regards, tom lane
  • Michael Fuhr at Nov 21, 2005 at 1:46 am

    On Sun, Nov 20, 2005 at 08:34:39PM -0500, Tom Lane wrote:
    Michael Fuhr <mike@fuhr.org> writes:
    Here's a simplified client program (the original has
    error checking but I've stripped it out for brevity; this simplified
    version behaves the same way):
    I get the same results for all three after fixing the subscripting
    mistake:

    - values[1] = "Fairview";
    + values[0] = "Fairview";

    Is your original program making the same mistake?
    [Smacks forehead.]

    Argh, that's what I get for thinking in one language while coding
    in another :-( Yeah, that's the problem; sorry for the noise.

    --
    Michael Fuhr

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedNov 20, '05 at 8:51p
activeNov 21, '05 at 1:46a
posts5
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Michael Fuhr: 3 posts Tom Lane: 2 posts

People

Translate

site design / logo © 2022 Grokbase