Thread: postgres 7.1.3: why does the query plan ignore indexes?
Hi, I know this question was asked many times, but please help me understand what I am doing wrong. I work with a postgresql 7.1.3 on Solaris 8 compiled with gcc 3.0.2 Some indexes are defined on a table ewsweb_test=> \d measures_product Index "measures_product"Attribute | Type ------------+----------product_id | smallint btree ewsweb_test=> \d measures_date Index "measures_date"Attribute | Type -----------+--------------------------date | timestamp with time zone btree but the query seems to ignore it. ewsweb_test=> explain select zero_yield, gross from measures where product_id=29 and date between '2003-03-12' and '2003-08-14'; NOTICE: QUERY PLAN: Seq Scan on measures (cost=0.00..12937.76 rows=254 width=12) EXPLAIN I know there is an option that alter the seq scan cost but I prefer to leave the optimizer as clean as possible. Thanks in advence for any help.Marco -- Marco Vezzoli tel. +39 039 603 6852 STMicroelectronics fax. +39 039 603 5055
Marco Vezzoli <marco.vezzoli@st.com> 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.
tgl@sss.pgh.pa.us wrote: > > Marco Vezzoli <marco.vezzoli@st.com> 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
Should we consider adding some warning when someone creates an index on an int2 column? --------------------------------------------------------------------------- Tom Lane wrote: > Marco Vezzoli <marco.vezzoli@st.com> 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 pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Should we consider adding some warning when someone creates an index on
> an int2 column?
I don't think so. Better to expend our energy on solving the
fundamental problem.
regards, tom lane
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> Bruce Momjian <pgman@candle.pha.pa.us> writes:
>>> Should we consider adding some warning when someone creates an index on
>>> an int2 column?
>>
>> I don't think so. Better to expend our energy on solving the
>> fundamental problem.
> I am thinking _until_ we fix the problem. Is it actually hard to add?
Why int2 in particular? And not int8? float4? Or numeric, float8,
or int4 in the wrong contexts? How about bpchar or varchar?
I figure we can waste at least as much time arguing about when/where/why
to issue warnings as it will take to solve the real problem.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Should we consider adding some warning when someone creates an index on > > an int2 column? > > I don't think so. Better to expend our energy on solving the > fundamental problem. I am thinking _until_ we fix the problem. Is it actually hard to add? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
"Tom Lane" <tgl@sss.pgh.pa.us> wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Should we consider adding some warning when someone creates an index on > > an int2 column? > > I don't think so. Better to expend our energy on solving the > fundamental problem. In the mean time that the fundamental problem is solved may be a warning is usefull. Regards Gaetano Mendola