Thread: PostgreSQL does CAST implicitely between int and a domain derived from int during SELECT query
PostgreSQL does CAST implicitely between int and a domain derived from int during SELECT query
From
Jean-Michel Pouré
Date:
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
Re: PostgreSQL does CAST implicitely between int and a domain derived from int during SELECT query
From
Andrew Dunstan
Date:
Jean-Michel Pouré wrote: > 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. > > > Isn't that cast effectively a no-op? cheers andrew
Re: PostgreSQL does CAST implicitely between int and a domain derived from int during SELECT query
From
Tom Lane
Date:
Jean-Michel Pouré <jm@poure.com> writes: > 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. I see no reason whatever to think that that domain has anything to do with the choice of query plan. regards, tom lane
Re: PostgreSQL does CAST implicitely between int and a domain derive from int during SELECT query
From
"Kevin Grittner"
Date:
Jean-Michel Pouré<jm@poure.com> wrote: > Some queries range between 400ms and 700ms. Please post the results of EXPLAIN ANALYZE, not just EXPLAIN. Also, it helps if you can show the definition of all tables used in the query (including indexes) and any lines from your postgresql.conf file which are not at the default value. (And of course, any overrides to those settings which were in effect on the connection when the query was run.) Please repost with that the pgsql-performance list -- this is very unlikely to be something which will lead to a change to the PostgreSQL product, which is what this list is about; it's probably just something you can tune. (Discussion can always come back here if a problem with PostgreSQL itself is found.) -Kevin
Re: PostgreSQL does CAST implicitely between int and a domain derived from int during SELECT query
From
Jean-Michel Pouré
Date:
Le mercredi 26 août 2009 à 10:07 -0400, Andrew Dunstan a écrit : > > Isn't that cast effectively a no-op? What is no-op in English (I am French, pardon my English). I will update table definition in 5 minutes. Stay tuned. Kind regards, Jean-Michel
Re: PostgreSQL does CAST implicitely between int and a domain derived from int during SELECT query
From
"Kevin Grittner"
Date:
Jean-Michel Pouré<jm@poure.com> wrote: >Le mercredi 26 août 2009 à 10:07 -0400, Andrew Dunstan a écrit : >> >> Isn't that cast effectively a no-op? > > What is no-op It's short for "no operation" -- meaning it doesn't really result in anything being done. You are probably way off base in your assumptions about why these queries aren't running faster, but have not provided enough information to determine the actual reason. -Kevin
Re: PostgreSQL does CAST implicitely between int and a domain derived from int during SELECT query
From
Alvaro Herrera
Date:
Jean-Michel Pouré wrote: > Le mercredi 26 août 2009 à 10:07 -0400, Andrew Dunstan a écrit : > > > > Isn't that cast effectively a no-op? > > What is no-op in English (I am French, pardon my English). “no-op” means “an operation which does nothing”. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Re: PostgreSQL does CAST implicitely between int and a domain derived from int during SELECT query
From
Andrew Dunstan
Date:
Jean-Michel Pouré wrote: > Le mercredi 26 août 2009 à 10:07 -0400, Andrew Dunstan a écrit : > >> Isn't that cast effectively a no-op? >> > > What is no-op in English (I am French, pardon my English). > I will update table definition in 5 minutes. > see <http://en.wikipedia.org/wiki/No-op> cheers andrew
Re: PostgreSQL does CAST implicitely between int and a domain derive from int during SELECT query
From
Jean-Michel Pouré
Date:
Le mercredi 26 août 2009 à 09:16 -0500, Kevin Grittner a écrit : > Please repost with that the pgsql-performance list Done: http://drupal.org/node/559986 Kind regards, JMP