Thread: different datatypes in index scan join
At the bottom of an email from this list, I saw: > -------------------------(end of broadcast)------------------------- > TIP 9: the planner will ignore your desire to choose an index scan > if your joining column's datatypes do not match If that's so, then what is the recommended way to ensure an index is used if we want to join on different datatypes? Erik
On Monday 23 Jun 2003 7:08 pm, Erik Price wrote: > At the bottom of an email from this list, I saw: > > -------------------------(end of broadcast)------------------------- > > TIP 9: the planner will ignore your desire to choose an index scan > > if your joining column's datatypes do not match > > If that's so, then what is the recommended way to ensure an index is > used if we want to join on different datatypes? Cast your datatypes. For example, if you have an index on an int8 field foo you'd use something like: WHERE foo = 7::int8; This is because the '7' is going to be assumed an int4. You could use the more-standard cast() function rather than the '::' operator if you don't mind the typing. This is part of PG's tradeoff where a flexible type system means being quite cautious about assumptions. Readers who know an easy way of having their cake and eating it should contact the hackers list as soon as they've read the archive discussions on type issues. -- Richard Huxton
Is there a link to some of these discussions? Jon On Mon, 23 Jun 2003, Richard Huxton wrote: > On Monday 23 Jun 2003 7:08 pm, Erik Price wrote: > > At the bottom of an email from this list, I saw: > > > -------------------------(end of broadcast)------------------------- > > > TIP 9: the planner will ignore your desire to choose an index scan > > > if your joining column's datatypes do not match > > > > If that's so, then what is the recommended way to ensure an index is > > used if we want to join on different datatypes? > > Cast your datatypes. For example, if you have an index on an int8 field foo > you'd use something like: > > WHERE foo = 7::int8; > > This is because the '7' is going to be assumed an int4. You could use the > more-standard cast() function rather than the '::' operator if you don't mind > the typing. > > This is part of PG's tradeoff where a flexible type system means being quite > cautious about assumptions. Readers who know an easy way of having their cake > and eating it should contact the hackers list as soon as they've read the > archive discussions on type issues. > > -- > Richard Huxton > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
On Monday 23 Jun 2003 10:06 pm, Jonathan Bartlett wrote: > Is there a link to some of these discussions? Go to http://archives.postgresql.org/ and search for "coercion" or "implicit coercion" or "implicit cast" or similar on the hackers list. -- Richard Huxton