{"id":1424,"date":"2018-01-19T19:22:49","date_gmt":"2018-01-19T19:22:49","guid":{"rendered":"http:\/\/goofy-trucks.flywheelsites.com\/optimizing-postgresql-page-3\/"},"modified":"2018-01-19T19:24:46","modified_gmt":"2018-01-19T19:24:46","slug":"optimizing-postgresql-page-3","status":"publish","type":"post","link":"https:\/\/phpbuilder.com\/optimizing-postgresql-page-3\/","title":{"rendered":"Optimizing Postgresql Page 3"},"content":{"rendered":"
\n
\n
By PHP Builder Staff<\/div>\n
on August 21, 2001<\/div>\n<\/p><\/div>\n
\n
\nTailing the log file clearly explained what the problem was.<\/div>\n
\nAll sorts of sexy debugging info will show up in this file, which
\nincludes SQL syntax errors, the output of EXPLAIN state, emts, connection
\nproblems, authentication attempts, and so forth.<\/div>\n
\nI restarted postgresql and brought our CGI online. Our jaws collectively
\ndropped to the floor as postgresql literally flew as soon as it started
\nto use the buffer. Server load by postgresql dropped to just under
\n10-percent.<\/div>\n
\nOne hitch I found with an early version of the system was that it had to
\nbuild up and tear down a postgresql connection with each request. This
\nwas intolerable, so I started to use the connection pooling features of
\nthe C library. Server load dropped another few notches with this option.
\nWith PHP you will want to use persistent connections (pg_pconnect
\ninstead of pg_connect) to fully take advantage of this effect.<\/div>\n

Indexes<\/h2>\n
\nI cannot emphasize enough the need to have proper indexing in
\npostgresql. One early mistake that I made was to index BIGINT columns.
\nThe columns were indexed ok, but postgresql refused to make use them.
\nAfter two days of tearing out my hair, it came to me that the
\narchitecture of the system was 32 bits. Could it be that postgresql
\nrefuses to make use of a 64 bit (BIGINT) index? Changing the type to
\nINTEGER quickly solved that problem. Maybe if I had one of those
\nnew-fangled 64 bit Itanium processors.<\/div>\n

Conclusion<\/h2>\n
\nThere are many things that you can do with your SQL statements to also
\nimprove query response, but these are adequately covered in the
\ninteractive postgresql documentation.<\/div>\n
\n


\nEricson Smith is a web developer at http:\/\/did-it.com.
\n<\/i><\/p><\/blockquote>\n<\/div>\n<\/div>\n

<\/p>\n

\n
\u00ab Previous Page<\/a><\/div>\n
1<\/a> <\/div>\n
| <\/div>\n
2<\/a> <\/div>\n
| <\/div>\n
3<\/div>\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"

I decided to take a shot at installing and using this database. For most of our work I use MySQL and will continue to do so, because of its ease of<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-1424","post","type-post","status-publish","format-standard","hentry","category-tutorials"],"_links":{"self":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1424","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/comments?post=1424"}],"version-history":[{"count":1,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1424\/revisions"}],"predecessor-version":[{"id":3283,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1424\/revisions\/3283"}],"wp:attachment":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/media?parent=1424"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/categories?post=1424"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/tags?post=1424"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}