Lists: | pgsql-hackers |
---|
From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Plan chosen for PQexecParams |
Date: | 2005-11-20 20:51:11 |
Message-ID: | 20051120205111.GA55457@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Michael Fuhr <mike(at)fuhr(dot)org> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Plan chosen for PQexecParams |
Date: | 2005-11-20 22:21:03 |
Message-ID: | 23524.1132525263@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Michael Fuhr <mike(at)fuhr(dot)org> 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
From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Plan chosen for PQexecParams |
Date: | 2005-11-21 00:59:43 |
Message-ID: | 20051121005942.GA62818@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Sun, Nov 20, 2005 at 05:21:03PM -0500, Tom Lane wrote:
> Michael Fuhr <mike(at)fuhr(dot)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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Michael Fuhr <mike(at)fuhr(dot)org> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Plan chosen for PQexecParams |
Date: | 2005-11-21 01:34:39 |
Message-ID: | 27565.1132536879@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Michael Fuhr <mike(at)fuhr(dot)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?
regards, tom lane
From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Plan chosen for PQexecParams |
Date: | 2005-11-21 01:46:12 |
Message-ID: | 20051121014612.GA63342@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Sun, Nov 20, 2005 at 08:34:39PM -0500, Tom Lane wrote:
> Michael Fuhr <mike(at)fuhr(dot)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