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 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
