#31393 [Opn->Asn]: pg_field_type inner query slows down scripts

ID: 31393 Updated by: derick@php.net Reported By: mauroi at digbang dot com -Status: Open +Status: Assigned Bug Type: PostgreSQL related PHP Version: Irrelevant -Assigned To: +Assigned To: yohgaki New Comment: Assigning to the maintainer. Previous Comments: ------------------------------------------------------------------------ [2005-01-03 22:18:54] mauroi at digbang dot com http://webmail.digbang.com/patch_type_oid.diff Thanks in advance ------------------------------------------------------------------------ [2005-01-03 22:14:35] derick@php.net Can you provide a patch of this in unified diff format? (External please, and provide a link) ------------------------------------------------------------------------ [2005-01-03 22:07:18] mauroi at digbang dot com I can send the patch with the new function (tested on PHP-5.0.3) in case you need it. Just tell me where. Thanks in advance. ------------------------------------------------------------------------ [2005-01-03 22:02:31] mauroi at digbang dot com Description: ------------ Using PostgreSQL we're casting every field we get from the database with the corresponding object (datetime, oid (lob), etc.). With that in mind, we're using the pg_field_type function that returns the needed information (data type). Lately we were having performance problems in the server, so, looking at php's source code we found that pg_field_type makes the following query: 'select oid,typname from pg_type' Since every new postgresql table generates a new type and the database has up to 500 tables, that query takes 500 ms to execute (just to know the type of a field, 80% of the page execution). So our workaround was to create a new function called pg_field_type_oid that only returns the oid of the field type (no internal query). So now the application has to know the corresponding datatype for the type oid. Our pages now execute in 90ms. Reproduce code: --------------- any call to pg_field_type ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=31393&edit=1