Dear friends,
Thank you for your previous answers. I am running into a systemic
problem using Drupal under PostgreSQL 8.4
Drupal relies heavily on a domain derived from int:
CREATE DOMAIN int_unsigned AS integer CONSTRAINT int_unsigned_check CHECK ((VALUE >= 0));
Analysing slow queries, I noticed that PostgreSQL 8.4 would cast data
from int4 to int_unsigned.
Details and query plan: http://drupal.org/node/559986
Some queries range between 400ms and 700ms.
The problem is that Drupal send 300 SQL queries per page. Of course
these queries are cached in PHP. But not solving it makes it difficult
to run a Drupal site under heavy load.
What do you think? Is this because of the constraint or is this a
possible feature missing in PostgreSQL. What solution do you recommend
to solve this problem? Is there a way to use a custom type? Should we
convert the schema to int and add local constraints?
A lot of SQL code hanging around could be impacted and this seems like a
serious issue to me. So it seems difficult to convert all these
applications. For usability, it should be better to avoid cast between
int and int_unsigned.
Kind regards,
Jean-Michel
