Re: Plan chosen for PQexecParams

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