Thread: cost of parse/plan/execute for one sample query

In benchmarks that I have done in the past comparing performance of
Oracle and Postgres in our web application, I found that I got ~140
requests/sec on Oracle and ~50 requests/sec on postgres.

The code path in my benchmark only issues one sql statement.  Since I
know that Oracle caches query plans, I wanted to see the cost under
postgres of the parse/plan/execute to see if the parsing and planing of
the sql statement would account for the difference in performance
between Oracle and postgres.

In a recent mail note to hackers, Tom mentioned the existence of the
show_parser_stats, show_planner_stats, and show_executor_stats
parameters in the postgresql.conf file.  So I turned them on ran my
query a few times and here are the results:

average of 10 runs:
parsing  = .003537 sec (19.3%)*
planning = .009793 sec (53.5%)
execute  = .004967 sec (27.2%)

If Oracle is only incurring the execute cost for each query then this
would explain the difference in performance between Oracle and Postgres.

This would lead me to conclude that the current proposed PREPARE/EXECUTE
patch will be very useful to me.  (now I just need to find the time to
test it).

thanks,
--Barry

* show_parser_stats prints out three separate timings: parser
statistics, parse analysis statistics, rewriter statistics, the number
.003537 is the sum of those three (.001086 + .002350 + .000101)


SELECT XF.FILE_TYPE_CODE, XF.FULL_PATH, XF.FILE_ID,   XF.PARENT_ID, XF.MIME_TYPE, XF.OWNER_PRINCIPAL_ID,
XF.REVISIONABLE_FLAG,  XF.OWNER_DELETE_FLAG, XF.OWNER_WRITE_FLAG, XF1_CREATION_DATE,   XF1_CREATED_BY,
XF1_LAST_UPDATE_DATE,XF1_LAST_UPDATED_BY,   XF.FILE_SIZE, CASE WHEN XF.QUOTA IS NULL THEN -1 ELSE XF.QUOTA END AS
QUOTA,  XF.LOGGING_FLAG, XF.HAS_LOCKS,   XF.HAS_DEAD_PROPERTIES, XF.LATEST_VERSION,   XF.QUOTA_LOCKED,
XF.TRASHCAN_PATH,XF.PRE_MOVE_NAME, XF.VIRTUAL_SERVER,   MAX(XEA.READ_FLAG) || MAX(XEA.WRITE_FLAG) ||
MAX(XEA.DELETE_FLAG)|| MAX(XEA.PERMISSION_FLAG) ||    CASE WHEN MAX(XCP.DIGEST) IS NULL THEN 'OO' ELSE MAX(XCP.DIGEST)
ENDAS PERMISSIONS,    XFV_FILE_VERSION_ID, XFV_CREATION_DATE, XFV_CREATED_BY,    XF.VERSION, XF.BLOB_ID, XF.BLOB_SIZE,
XF.DATA,   XF.STORAGE_STATE, XF.STORAGE_DATE, XF.STORAGE_LOCATION_ID, XF.STORAGE_FILENAME,   XBU.PERIOD_START,
XBU.BYTES_THIS_PERIOD,XBU.BYTES_TOTAL,   XF.DIGEST, XF.HIGHEST_VERSION,   XF.VERSIONING_FLAGS,   XF.CONTENT_LANGUAGE,
XF.OWNER_INHERIT_DELETE_FLAG,XF.OWNER_INHERIT_WRITE_FLAG, XF.VER_COMMENT,   XF.CHILD_INHERIT_ON_CREATE  FROM
(((XYF_URLSXU LEFT JOIN XYF_FILES XF1 (XF1_CREATION_DATE, XF1_CREATED_BY,   XF1_LAST_UPDATE_DATE, XF1_LAST_UPDATED_BY,
XF1_FILE_ID)ON (XU.FILE_ID = XF1_FILE_ID))  XF2 LEFT OUTER JOIN XYF_FILE_VERSIONS XFV     (XFV_FILE_ID,
XFV_FILE_VERSION_ID,XFV_CREATION_DATE, XFV_CREATED_BY)     ON (XF2.FILE_ID = XFV.XFV_FILE_ID   AND XFV.VERSION =  CASE
WHEN-1 = -1 THEN XF2.LATEST_VERSION ELSE -1 END)) AS XF3   LEFT OUTER JOIN XYF_BLOBS XB (XB_BLOB_ID) ON (XF3.BLOB_ID =
XB.XB_BLOB_ID))AS XF,   XYF_BANDWIDTH_USAGE XBU,   XYF_ENTRY_ACLS XEA,   XYF_CACHED_PRINCIPALS XCP  WHERE
XBU.ROOT_DIRECTORY= '/testuser2'   AND XF.VIRTUAL_SERVER = XBU.ROOT_DIRECTORY_VIRTUAL_SERVER    AND XEA.PRINCIPAL_ID =
XCP.ALT_PRINCIPAL_ID  AND (XCP.PRINCIPAL_ID = 1000 OR XCP.PRINCIPAL_ID = 1)   AND XF.FILE_ID = XEA.FILE_ID  AND
XF.VIRTUAL_SERVER= 1 AND (XF.FULL_PATH = '/testuser2/bugs.txt')  GROUP BY XF.FILE_ID, XF.FULL_PATH, XF.FILE_TYPE_CODE,
XF.PARENT_ID,XF.MIME_TYPE, XF.REVISIONABLE_FLAG,  XF.OWNER_DELETE_FLAG, XF.OWNER_WRITE_FLAG,
XF.OWNER_INHERIT_DELETE_FLAG, XF.OWNER_INHERIT_WRITE_FLAG, XF1_CREATION_DATE,  XF1_CREATED_BY, XF1_LAST_UPDATE_DATE,
XF1_LAST_UPDATED_BY, XF.FILE_SIZE, XF.QUOTA, XF.LOGGING_FLAG,  XF.HAS_LOCKS, XF.HAS_DEAD_PROPERTIES, XF.LATEST_VERSION,
XF.OWNER_PRINCIPAL_ID,  XF.QUOTA_LOCKED, XF.TRASHCAN_PATH,  XF.PRE_MOVE_NAME, XF.VIRTUAL_SERVER,  XFV_FILE_VERSION_ID,
XFV_CREATION_DATE,XFV_CREATED_BY,  XF.VERSION, XF.BLOB_ID, XF.BLOB_SIZE, XF.DATA,  XF.STORAGE_STATE, XF.STORAGE_DATE,
XF.STORAGE_LOCATION_ID,XF.STORAGE_FILENAME,  XBU.PERIOD_START, XBU.BYTES_THIS_PERIOD,  XBU.BYTES_TOTAL, XF.DIGEST,
XF.HIGHEST_VERSION, XF.VERSIONING_FLAGS,  XF.CONTENT_LANGUAGE, XF.VER_COMMENT, XF.CHILD_INHERIT_ON_CREATE; 

