PostgreSQL does CAST implicitely between int and a domain derived from int during SELECT query

From: Jean-Michel Pouré <jm(at)poure(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: PostgreSQL does CAST implicitely between int and a domain derived from int during SELECT query
Date: 2009-08-26 13:52:38
Message-ID: 1251294758.9378.23.camel@acer
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrew Dunstan 2009-08-26 14:02:33 pretty print viewdefs
Previous Message Andrew Dunstan 2009-08-26 12:54:08 pretty print viewdefs