Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group