In testing Neil's PREPARE/EXECUTE patch on my test query, I found the
parser complains that this query is not valid when using current
sources.  The error I get is:

psql:testorig.sql:1: ERROR:  JOIN/ON clause refers to "xf2", which is
not part of JOIN

I think the sql is valid (at least it has worked in 7.1 and 7.2).  Is
this a bug?

thanks,
--Barry

PS.  I forgot to mention that the below performance numbers were done on
7.2 (not current sources).

Barry Lind wrote:
> In benchmarks that I have done in the past comparing performance of
> Oracle and Postgres in our web application, I found that I got ~140
> requests/sec on Oracle and ~50 requests/sec on postgres.
>
> The code path in my benchmark only issues one sql statement.  Since I
> know that Oracle caches query plans, I wanted to see the cost under
> postgres of the parse/plan/execute to see if the parsing and planing of
> the sql statement would account for the difference in performance
> between Oracle and postgres.
>
> In a recent mail note to hackers, Tom mentioned the existence of the
> show_parser_stats, show_planner_stats, and show_executor_stats
> parameters in the postgresql.conf file.  So I turned them on ran my
> query a few times and here are the results:
>
> average of 10 runs:
> parsing  = .003537 sec (19.3%)*
> planning = .009793 sec (53.5%)
> execute  = .004967 sec (27.2%)
>
> If Oracle is only incurring the execute cost for each query then this
> would explain the difference in performance between Oracle and Postgres.
>
> This would lead me to conclude that the current proposed PREPARE/EXECUTE
> patch will be very useful to me.  (now I just need to find the time to
> test it).
>
> thanks,
> --Barry
>
> * show_parser_stats prints out three separate timings: parser
> statistics, parse analysis statistics, rewriter statistics, the number
> .003537 is the sum of those three (.001086 + .002350 + .000101)
>
>

