How to optimize a fetch into a crosstab I have a schema like:

[ad_id] .  [name] . [valueofname]
 1 .        name .    "brian"
 1 .        age  .    "23"
 2 .        job  .    "IT"
 2 .        name .    "Jack" 


The row name contains multiple values: age, name, birthday, job, and I'd like to convert it into this:

[ad_id] .      [name]  .       [age] .              [birthday] .    [job]
[valueofad_id][valueofname] [valueofnameofage] [valueofnameofbirth] [valueofnameofjob]


Here is the query that I made; it's working but it's very slow because it is trying to make for each column its own table and JOIN it.

Do you have any suggestion or insights please? I am dealing with huge data volumes and trying to optimize fetching.

select users.ad_id,name.valueofname as name,
        age.valueofname as age,
        birthday.valueofname as birthday,
        job.valueofname as job
from users
left join users_details as name on (users.ad_id=name.ad_id) 
left join users_details as age on (users.ad_id=age.ad_id) 
left join users_details as job on (users.ad_id=job.ad_id) 
left join users_details as birthday on (users.ad_id=birthday.ad_id)
group by ad_id,name.valueofname,age.valueofname,birthday.valueofname;
SELECT *
FROM   crosstab(
       'SELECT ad_id, d.name, d.valueofname
        FROM   users
        LEFT   JOIN users_details d USING (ad_id)
        ORDER  BY 1'

    , $$VALUES ('name'), ('age'), ('birthday'), ('job')$$
   ) AS ct (ad_id int, name text, age text, birthday text, job text);


The LEFT JOIN to keep users without any data in the result. If that never occurs or is not desirable, remove the table users from the query completely - it has no other use here.

Detailed explanation for crosstab():


PostgreSQL Crosstab Query


The major advantage over alternative solutions is top performance, which seems to be your prime objective.