Get Column Types Using PDO (getColumnMeta is /slow/)

Question

Trying to write something to auto convert from some arbitrary DB result (i.e. not always all from table x), to an appropriate PHP typed result.

I extended the PDOStatement class,

class Statement extends PDOStatement {
    protected $pdo;
    protected $transformer;

    protected function __construct(PDO $pdo) {
        $this->pdo = $pdo;
        $this->transformer = $pdo->getTransformer();
    }

    public function fetchAll() {
        $results = parent::fetchAll(PDO::FETCH_ASSOC);

        if ($this->getTransformer()) $results = $this->completeResults($results);

        return $results;
    }

    private function completeResults(array $results = []) {
        if ($results == null || count($results) == 0) return null;
        if ($results[0] == false || !is_array($results[0])) return null;

        $index = 0;
        $typeMap = [];

        foreach ($results[0] as $column => $result) {
            $meta = $this->getColumnMeta($index); // this is very painful
            $typeMap[$column] = $meta['native_type'];
            $index++;
        }

        $transformer = $this->getTransformer();
        foreach ($results as $index => &$result) {
            array_walk($result, function(&$value, $key) use ($typeMap, $transformer) {
                $type = $typeMap[$key];
                $value = $transformer->transformToPhpValue($value, $type);
            });
        }

        return $results;
    }
}

Previously, before I was aware of PDO abstraction, I was using (in my specific case) the standard pg_...() methods. Using pg_field_type($resource, $column);, I could fetch the column type, and it was relatively speedy.

Now, using the new (for me) PDO method. If I comment out the part of my code where I do the transformation, and run 7 consecutive queries:

time to complete: 9.5367431640625E-7 seconds 
time to complete: 1.1920928955078E-6 seconds 
time to complete: 9.5367431640625E-7 seconds 
time to complete: 0 seconds 
time to complete: 9.5367431640625E-7 seconds 
time to complete: 0 seconds 
time to complete: 0 seconds

with it enabled:

time to complete: 0.5777850151062 seconds 
time to complete: 0.49124097824097 seconds 
time to complete: 0.28375911712646 seconds 
time to complete: 0.5946729183197 seconds 
time to complete: 0.42177200317383 seconds 
time to complete: 5.0067901611328E-6 seconds 
time to complete: 0.42121982574463 seconds 

That's /insane/.

I can tell it's fetching the column information one by one by one by looking at my Postgres logs:

LOG:  statement: SELECT TYPNAME FROM PG_TYPE WHERE OID=1114
LOG:  statement: SELECT TYPNAME FROM PG_TYPE WHERE OID=1114
LOG:  statement: SELECT TYPNAME FROM PG_TYPE WHERE OID=25
... like 30 more of these ...
LOG:  statement: SELECT TYPNAME FROM PG_TYPE WHERE OID=25
LOG:  statement: SELECT TYPNAME FROM PG_TYPE WHERE OID=23
LOG:  statement: SELECT TYPNAME FROM PG_TYPE WHERE OID=23
LOG:  statement: SELECT TYPNAME FROM PG_TYPE WHERE OID=23

The queries range in complexity from

SELECT 
    p.modified_at, ... ~ 30 fields ..., r.level AS id_level
FROM table_p AS p
LEFT JOIN table_a AS a ON (p.owner = a.id)
LEFT JOIN table_a0 AS a0 ON (p.reporter = a0.id)
LEFT JOIN table_r AS r ON (p.id = r.id)
WHERE (p.id = 1)

to just SELECT * FROM table_a AS a;

So, I guess the question is: is there a better way to do this? Is there a way that I can do this without impacting the speed of my code? 7 queries is on the low-end of consecutive queries run per request, so it's something that I'd like to deal with.

Answer 1

First of all, PDOStatement::getColumnMeta() is experimental, so be very careful using it (hope you'll setup autotests to check this with any php/pdo version update).

As for speed of retrieving metadata, I've run some tests and it turns out that SELECT TYPNAME FROM PG_TYPE WHERE OID=% queries are run blazing fast:

explain analyze SELECT TYPNAME FROM PG_TYPE WHERE OID=25;
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Index Scan using pg_type_oid_index on pg_type  (cost=0.27..8.29 rows=1 width=64) (actual time=0.051..0.055 rows=1 loops=1)
   Index Cond: (oid = 25::oid)
 Planning time: 0.165 ms
 Execution time: 0.100 ms
(4 rows)

explain analyze SELECT TYPNAME FROM PG_TYPE WHERE OID=1114;
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Index Scan using pg_type_oid_index on pg_type  (cost=0.27..8.29 rows=1 width=64) (actual time=0.083..0.085 rows=1 loops=1)
   Index Cond: (oid = 1114::oid)
 Planning time: 0.192 ms
 Execution time: 0.139 ms
(4 rows)

explain analyze SELECT TYPNAME FROM PG_TYPE WHERE OID=600;
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Index Scan using pg_type_oid_index on pg_type  (cost=0.27..8.29 rows=1 width=64) (actual time=0.063..0.064 rows=1 loops=1)
   Index Cond: (oid = 600::oid)
 Planning time: 0.261 ms
 Execution time: 0.125 ms
(4 rows)

This is about 0.0001 seconds for PG to select that data, even adding up 30 of these will not sum in 0.5 second or something like that.

I'd recommend you run explain analyze on your server for pg_type queries and see what are the timings there.

Bet you are not using persistent connection to a DB, and that adds up a whole bunch of time to your metadata calls.