SELECT XF.FILE_TYPE_CODE, XF.FULL_PATH, XF.FILE_ID,   XF.PARENT_ID, XF.MIME_TYPE, XF.OWNER_PRINCIPAL_ID,
XF.REVISIONABLE_FLAG,  XF.OWNER_DELETE_FLAG, XF.OWNER_WRITE_FLAG, XF1_CREATION_DATE,   XF1_CREATED_BY,
XF1_LAST_UPDATE_DATE,XF1_LAST_UPDATED_BY,   XF.FILE_SIZE, CASE WHEN XF.QUOTA IS NULL THEN -1 ELSE XF.QUOTA END AS
QUOTA,  XF.LOGGING_FLAG, XF.HAS_LOCKS,   XF.HAS_DEAD_PROPERTIES, XF.LATEST_VERSION,   XF.QUOTA_LOCKED,
XF.TRASHCAN_PATH,XF.PRE_MOVE_NAME, XF.VIRTUAL_SERVER,   MAX(XEA.READ_FLAG) || MAX(XEA.WRITE_FLAG) ||
MAX(XEA.DELETE_FLAG)|| MAX(XEA.PERMISSION_FLAG) ||    CASE WHEN MAX(XCP.DIGEST) IS NULL THEN 'OO' ELSE MAX(XCP.DIGEST)
ENDAS PERMISSIONS,    XFV_FILE_VERSION_ID, XFV_CREATION_DATE, XFV_CREATED_BY,    XF.VERSION, XF.BLOB_ID, XF.BLOB_SIZE,
XF.DATA,   XF.STORAGE_STATE, XF.STORAGE_DATE, XF.STORAGE_LOCATION_ID, XF.STORAGE_FILENAME,   XBU.PERIOD_START,
XBU.BYTES_THIS_PERIOD,XBU.BYTES_TOTAL,   XF.DIGEST, XF.HIGHEST_VERSION,   XF.VERSIONING_FLAGS,   XF.CONTENT_LANGUAGE,
XF.OWNER_INHERIT_DELETE_FLAG,XF.OWNER_INHERIT_WRITE_FLAG, XF.VER_COMMENT,   XF.CHILD_INHERIT_ON_CREATE  FROM
(((XYF_URLSXU LEFT JOIN XYF_FILES XF1 (XF1_CREATION_DATE, XF1_CREATED_BY,   XF1_LAST_UPDATE_DATE, XF1_LAST_UPDATED_BY,
XF1_FILE_ID)ON (XU.FILE_ID = XF1_FILE_ID))  XF2 LEFT OUTER JOIN XYF_FILE_VERSIONS XFV     (XFV_FILE_ID,
XFV_FILE_VERSION_ID,XFV_CREATION_DATE, XFV_CREATED_BY)     ON (XF2.FILE_ID = XFV.XFV_FILE_ID   AND XFV.VERSION =  CASE
WHEN-1 = -1 THEN XF2.LATEST_VERSION ELSE -1 END)) AS XF3   LEFT OUTER JOIN XYF_BLOBS XB (XB_BLOB_ID) ON (XF3.BLOB_ID =
XB.XB_BLOB_ID))AS XF,   XYF_BANDWIDTH_USAGE XBU,   XYF_ENTRY_ACLS XEA,   XYF_CACHED_PRINCIPALS XCP  WHERE
XBU.ROOT_DIRECTORY= '/testuser2'   AND XF.VIRTUAL_SERVER = XBU.ROOT_DIRECTORY_VIRTUAL_SERVER    AND XEA.PRINCIPAL_ID =
XCP.ALT_PRINCIPAL_ID  AND (XCP.PRINCIPAL_ID = 1000 OR XCP.PRINCIPAL_ID = 1)   AND XF.FILE_ID = XEA.FILE_ID  AND
XF.VIRTUAL_SERVER= 1 AND (XF.FULL_PATH = '/testuser2/bugs.txt')  GROUP BY XF.FILE_ID, XF.FULL_PATH, XF.FILE_TYPE_CODE,
XF.PARENT_ID,XF.MIME_TYPE, XF.REVISIONABLE_FLAG,  XF.OWNER_DELETE_FLAG, XF.OWNER_WRITE_FLAG,
XF.OWNER_INHERIT_DELETE_FLAG, XF.OWNER_INHERIT_WRITE_FLAG, XF1_CREATION_DATE,  XF1_CREATED_BY, XF1_LAST_UPDATE_DATE,
XF1_LAST_UPDATED_BY, XF.FILE_SIZE, XF.QUOTA, XF.LOGGING_FLAG,  XF.HAS_LOCKS, XF.HAS_DEAD_PROPERTIES, XF.LATEST_VERSION,
XF.OWNER_PRINCIPAL_ID,  XF.QUOTA_LOCKED, XF.TRASHCAN_PATH,  XF.PRE_MOVE_NAME, XF.VIRTUAL_SERVER,  XFV_FILE_VERSION_ID,
XFV_CREATION_DATE,XFV_CREATED_BY,  XF.VERSION, XF.BLOB_ID, XF.BLOB_SIZE, XF.DATA,  XF.STORAGE_STATE, XF.STORAGE_DATE,
XF.STORAGE_LOCATION_ID,XF.STORAGE_FILENAME,  XBU.PERIOD_START, XBU.BYTES_THIS_PERIOD,  XBU.BYTES_TOTAL, XF.DIGEST,
XF.HIGHEST_VERSION, XF.VERSIONING_FLAGS,  XF.CONTENT_LANGUAGE, XF.VER_COMMENT, XF.CHILD_INHERIT_ON_CREATE; 

