php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #31393 pg_field_type inner query slows down scripts
Submitted: 2005-01-03 22:02 UTC Modified: 2005-02-15 00:52 UTC
Votes:1
Avg. Score:5.0 ± 0.0
Reproduced:0 of 0 (0.0%)
From: mauroi at digbang dot com Assigned:
Status: Closed Package: PostgreSQL related
PHP Version: 5.1 OS:
Private report: No CVE-ID: None
View Developer Edit
 [2005-01-03 22:02 UTC] 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

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-01-03 22:07 UTC] 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:14 UTC] derick@php.net 
Can you provide a patch of this in unified diff format? (External please, and provide a link)
 [2005-01-03 22:18 UTC] mauroi at digbang dot com 
http://webmail.digbang.com/patch_type_oid.diff
Thanks in advance
 [2005-01-03 22:39 UTC] derick@php.net 
Assigning to the maintainer.
 [2005-01-12 07:17 UTC] yohgaki at ohgaki dot net 
Although, it seems 500 ms execution time for the query is too long. (Try vaccumedb -f , if you haven't done yet) The patch itself seems good. Question is getting OID of data type is good for anything else.
 [2005-01-12 13:59 UTC] mauroi at digbang dot com 
The database is very big (lots of schemas). I couldn't find a way to make that query faster. Of course, I make a regular vaccum full, but I don't get better results with this particular query. 
I don't think there's another use for this function (in fact, it can be used in the same situations where you use pg_field_type).
Thanks in advance....
 [2005-02-15 00:38 UTC] edink@php.net 
The function is added to the current development branch and it will be a part of PHP 5.1.0 release.

Thank you for the addition of this very useful function.
 
PHP Copyright © 2001-2025 The PHP Group
All rights reserved. 		Last updated: Sun Feb 23 07:01:29 2025 UTC