Re: Found a possible bug in IDS 11.x... and a nasty one at that... has anyone suffered this?

Just out of curiousity, why are you using floats and not the DECIMAL data types? Not that it matters, but when you say 402 bytes wide is that your calculation or what the engine tells you the width is? The reason I ask is that its an 'odd' number to have as an actual width. I was expecting a different width to actually be used by the database? 512 bytes maybe? What happens if you change the floats and small floats to DECIMALSs? (Besides the table width growing?) I realize that this may seem irrelevant since you're not really using these rows in the query. Just don't like seeing these data types in databases. (float, small float, double, etc ...) I don't know if you would call this a bug per se, but what do you get when you look at the query path (set explain on?) Also just for kicks, what happens if you ran the query as a join against itself instead of a range? SELECT kic_kepler_id, ra, dec, kic_teff FROM kicplus WHERE kic_teff>5000 AND kic_teff<6500 (Just formated your query and capitalized KEY words) Becomes SELECT a.kic_kepler_id, ra, dec, kic_teff FROM kicplus a, kicplus b WHERE a.kic_kepler_id = b.kic_kepler_id AND a.kic_teff >5000 AND b.kic_teff < 6500 It would be interesting to see what you get when you have the index and when you don't. Also, going from memory and its never a good thing since database syntaxes aren't always the same for each database, but what happens when you use a RANGE qualifier like BETWEEN? (Assuming that my memory isn't playing tricks on me and Informix supports that key word?) SELECT kic_kepler_id, ra, dec, kic_teff FROM kicplus WHERE kic_teff BETWEEN 5000 AND 6500 You could also write the join as an inner query too... While these additional queries may not make intuitive sense, it would be interesting to see what the optimized does and the result timings. (I don't know what to expect, except to expect the unexpected.) But hey! What do I know? ;-) -G On Nov 25, 2:38 pm, fandelau <fande...@gmail.com> wrote: > Hello to all! > I think I've found a nasty bug in IDS 11.xx on Solaris (SunOS 5.10) > (yes I got the same results in both 11.10.FC3 and 11.50.FC3)... here > are the details: > > I got a table 402 bytes wide, 78 columns all but 1 are either integer > or float/decimal, with 13.1 million records in it (please find the > schema below). Table has 1 extent and it is not fragmented. I was > requested to index 13 of the columns, all single column indexes > indexes, which were created in a separate dbspace from the data. One > of the indexes is on a column that has 11 million nulls, the other 2.1 > million records have what I would call normal cardinality. A simple > query (on a freshly bounced instance) with a range filter on that > column returns in about 45 minutes on 11.10 and 22 minutes on 11.50, > having the query optimizer choose an index path on the index in > question. When the index was dropped and the same query ran on a seq > scan, it took 1.5 minutes on either version (freshly bounced)... > >         select kic_kepler_id, ra, dec, kic_teff from kicplus where> kic_teff>5000 and kic_teff<6500 >         being kic_teff the indexed column. > > I have ran the query as a count, as opposed to fetching any columns > and it comes back in a few seconds... > >         select count(*) from kicplus where kic_teff>5000 and> kic_teff<6500 > > So the index is being used correctly and finds the data requested. > > I know the cardinality of the column is skewed, but 44/22 minutes? > > I have already opened a call with IBM, and they were able to duplicate > this behavior, and they tried to blame it on engine config. While they > were doing their thing, I tried this in 2 other servers with different > configurations (same platform) and gotten the exact same results... > I'm willing to grant that engine config would slow down the query a > bit, perhaps from a few seconds to a couple of minutes, 5 minutes > max... but 44/22 minutes? I'm sorry but I refuse to believe it is > engine config. > > While all this was happening, the appl team ran the same test on a > vanilla installation of Oracle, no fancy stuff, and it comes back in a > few seconds. When I told this to the IBM engineer, he stopped on his > tracks and replied that he was going to involve more resources into > investigating this issue. > > I hope they are really looking into this. Meanwhile, we have decided > to drop the index on that column and opted for the seq scan, but > nonetheless this behavior is not acceptable. > > Has anyone suffered this issue? Any hints? let me know if I can > provide anymore details. > > Happy Thanksgiving Day to y'all! > > Ramon > > create table "informix".kicplus >   ( >     kic_kepler_id integer, >     kic_tmid integer, >     kic_tm_designation char(50), >     kic_fov_flag integer, >     kct_ktc_flag integer, >     kic_ra float, >     ra float, >     dec float, >     kic_glon float, >     kic_glat float, >     kic_parallax smallfloat, >     kic_pmtotal smallfloat, >     kic_pmra smallfloat, >     kic_pmdec smallfloat, >     kic_umag smallfloat, >     kic_gmag smallfloat, >     kic_rmag smallfloat, >     kic_imag smallfloat, >     kic_zmag smallfloat, >     kic_kepmag smallfloat, >     kic_gredmag smallfloat, >     kic_d51mag smallfloat, >     kic_jmag smallfloat, >     kic_hmag smallfloat, >     kic_kmag smallfloat, >     kic_grcolor smallfloat, >     kic_gkcolor smallfloat, >     kic_jkcolor smallfloat, >     kic_teff integer, >     kic_logg smallfloat, >     kic_feh smallfloat, >     kic_radius smallfloat, >     kic_ebminusv smallfloat, >     kic_av smallfloat, >     kic_galaxy integer, >     kic_blend integer, >     kic_variable integer, >     kic_scpid integer, >     kic_altid integer, >     kic_altsource integer, >     kic_cq char(10), >     kic_pq integer, >     kic_aq integer, >     kic_catkey integer, >     kic_scpkey integer, >     kct_sky_group_id integer, >     kct_crowding smallfloat, >     kct_num_sonccd integer, >     kct_contamination smallfloat, >     kct_edge_dist0 integer, >     kct_edge_dist1 integer, >     kct_edge_dist2 integer, >     kct_edge_dist3 integer, >     kct_channel_s0 integer, >     kct_module_s0 integer, >     kct_output_s0 integer, >     kct_row_s0 integer, >     kct_column_s0 integer, >     kct_channel_s1 integer, >     kct_module_s1 integer, >     kct_output_s1 integer, >     kct_row_s1 integer, >     kct_column_s1 integer, >     kct_channel_s2 integer, >     kct_module_s2 integer, >     kct_output_s2 integer, >     kct_row_s2 integer, >     kct_column_s2 integer, >     kct_channel_s3 integer, >     kct_module_s3 integer, >     kct_output_s3 integer, >     kct_row_s3 integer, >     kct_column_s3 integer, >     x decimal(17,16), >     y decimal(17,16), >     z decimal(17,16), >     spt_ind integer, >     cntr serial not null >   )  extent size 2500000 next size 250000 lock

Sorry to top post. Not very good at posting @ 3:00 in the morning. :-) I meant to say optimizer so that the last part makes sense. > From: im_gumby@hotmail.com > Subject: Re: Found a possible bug in IDS 11.x... and a nasty one at that... has anyone suffered this? > Date: Tue, 1 Dec 2009 01:45:23 -0800 > To: informix-list@iiug.org > > Just out of curiousity, why are you using floats and not the DECIMAL > data types? > > Not that it matters, but when you say 402 bytes wide is that your > calculation or what the engine tells you the width is? The reason I > ask is that its an 'odd' number to have as an actual width. I was > expecting a different width to actually be used by the database? 512 > bytes maybe? > > What happens if you change the floats and small floats to DECIMALSs? > (Besides the table width growing?) > > I realize that this may seem irrelevant since you're not really using > these rows in the query. Just don't like seeing these data types in > databases. (float, small float, double, etc ...) > > I don't know if you would call this a bug per se, but what do you get > when you look at the query path (set explain on?) > > > Also just for kicks, what happens if you ran the query as a join > against itself instead of a range? > > SELECT kic_kepler_id, ra, dec, kic_teff > FROM kicplus > WHERE kic_teff>5000 > AND kic_teff<6500 > (Just formated your query and capitalized KEY words)> > Becomes > > SELECT a.kic_kepler_id, ra, dec, kic_teff > FROM kicplus a, kicplus b > WHERE a.kic_kepler_id = b.kic_kepler_id > AND a.kic_teff >5000 > AND b.kic_teff < 6500> > It would be interesting to see what you get when you have the index > and when you don't. > > Also, going from memory and its never a good thing since database > syntaxes aren't always the same for each database, but what happens > when you use a RANGE qualifier like BETWEEN? (Assuming that my memory > isn't playing tricks on me and Informix supports that key word?) > > SELECT kic_kepler_id, ra, dec, kic_teff > FROM kicplus > WHERE kic_teff BETWEEN 5000 AND 6500> > You could also write the join as an inner query too... > > While these additional queries may not make intuitive sense, it would > be interesting to see what the optimized does and the result timings. > (I don't know what to expect, except to expect the unexpected.) But > hey! What do I know? ;-) > > -G > > > On Nov 25, 2:38 pm, fandelau <fande...@gmail.com> wrote: > > Hello to all! > > I think I've found a nasty bug in IDS 11.xx on Solaris (SunOS 5.10) > > (yes I got the same results in both 11.10.FC3 and 11.50.FC3)... here > > are the details: > > > > I got a table 402 bytes wide, 78 columns all but 1 are either integer > > or float/decimal, with 13.1 million records in it (please find the > > schema below). Table has 1 extent and it is not fragmented. I was > > requested to index 13 of the columns, all single column indexes > > indexes, which were created in a separate dbspace from the data. One > > of the indexes is on a column that has 11 million nulls, the other 2.1 > > million records have what I would call normal cardinality. A simple > > query (on a freshly bounced instance) with a range filter on that > > column returns in about 45 minutes on 11.10 and 22 minutes on 11.50, > > having the query optimizer choose an index path on the index in > > question. When the index was dropped and the same query ran on a seq > > scan, it took 1.5 minutes on either version (freshly bounced)... > > > > select kic_kepler_id, ra, dec, kic_teff from kicplus where> > kic_teff>5000 and kic_teff<6500 > > being kic_teff the indexed column. > > > > I have ran the query as a count, as opposed to fetching any columns > > and it comes back in a few seconds... > > > > select count(*) from kicplus where kic_teff>5000 and> > kic_teff<6500 > > > > So the index is being used correctly and finds the data requested. > > > > I know the cardinality of the column is skewed, but 44/22 minutes? > > > > I have already opened a call with IBM, and they were able to duplicate > > this behavior, and they tried to blame it on engine config. While they > > were doing their thing, I tried this in 2 other servers with different > > configurations (same platform) and gotten the exact same results... > > I'm willing to grant that engine config would slow down the query a > > bit, perhaps from a few seconds to a couple of minutes, 5 minutes > > max... but 44/22 minutes? I'm sorry but I refuse to believe it is > > engine config. > > > > While all this was happening, the appl team ran the same test on a > > vanilla installation of Oracle, no fancy stuff, and it comes back in a > > few seconds. When I told this to the IBM engineer, he stopped on his > > tracks and replied that he was going to involve more resources into > > investigating this issue. > > > > I hope they are really looking into this. Meanwhile, we have decided > > to drop the index on that column and opted for the seq scan, but > > nonetheless this behavior is not acceptable. > > > > Has anyone suffered this issue? Any hints? let me know if I can > > provide anymore details. > > > > Happy Thanksgiving Day to y'all! > > > > Ramon > > > > create table "informix".kicplus > > ( > > kic_kepler_id integer, > > kic_tmid integer, > > kic_tm_designation char(50), > > kic_fov_flag integer, > > kct_ktc_flag integer, > > kic_ra float, > > ra float, > > dec float, > > kic_glon float, > > kic_glat float, > > kic_parallax smallfloat, > > kic_pmtotal smallfloat, > > kic_pmra smallfloat, > > kic_pmdec smallfloat, > > kic_umag smallfloat, > > kic_gmag smallfloat, > > kic_rmag smallfloat, > > kic_imag smallfloat, > > kic_zmag smallfloat, > > kic_kepmag smallfloat, > > kic_gredmag smallfloat, > > kic_d51mag smallfloat, > > kic_jmag smallfloat, > > kic_hmag smallfloat, > > kic_kmag smallfloat, > > kic_grcolor smallfloat, > > kic_gkcolor smallfloat, > > kic_jkcolor smallfloat, > > kic_teff integer, > > kic_logg smallfloat, > > kic_feh smallfloat, > > kic_radius smallfloat, > > kic_ebminusv smallfloat, > > kic_av smallfloat, > > kic_galaxy integer, > > kic_blend integer, > > kic_variable integer, > > kic_scpid integer, > > kic_altid integer, > > kic_altsource integer, > > kic_cq char(10), > > kic_pq integer, > > kic_aq integer, > > kic_catkey integer, > > kic_scpkey integer, > > kct_sky_group_id integer, > > kct_crowding smallfloat, > > kct_num_sonccd integer, > > kct_contamination smallfloat, > > kct_edge_dist0 integer, > > kct_edge_dist1 integer, > > kct_edge_dist2 integer, > > kct_edge_dist3 integer, > > kct_channel_s0 integer, > > kct_module_s0 integer, > > kct_output_s0 integer, > > kct_row_s0 integer,@@N

> Sorry to top post. > Not very good at posting @ 3:00 in the morning. :-) No worries Ian... We all have been there... ^_^ > > Just out of curiousity, why are you using floats and not the DECIMAL > > data types? Actually the DDLs comes from another team, who defines the tables... > > I don't know if you would call this a bug per se, but what do you get > > when you look at the query path (set explain on?) Here... QUERY: (OPTIMIZATION TIMESTAMP: 12-03-2009 16:53:43) ------ select kic_kepler_id, ra, dec, kic_teff from kicplus_tmp where kic_teff > 5000 and kic_teff < 6500 into temp foo with no log Estimated Cost: 677384 Estimated # of Rows Returned: 1544345 1) informix.kicplus_tmp: INDEX PATH (1) Index Keys: kic_teff (Serial, fragments: ALL) Lower Index Filter: informix.kicplus_tmp.kic_teff > 5000 Upper Index Filter: informix.kicplus_tmp.kic_teff < 6500 > > Also just for kicks, what happens if you ran the query as a join > > against itself instead of a range? <SNIP> Same time issue (it is still running...). Here's the sqexplain... QUERY: (OPTIMIZATION TIMESTAMP: 12-03-2009 16:51:06) ------ select a.kic_kepler_id, b.ra, a.dec, b.kic_teff from kicplus_tmp a, kicplus_tmp b where a.kic_kepler_id = b.kic_kepler_id and a.kic_teff > 5000 and b.kic_teff < 6500 into temp foo with no log Estimated Cost: 2597967 Estimated # of Rows Returned: 250913 1) informix.a: INDEX PATH (1) Index Keys: kic_teff (Serial, fragments: ALL) Lower Index Filter: informix.a.kic_teff > 5000 2) informix.b: INDEX PATH Filters: informix.b.kic_teff < 6500 (1) Index Keys: kic_kepler_id (Serial, fragments: ALL) Lower Index Filter: informix.a.kic_kepler_id = informix.b.kic_kepler_id NESTED LOOP JOIN > > It would be interesting to see what you get when you have the index > > and when you don't. with the index you get the above... without you get a sequencial scan... > > Also, going from memory and its never a good thing since database > > syntaxes aren't always the same for each database, but what happens > > when you use a RANGE qualifier like BETWEEN? (Assuming that my memory > > isn't playing tricks on me and Informix supports that key word?) Tried it... same result > > You could also write the join as an inner query too... With itself? > > While these additional queries may not make intuitive sense, it would > > be interesting to see what the optimized does and the result timings. > > (I don't know what to expect, except to expect the unexpected.) But > > hey! What do I know? ;-) and guess what the unexpected (the index actually slowing the query down) happened to me! LOL! At this point I'm leaving this to IBM's tech support... I'll post their findings, when I get them. Thanks so much for your suggestions! Ramón

