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

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

From: Jean-Michel Pouré <jm(at)poure(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: PostgreSQL does CAST implicitely between int and a domain derived from int
Date: 2009-08-26 14:40:31
Message-ID: 1251297631.9378.39.camel@acer (view raw or flat)
Thread:
Lists: pgsql-performance
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

Responses

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2009-08-26 14:59:45
Subject: Re: PostgreSQL does CAST implicitely between int and a domain derivedfrom int
Previous:From: Gerhard HeiftDate: 2009-08-26 14:26:13
Subject: Re: How to create a multi-column index with 2 dates using 'gist'?

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