Ask HN: How to increase PHP+Postgres speed? my testing result is slow

1 pointsby Elect27 lat temu2 comments

# PHP: ` $dbconn = pg_connect("host=127.0.0.1 dbname=test user=test password=test"); $start = microtime(true); for($i=1;$i<=5000;$i++) { pg_query("insert into largedb(uid,sid) values($i,$i)"); } echo ( (microtime(true) - $start) *1000).' ms'; exit; ` // result: 15504.348993301 ms

# Shell: ` begin for r in 1..5000 loop insert into largedb (uid,sid) values(r,r); end loop; end; $$; ` // result: 43 ms

PHP&PG are in same server. Shell's command is very fast, so seems there is much time spend in interact between PHP and PG. How can I increase this? I've also tested PHP+Mongodb, insert same amount of data only takes 200ms.

2 comments

gregjor7 lat temu

More of Stack Overflow question. Not clear on how the Shell script works... insert into not a Shell command. Maybe try prepared statement. Impossible to diagnose with seeing the table definition.

gregjor7 lat temu

I assume uid and sid have indexes on them. If they do, note that inserting sorted values into a b-tree (today structure for a database indeed) creates worst-case performance.