Should we consider adding some warning when someone creates an index on an int2 column?
--------------------------------------------------------------------------- Tom Lane wrote: > Marco Vezzoli <[EMAIL PROTECTED]> writes: > > ewsweb_test=> \d measures_product > > Index "measures_product" > > Attribute | Type > > ------------+---------- > > product_id | smallint > ^^^^^^^^ > > > ewsweb_test=> explain select zero_yield, gross from measures where > > product_id=29 and date between '2003-03-12' and '2003-08-14'; > ^^^^^^^^^^^^^ > > "29" is taken as an integer (int4). To get an indexscan on an int2 > column, you need to explicitly cast it to int2: > product_id = 29::smallint > or you can put it in quotes and let the parser figure out the right > type: > product_id = '29' > > Yes, we'd like to make this better, but there are surprisingly many > pitfalls in tinkering with the assignment of datatypes to constants... > > regards, tom lane > > PS: you could also consider whether it's really saving you any space to > store product_id as a smallint instead of int. Because of alignment > considerations, it very possibly isn't. > > ---------------------------(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 > -- Bruce Momjian | http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
