bug with current sources? Re: cost of parse/plan/execute for one sample query - Mailing list pgsql-hackers

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;