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) END AS 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_URLS XU 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;
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
