I'm doing a query from PHP to PostgreSQL and it's extremely slow. The query is the SELECT *, CAST(data->>'created_at' as timestamp) as fecha FROM gorda_lipo.tweets WHERE idusuario = 125379829 LIMIT 20
On PHPpgadmin it tooks only 21.172 ms, but if I try to do the query on PHP with pg_query(the connection, the query) the query is canceled because of timeout, and if I try to manually cancel it using SELECT pg_cancel_backend(pid); it takes more than one minute.
If I make another querySELECT *, CAST(data->>'created_at' as date) as fecha,CAST(data->>'id' as BIGINT) as idusuario FROM gorda_lipo.usuarios WHERE CAST(data->>'id' as BIGINT) = (SELECT *from (SELECT CAST(data->>'id' as BIGINT) FROM gorda_lipo.usuarios LIMIT 50) t ORDER BY random() LIMIT 1)
It works fine. I pretend to use the result for the idusuario of the first query, but it doens't work even if I use a number instead.
Why is the query so slow on PHP with pg_query ? What am I doing wrong? Is there another plugin to use?
Take a look at php.ini's max_execution_time value. There's no reason to your query be slower when querying from PHP, you probably are trying to display all data at once.