Barry Lind <barry@xythos.com> writes:
> In testing Neil's PREPARE/EXECUTE patch on my test query, I found the 
> parser complains that this query is not valid when using current 
> sources.  The error I get is:

> psql:testorig.sql:1: ERROR:  JOIN/ON clause refers to "xf2", which is 
> not part of JOIN

Hmm.  I have an open bug with sub-SELECTs inside a JOIN, but this
example doesn't look like it would trigger that.

> I think the sql is valid (at least it has worked in 7.1 and 7.2).  Is 
> this a bug?

Dunno.  Give me a test case (and no, I am *not* going to try to
reverse-engineer table schemas from that SELECT).
        regards, tom lane

Tom,

OK here is a test case:

create table test1 (t1a int);
create table test2 (t2a int);
create table test3 (t3a int);
SELECT x2.t2a
FROM ((test1 t1 LEFT JOIN test2 t2 ON (t1.t1a = t2.t2a)) AS x1
LEFT OUTER JOIN test3 t3 ON (x1.t2a = t3.t3a)) AS x2
WHERE x2.t2a = 1;

The select works under 7.2, but gives the following error in 7.3:

ERROR:  JOIN/ON clause refers to "x1", which is not part of JOIN

thanks,
--Barry



Tom Lane wrote:
> Barry Lind <barry@xythos.com> writes:
> 
>>In testing Neil's PREPARE/EXECUTE patch on my test query, I found the 
>>parser complains that this query is not valid when using current 
>>sources.  The error I get is:
> 
> 
>>psql:testorig.sql:1: ERROR:  JOIN/ON clause refers to "xf2", which is 
>>not part of JOIN
> 
> 
> Hmm.  I have an open bug with sub-SELECTs inside a JOIN, but this
> example doesn't look like it would trigger that.
> 
> 
>>I think the sql is valid (at least it has worked in 7.1 and 7.2).  Is 
>>this a bug?
> 
> 
> Dunno.  Give me a test case (and no, I am *not* going to try to
> reverse-engineer table schemas from that SELECT).
> 
>             regards, tom lane
> 

Barry Lind <barry@xythos.com> writes:
> OK here is a test case:

Looks like a bug, all right --- I must have introduced this when I redid
the handling of JOIN aliases a few weeks ago.   Will fix.

Thanks for the report.
        regards, tom lane

Barry Lind <barry@xythos.com> writes:
> The select works under 7.2, but gives the following error in 7.3:
> ERROR:  JOIN/ON clause refers to "x1", which is not part of JOIN

I've committed a fix for this.  Thanks again.
        regards, tom lane