[EMAIL PROTECTED] 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.
thank you, now it works better; however it seems that the date index is ignored. ewsweb_test=> explain select zero_yield, gross from measures where product_id=29::smallint and date between '2003-03-12' and '2003-08-14'; NOTICE: QUERY PLAN: Index Scan using measures_product on measures (cost=0.00..3792.27 rows=254 width=12) EXPLAIN -- Marco Vezzoli tel. +39 039 603 6852 STMicroelectronics fax. +39 039 603 5055 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