@ Fernando Nunes > I understand your surprise, and given the timmings I think it's worth > looking into it. But by no means this is the first time a sequential > scan works faster than an index scan. Of course ideally the optimizer > should always know that before choosing... I can understand a seq scan taking a minute vs a bad index path taking 5 minutes, but 45 minutes? even 22? > So essentially all of the queries using the index performed equally as bad. > > That would imply that the optimizer was working properly. > > So yeah, lets see what IBM comes up with.... > > -G Actually G, the optimizer is working properly, because when I execute the queries as a count they work fine (I mean they come back in a couple of secs), which means that the optimizer is choosing the right index path and finding the right keys in the index. the problem must be in the way the data is being accessed. So, indeed, let's wait for IBM to respond... I can hear the Jeopardy Final Clue music playing in the background... Cheers! Ramon

> From: fandelau@gmail.com > Actually G, the optimizer is working properly, because when I execute > the queries as a count they work fine (I mean they come back in a > couple of secs), which means that the optimizer is choosing the right > index path and finding the right keys in the index. the problem must > be in the way the data is being accessed. > > So, indeed, let's wait for IBM to respond... I can hear the Jeopardy > Final Clue music playing in the background... > > Cheers! > > Ramon Ah, but then that's the rub. If the optimizer is working correctly and you're getting consistent performance from the different queries than are you sure you actually have a bug? Or is it a design defect? The queries I asked about in addition to the one you ran originally all dealt with ways of giving you equivalent data via different paths. If there was an issue with the optimizer then you'd see a big difference in timings between the queries. So then, assuming your tests are accurate and reproducible on different systems, then we'd have to see what could be causing it. I mean if its not the queries, then what about the data types themselves? Lets face it, floats and doubles are not the most database friendly and even though you're not using them in your query, they could be an issue. So the first question ... Can you reproduce the problem on the same 'version' of IDS but on a different hardware platform? (Switch from Solaris on Sun, to Linux on Intel , or AIX) If you ran the same test set on a different release of IDS, do you still get the problem? If you created a different database, unloaded the tables, created the new tables and then reloaded the data then created the index, what happens? What happens if you reduce the table width by removing the fields you're not using? Does that effect the query? What happens if you change the floats and doubles to DECIMAL? I guess the point is to see what you can change in your schema that would still result in the same problem, or causes the problem to go away. No offense to IBM, but even at level 3 support, what's the priority? -G _________________________________________________________________ Windows Live Hotmail is faster and more secure than ever. http://www.microsoft.com/windows/windowslive/hotmail_bl1/hotmail_bl1.aspx?ocid=PID23879::T:WLMTAGL:ON:WL:en-ww:WM_IMHM_1:092009

