| *** gerhard has joined #ingres | 00:00 | |
| *** Mud has joined #ingres | 00:09 | |
| *** grantc has joined #ingres | 00:14 | |
| *** Dejan has joined #ingres | 01:20 | |
| gerhard | Hi all | 01:23 |
|---|---|---|
| grantc | morning gerhard | 01:23 |
| Dejan | hello everyone | 01:26 |
| gerhard | It's nice that the Ingres KB articles are available now without being logged into the service desk. Also nice that it is now possible to make a browser bookmark for an article. | 01:29 |
| Dejan | really ? | 01:35 |
| Dejan | there is one article from john smedley i would like to steal | 01:35 |
| Dejan | about perfromance tuning | 01:35 |
| grantc | www.ingres.com/kb | 01:37 |
| Dejan | god | 01:38 |
| Dejan | this is brilliant | 01:38 |
| Dejan | :D | 01:38 |
| Vroomfondle | how queer: I have two tables, A and B. Both have the same key. A has one row in it. If I join B on A with INNER JOIN, the query takes > 10 seconds. If I rewrite the query and copy-paste the value from A into "select * from B where keycolumn = 'value_from_A'", the query runs instantly. | 02:38 |
| Vroomfondle | indexes on both look fine | 02:38 |
| Vroomfondle | I guess I might have to learn how to actually read a QEP properly | 02:40 |
| Vroomfondle | it looks like the QEP for the join involves a sort on 9983 pages' worth of B, even though the column that I'm joining on is the leftmost part of the key for the table. | 02:41 |
| Dejan | the order is important in sql | 02:43 |
| Vroomfondle | oh, the index for that table is stored as ISAM | 02:43 |
| Vroomfondle | could that be the cause of the performance problem? | 02:43 |
| Dejan | you said A has 1 row?? | 02:43 |
| Dejan | right? | 02:43 |
| Vroomfondle | yes. | 02:44 |
| Vroomfondle | A has one row, B has lots of rows | 02:44 |
| Vroomfondle | (about a million) | 02:44 |
| Dejan | A inner join B is not the same as select * from B where keycolumn=value... | 02:45 |
| Dejan | right? | 02:45 |
| Vroomfondle | indeed | 02:45 |
| grantc | if the stats are missing cola = col b is assumed to be 10% | 02:46 |
| Vroomfondle | it would appear that the key index is used when using WHERE, but when using JOIN it's using a separate index | 02:46 |
| grantc | cola = value is 1% iirc | 02:46 |
| Vroomfondle | we tried building the stats on Friday. It made no difference. | 02:48 |
| Vroomfondle | In fact, my test afterwards actually ran *slower* but that might've been due to system load | 02:48 |
| Vroomfondle | and I'm wrong about the indexes, actually. It's using the same index for both. I'm gonna try modifying it to btree. | 02:49 |
| Vroomfondle | hrm... that didn't help much, if at all | 03:04 |
| grantc | what about a index that just includes that column | 03:05 |
| Vroomfondle | worth a try I guess | 03:06 |
| Vroomfondle | doesn't seem to have helped | 03:08 |
| Vroomfondle | the only difference in the query plans appears to be the sort. If I JOIN, it sorts the index before joining. If I use WHERE x=y, it doesn't do a sort. | 03:09 |
| Dejan | did you try to JOIN with USING clause? | 03:22 |
| Dejan | it may make a difference | 03:23 |
| Vroomfondle | I'm just doing INNER JOIN tableB b on b.x = a.x | 03:25 |
| Dejan | try INNER JOIN B USING (x) | 03:26 |
| Vroomfondle | still does the sort | 03:27 |
| Vroomfondle | I have a hypothesis which I'm about to test, though. Table A is a heap (I thought that'd make no difference as it only has one row). I'll try modifying it to an ISAM or a BTREE and see if that makes a difference. | 03:28 |
| *** javahorn_ has joined #ingres | 03:31 | |
| *** javahorn_ has quit IRC | 03:57 | |
| *** atrofast has joined #ingres | 04:44 | |
| *** atrofast has quit IRC | 05:03 | |
| *** cthibert has joined #ingres | 05:05 | |
| *** atrofast has joined #ingres | 05:06 | |
| *** cthibert1 has joined #ingres | 05:08 | |
| *** atrofast has left #ingres | 05:09 | |
| *** atrofast has joined #ingres | 05:09 | |
| *** cthibert has quit IRC | 05:10 | |
| *** rossand has joined #ingres | 05:26 | |
| *** ChanServ sets mode: +o rossand | 05:26 | |
| * Vroomfondle replaces his joins with select loops, and now his code takes 1/5th of the time to execute | 05:36 | |
| pboro | :-o | 05:36 |
| pboro | that's sad | 05:36 |
| pboro | I like JOINs because of they are so readable | 05:36 |
| Vroomfondle | me too. However it seems that Ingres sorts both indexes which are involved in the join, and if one of the indexes is big then that adds a huge overhead to the query (on our system, at least). | 05:36 |
| pboro | does that mean it wont use the index at all? | 05:36 |
| pboro | or what happens then | 05:37 |
| Vroomfondle | it does use the index, but the sort seems to take a while | 05:39 |
| Vroomfondle | about ten seconds in this case | 05:40 |
| pboro | weird... | 05:40 |
| pboro | what kind of join is it using? | 05:41 |
| Vroomfondle | inner join | 05:41 |
| pboro | did you check qep? | 05:41 |
| Vroomfondle | yes, that's how I found this out | 05:41 |
| pboro | yeah but the technical algo :) | 05:41 |
| Vroomfondle | oh. FSM | 05:41 |
| pboro | do you have hash joins disabled? | 05:42 |
| Vroomfondle | I have no idea. How do I find out? | 05:42 |
| pboro | check config.dat and the value for opf_hash_join | 05:43 |
| pboro | and which ingres version? | 05:43 |
| Vroomfondle | yeah, it's off. | 05:43 |
| Vroomfondle | 9.2. | 05:43 |
| pboro | it's an upgrade? not a clean install? | 05:44 |
| Vroomfondle | I don't know for sure. I'm not in charge of that kind of thing. I *think* we only ever do clean installs. | 05:44 |
| Vroomfondle | is a hash join better? I've reached the limits of my knowledge here | 05:45 |
| pboro | you could try if running the query with "SET HASH" and see if it makes any difference on the query time and QEP | 05:46 |
| pboro | ie. first run SET HASH; in the session and then the query | 05:46 |
| pboro | opf_hash_join defaults to on afaik | 05:46 |
| pboro | yeah FSM is the "old-type" join way and hash is used if it's applicable and opf_hash_join is not disabled | 05:47 |
| pboro | hash joins are also being enhanced... for example http://community.ingres.com/wiki/Performance_Enhancements#SIR_122512:_Hash-join.2C_Hash-agg_improvements | 05:47 |
| *** cthibert has joined #ingres | 05:49 | |
| *** cthibert1 has quit IRC | 05:49 | |
| Vroomfondle | hrm. I did SET HASH then SET QEP then my query | 05:49 |
| Vroomfondle | it's still using FSM | 05:49 |
| pboro | ok | 05:49 |
| pboro | FSM stands for Full Sort Merge... here's an explanation of joins: http://docs.ingres.com/Ingres/9.2/Database%20Administrator%20Guide/typesofnodesinaqep.htm#o2283 | 05:52 |
| pboro | what join type or similar does it use when you do it traditionally (where col1.id = col2.id)? | 05:52 |
| Vroomfondle | same thing | 05:53 |
| pboro | FSM in both cases? :o | 05:53 |
| Vroomfondle | yup | 05:53 |
| pboro | it's pretty weird that there's a differences between JOIN and traditional way of doing join | 05:57 |
| pboro | since I have understood that JOIN would be broken into pieces similar to traditional join for execution, but apparently it's not the case | 05:58 |
| Vroomfondle | I wonder if there's something else in our configuration that's forcing the FSM algorithm to be used | 06:14 |
| pboro | if you have support contract, then just create a request :) | 06:14 |
| Vroomfondle | yeah, think I'll raise a ticket | 06:25 |
| pboro | since it's easy to reproduce, there should not be a problem in investigating it | 06:25 |
| *** cthibert1 has joined #ingres | 06:30 | |
| *** cthibert has quit IRC | 06:32 | |
| *** Mud has quit IRC | 07:03 | |
| *** gerhard has quit IRC | 07:30 | |
| *** Alex| has quit IRC | 07:33 | |
| *** Mud has joined #ingres | 07:46 | |
| *** atrofast has quit IRC | 08:16 | |
| *** atrofast has joined #ingres | 08:20 | |
| *** dyki has joined #ingres | 08:21 | |
| *** atrofast has quit IRC | 08:27 | |
| *** atrofast has joined #ingres | 08:29 | |
| *** atrofast has quit IRC | 08:30 | |
| *** atrofast has joined #ingres | 08:32 | |
| *** dyki has quit IRC | 08:49 | |
| *** grantc has quit IRC | 08:51 | |
| *** Alex| has joined #ingres | 09:53 | |
| *** ChanServ sets mode: +o Alex| | 09:53 | |
| *** Dejan has quit IRC | 10:20 | |
| *** rossand has quit IRC | 11:14 | |
| *** rossand has joined #ingres | 11:15 | |
| *** ChanServ sets mode: +o rossand | 11:15 | |
| *** atrofast has quit IRC | 12:56 | |
| *** atrofast has joined #ingres | 13:00 | |
| *** DerMeister has joined #ingres | 13:08 | |
| *** atrofast has quit IRC | 13:14 | |
| *** atrofast has joined #ingres | 13:17 | |
| *** rossand has quit IRC | 13:40 | |
| *** cthibert1 has left #ingres | 13:49 | |
| *** Mud has quit IRC | 13:55 | |
| *** Alex| has quit IRC | 14:08 | |
| *** DerMeister has quit IRC | 14:25 | |
| *** atrofast has quit IRC | 16:57 | |
| *** cytrinox_ has joined #ingres | 19:20 | |
| *** cytrinox has quit IRC | 19:23 | |
| *** cytrinox_ is now known as cytrinox | 19:23 | |
| *** Mud has joined #ingres | 21:40 | |
| *** Alex| has joined #ingres | 22:39 | |
| *** ChanServ sets mode: +o Alex| | 22:39 | |
| *** Alex| has quit IRC | 23:25 | |
| *** Alex| has joined #ingres | 23:57 | |
| *** ChanServ sets mode: +o Alex| | 23:57 | |
| *** Alex| has quit IRC | 23:58 | |
| *** Alex| has joined #ingres | 23:58 | |
| *** ChanServ sets mode: +o Alex| | 23:58 | |
Generated by irclog2html.py 2.7 by Marius Gedminas - find it at mg.pov.lt!