Thread: PostgreSQL does CAST implicitely between int and a domain derived from int

PostgreSQL does CAST implicitely between int and a domain derived from int

From
Jean-Michel Pouré
Date:
Dear friends,

I contact on Postgresql hackers request.

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. Some queries range between 400ms and 700ms.

This provides some large sequential scans.
Could you help understand why a cast happens?

Details, query plan and database:
http://drupal.org/node/559986

Postgresql.conf has no special settings for optimizing queries other
than PostgreSQL 8.4 syntax. Only shared memory is much larger.

Kind regards,
Jean-Michel

Attachment

Re: PostgreSQL does CAST implicitely between int and a domain derivedfrom int

From
"Kevin Grittner"
Date:
Jean-Michel Pouré<jm@poure.com> wrote:
> Details, query plan and database:
> http://drupal.org/node/559986

That still has EXPLAIN output rather than EXPLAIN ANALYZE output.
Without the "actual" information, it's much harder to tell where
things might be improved.

-Kevin