Every time I tested an idea to speed it up, I got exactly the same loading time on a Athlon 1800+, 256Mb RAM, 20Gb PATA computer as compared to a Dual Opteron 246, 1Gb RAM, 70Gb WD Raptor SATA server. Now, why a dual opteron machine can't perform any faster than a lowly 1800+ athlon in numerous tests is completely beyond me ... increased memory and RAID 0 disc configurations so far have not resulted in any significant performance gain in the opteron server.
How many rows does the query return ?
Maybe a lot of time is spent, hidden in the PHP libraries, converting the rows returned by psql into PHP objects.
You should try that :
EXPLAIN ANALYZE SELECT your query
-> time is T1 CREATE TABLE cache AS SELECT your query
EXPLAIN ANALYZE SELECT * FROM cache
-> time is T2 (probably very small)Now in your PHP script replace SELECT your query by SELECT * FROM cache. How much does the final page time changes ? This will tell you the time spend in the postgres engine, not in data transmission and PHPing. It will tell wat you can gain optimizing the query.
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
