On 21-10-2007 16:41 Niels Nes wrote:
Could your try with the to be released code, ie run
monetdb-install.sh --enable-sql --enable-optimize --nightly=stable
I get slightly different code, which looks faster.
Its equally slow, and the fast query seems to be ten times slower now (7 secs vs 0.7). I've attached the new traces. If you like, I can probably get you a copy of the data off-list, it should be repeatable with only one day of data, which is 11MB bzip2'ed. Best regards, Arjen sql>trace select channelid, count(clientip) from pageviews where timestamp between '2007-09-21 00:00:00' and '2007-09-22 00:00:00' group by channelid; +-------------+----------------------------------------------------------------------------------------------------------+ | 2 usec | mdb.setTimer(_2=true) | | 155 usec | _3:bat[:oid,:timestamp] := sql.bind(_4="sys", _5="pageviews", _6="timestamp", _7=0) | | 18 usec | _12:bat[:oid,:timestamp] := sql.bind(_4="sys", _5="pageviews", _6="timestamp", _13=1) | | 17 usec | constraints.emptySet(_12=<tmp_1467>bat[:oid,:timestamp]{0}) | | 17 usec | _12:bat[:oid,:timestamp] := nil; | | 48 usec | _14:bat[:oid,:timestamp] := sql.bind(_4="sys", _5="pageviews", _6="timestamp", _15=2) | | 16 usec | constraints.emptySet(_14=<tmp_1221>bat[:oid,:timestamp]{0}) | | 16 usec | _14:bat[:oid,:timestamp] := nil; | | 56 usec | _19:bat[:oid,:sht] := sql.bind(_4="sys", _5="pageviews", _20="channelid", _7=0) | | 17 usec | _26:bat[:oid,:str] := sql.bind(_4="sys", _5="pageviews", _27="clientip", _7=0) | | 25 usec | _8 := calc.timestamp(A0="2007-09-21 00:00:00") | | 17 usec | _9 := calc.timestamp(A1="2007-09-22 00:00:00") | |6112173 | _10 := algebra.uselect(_3=<tmp_1253>bat[:oid,:timestamp]{55914459}, _8=2007-09-21 00:00:00.000, | :usec |_9=2007-09-22 00:00:00.000, _11=true, _11=true) | | 95 usec | _3:bat[:oid,:timestamp] := nil; | | 29 usec | _17 := algebra.markT(_10=<tmp_1475>bat[:oid,:oid]{1916813}, _16=0@0) | | 16 usec | _10 := nil; | | 17 usec | _18 := bat.reverse(_17=<tmp_1503>bat[:oid,:oid]{1916813}) | | 16 usec | _17 := nil; | | 21040 usec | _21 := algebra.join(_18=<~tmp_1503>bat[:oid,:oid]{1916813}, _19=<tmp_1266>bat[:oid,:sht]{55914459}) | | 74147 usec | (ext40,grp38):= group.new(_21=<tmp_1477>bat[:oid,:sht]{1916813}) | | 2103 usec | _21 := nil; | | 43 usec | _24 := bat.mirror(ext40=<tmp_1474>bat[:oid,:int]{5}) | | 18 usec | ext40 := nil; | | 45 usec | _25 := algebra.joinPath(_24=<tmp_1501>bat[:oid,:oid]{5}, _18=<~tmp_1503>bat[:oid,:oid]{1916813}, | : |_19=<tmp_1266>bat[:oid,:sht]{55914459}) | | 82 usec | _19:bat[:oid,:sht] := nil; | |1133513 | _28 := algebra.join(_18=<~tmp_1503>bat[:oid,:oid]{1916813}, _26=<tmp_1260>bat[:oid,:str]{55914459}) | :usec | | | 7107 usec | _18 := nil; | | 39 usec | _26:bat[:oid,:str] := nil; | | 68945 usec | _29:bat[:oid,:int] := aggr.count_no_nil(_28=<tmp_1474>bat[:oid,:str]{1916813}, | : |grp38=<tmp_1502>bat[:oid,:oid]{1916813}, _24=<tmp_1501>bat[:oid,:oid]{5}) | | 25260 usec | _28 := nil; | | 13018 usec | grp38 := nil; | | 81 usec | _24 := nil; | | 21 usec | _30 := sql.resultSet(_15=2, _13=1, _25=<tmp_1476>bat[:oid,:sht]{5}) | | 21 usec | sql.rsColumn(_30=3, _32="sys.pageviews", _20="channelid", _33="smallint", _34=16, _7=0, | : |_25=<tmp_1476>bat[:oid,:sht]{5}) | | 16 usec | _25 := nil; | | 17 usec | sql.rsColumn(_30=3, _32="sys.pageviews", _36="count_no_nil_clientip", _37="int", _38=15, _7=0, | : |_29=<tmp_1211>bat[:oid,:int]{5}) | | 15 usec | _29:bat[:oid,:int] := nil; | +-------------+----------------------------------------------------------------------------------------------------------+ | 0, 538187 | | 1, 1108478 | | 4, 42867 | | 3, 145565 | | 2, 81716 | +-------------+----------------------------------------------------------------------------------------------------------+ | 47 usec | sql.exportResult(_30=3, _40="") | |7458996 | user.s31_1(_4="2007-09-21 00:00:00", _5="2007-09-22 00:00:00") | :usec | | +-------------+----------------------------------------------------------------------------------------------------------+ sql>trace select channelid, count(distinct clientip), count(*) from pageviews where timestamp between '2007-09-21 00:00:00' and '2007-09-22 00:00:00' group by channelid;more>more>more> +-----------------------------------------------------------------------------------------------------------------------+ |2 usec # mdb.setTimer(_2=true) | +-----------------------------------------------------------+----------------------------------------------------------+ |81 usec # _3:bat[:oid |:timestamp | |17 usec # _12:bat[:oid |:timestamp | |16 usec # constraints.emptySet(_12=<tmp_1467>bat[:oid |:timestamp | |16 usec # _12:bat[:oid |:timestamp | |16 usec # _14:bat[:oid |:timestamp | |15 usec # constraints.emptySet(_14=<tmp_1221>bat[:oid |:timestamp | |15 usec # _14:bat[:oid |:timestamp | |16 usec # _19:bat[:oid |:sht | |16 usec # _26:bat[:oid |:str | +-----------------------------------------------------------------------------------------------------------------------+ |22 usec # _8 := calc.timestamp(A0="2007-09-21 00:00:00") | |16 usec # _9 := calc.timestamp(A1="2007-09-22 00:00:00") | +-----------------------------------------------------------+----------------------------------------------------------+ |5955043 usec # _10 := |:timestamp | :algebra.uselect(_3=<tmp_1253>bat[:oid | | |93 usec # _3:bat[:oid |:timestamp | |30 usec # _17 := algebra.markT(_10=<tmp_1502>bat[:oid |:oid | +-----------------------------------------------------------------------------------------------------------------------+ |15 usec # _10 := nil; | +-----------------------------------------------------------+----------------------------------------------------------+ |17 usec # _18 := bat.reverse(_17=<tmp_1474>bat[:oid |:oid | +-----------------------------------------------------------------------------------------------------------------------+ |15 usec # _17 := nil; | +-----------------------------------------------------------+----------------------------------------------------------+ |20933 usec # _21 := |:oid | :algebra.join(_18=<~tmp_1474>bat[:oid | | +---------------------------------------+--------------------------------------+--------------------------------------+ |73434 usec # (ext40 |grp38):= |:sht | : |group.new(_21=<tmp_1475>bat[:oid | | +-----------------------------------------------------------------------------------------------------------------------+ |1997 usec # _21 := nil; | +-----------------------------------------------------------+----------------------------------------------------------+ |43 usec # _24 := bat.mirror(ext40=<tmp_1477>bat[:oid |:int | +-----------------------------------------------------------------------------------------------------------------------+ |18 usec # ext40 := nil; | +-----------------------------------------------------------+----------------------------------------------------------+ |42 usec # _25 := |:oid | :algebra.joinPath(_24=<tmp_1501>bat[:oid | | |15 usec # _19:bat[:oid |:sht | |1088640 usec # _28 := |:oid | :algebra.join(_18=<~tmp_1474>bat[:oid | | +-----------------------------------------------------------------------------------------------------------------------+ |6947 usec # _18 := nil; | +-----------------------------------------------------------+----------------------------------------------------------+ |35 usec # _26:bat[:oid |:str | +---------------------------------------+--------------------------------------+--------------------------------------+ |221825 usec # (_29 |grp57):= |:oid | : |group.new(grp38=<tmp_1503>bat[:oid | | |1785482734 usec # (_31 |grp59):= |:int | : |group.derive(_29=<tmp_1474>bat[:oid | | +-----------------------------------------------------------------------------------------------------------------------+ |103 usec # _29 := nil; | |8784 usec # grp57 := nil; | +-----------------------------------------------------------+----------------------------------------------------------+ |15655 usec # _33 := bat.mirror(_31=<tmp_1504>bat[:oid |:int | +-----------------------------------------------------------------------------------------------------------------------+ |6746 usec # _31 := nil; | +-----------------------------------------------------------+----------------------------------------------------------+ |141173 usec # _34 := |:str | :algebra.semijoin(_28=<tmp_1477>bat[:oid | | +-----------------------------------------------------------------------------------------------------------------------+ |33831 usec # _28 := nil; | |2743 usec # _33 := nil; | +-----------------------------------------------------------+----------------------------------------------------------+ |38271 usec # _35:bat[:oid |:int | +-----------------------------------------------------------------------------------------------------------------------+ |5579 usec # _34 := nil; | +-----------------------------------------------------------+----------------------------------------------------------+ |38047 usec # _36:bat[:oid |:int | +-----------------------------------------------------------------------------------------------------------------------+ |12848 usec # grp38 := nil; | |84 usec # _24 := nil; | +-----------------------------+----------------------------+----------------------------+----------------------------+ |22 usec # _37 := |_13=1 |_25=<tmp_1476>bat[:oid |:sht | :sql.resultSet(_38=3 | | | | +--------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+ |22 usec # |_40="sys.pag |_20="channel |_41="smallin |_42=16 |_7=0 |_25=<tmp_147 |:sht | :sql.rsColumn |eviews" |id" |t" | | |6>bat[:oid | | :(_37=4 | | | | | | | | +-----------------------------------------------------------------------------------------------------------------------+ |16 usec # _25 := nil; | +--------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+ |17 usec # |_40="sys.pag |_44="count_n |_45="int" |_46=15 |_7=0 |_35=<tmp_121 |:int | :sql.rsColumn |eviews" |o_nil_client | | | |1>bat[:oid | | :(_37=4 | |ip" | | | | | | +-----------------------------------------------------------+----------------------------------------------------------+ |15 usec # _35:bat[:oid |:int | +--------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+ |17 usec # |_40="sys.pag |_48="count_c |_45="int" |_49=32 |_7=0 |_36=<tmp_121 |:int | :sql.rsColumn |eviews" |hannelid" | | | |0>bat[:oid | | :(_37=4 | | | | | | | | +-----------------------------------------------------------+----------------------------------------------------------+ |15 usec # _36:bat[:oid |:int | +-----------------------------------------------------------+----------------------------------------------------------+ +-----------+----------------------+----------------+ |channelid |count_no_nil_clientip |count_channelid | +===========+======================+================+ | 0 | 77524 | 538187 | | 1 | 110681 | 1108478 | | 4 | 20120 | 42867 | | 3 | 44457 | 145565 | | 2 | 30274 | 81716 | +-----------+----------------------+ |channelid |count_no_nil_clientip | +===========+======================+ | 52 usec | _51="") | :# | | :sql.expor | | :tResult(_ | | :37=4 | | |179316609 | _5="2007-09-22 | :8 usec # |00:00:00") | :user.s32_ | | :1(_4="200 | | :7-09-21 | | :00:00:00" | | +-----------+----------------------+ sql>