Ian Michael Gumby wrote: > > > > From: fandelau@gmail.com > > > > Actually G, the optimizer is working properly, because when I execute > > the queries as a count they work fine (I mean they come back in a > > couple of secs), which means that the optimizer is choosing the right > > index path and finding the right keys in the index. the problem must > > be in the way the data is being accessed. > > > > So, indeed, let's wait for IBM to respond... I can hear the Jeopardy > > Final Clue music playing in the background... > > > > Cheers! > > > > Ramon I'm not sure, but this looks like a part of a private email. But this phrase: "> > Actually G, the optimizer is working properly, because when I execute > > the queries as a count they work fine (I mean they come back in a > > couple of secs), which means that the optimizer is choosing the right > > index path and finding the right keys in the index. the problem must > > be in the way the data is being accessed. " Is not correct. And it ignores what I tried to explain in my previous post. A SELECT COUNT(*) with good timings does not prove that the index is doing the right thing. A select count(*) just needs the index. Nothing more. A SELECT <SOME COLUMNS> needs the index AND the data. And that makes a BIG difference and it's easy to find queries where a change in the projection list should change and effectively changes the query plan. Maybe this should be one of those cases. Another aspect that may not be clear is this: How did you take your timmings? To have good measures I would recommend something like: SELECT "Start: " || CURRENT YEAR TO SECOND FROM systables WHERE tabid = 1; UNLOAD TO /dev/null<INSERT query here...> ; SELECT "End: " || CURRENT YEAR TO SECOND FROM systables WHERE tabid = 1; You must avoid comparing the time between start and the fetch of the first record(s). Regards.