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=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=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=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=bat[:oid,:oid]{1916813}, _16=0@0) |
| 16 usec | _10 := nil; |
| 17 usec | _18 := bat.reverse(_17=bat[:oid,:oid]{1916813}) |
| 16 usec | _17 := nil; |
| 21040 usec | _21 := algebra.join(_18=<~tmp_1503>bat[:oid,:oid]{1916813}, _19=bat[:oid,:sht]{55914459}) |
| 74147 usec | (ext40,grp38):= group.new(_21=bat[:oid,:sht]{1916813}) |
| 2103 usec | _21 := nil; |
| 43 usec | _24 := bat.mirror(ext40=bat[:oid,:int]{5}) |
| 18 usec | ext40 := nil; |
| 45 usec | _25 := algebra.joinPath(_24=bat[:oid,:oid]{5}, _18=<~tmp_1503>bat[:oid,:oid]{1916813}, |
: |_19=bat[:oid,:sht]{55914459}) |
| 82 usec | _19:bat[:oid,:sht] := nil; |
|1133513 | _28 := algebra.join(_18=<~tmp_1503>bat[:oid,:oid]{1916813}, _26=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=bat[:oid,:str]{1916813}, |
: |grp38=bat[:oid,:oid]{1916813}, _24=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=bat[:oid,:sht]{5}) |
| 21 usec | sql.rsColumn(_30=3, _32="sys.pageviews", _20="channelid", _33="smallint", _34=16, _7=0, |
: |_25=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=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=bat[:oid |:timestamp |
|16 usec # _12:bat[:oid |:timestamp |
|16 usec # _14:bat[:oid |:timestamp |
|15 usec # constraints.emptySet(_14=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=bat[:oid | |
|93 usec # _3:bat[:oid |:timestamp |
|30 usec # _17 := algebra.markT(_10=bat[:oid |:oid |
+-----------------------------------------------------------------------------------------------------------------------+
|15 usec # _10 := nil; |
+-----------------------------------------------------------+----------------------------------------------------------+
|17 usec # _18 := bat.reverse(_17=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=bat[:oid | |
+-----------------------------------------------------------------------------------------------------------------------+
|1997 usec # _21 := nil; |
+-----------------------------------------------------------+----------------------------------------------------------+
|43 usec # _24 := bat.mirror(ext40=bat[:oid |:int |
+-----------------------------------------------------------------------------------------------------------------------+
|18 usec # ext40 := nil; |
+-----------------------------------------------------------+----------------------------------------------------------+
|42 usec # _25 := |:oid |
:algebra.joinPath(_24=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=bat[:oid | |
|1785482734 usec # (_31 |grp59):= |:int |
: |group.derive(_29=bat[:oid | |
+-----------------------------------------------------------------------------------------------------------------------+
|103 usec # _29 := nil; |
|8784 usec # grp57 := nil; |
+-----------------------------------------------------------+----------------------------------------------------------+
|15655 usec # _33 := bat.mirror(_31=bat[:oid |:int |
+-----------------------------------------------------------------------------------------------------------------------+
|6746 usec # _31 := nil; |
+-----------------------------------------------------------+----------------------------------------------------------+
|141173 usec # _34 := |:str |
:algebra.semijoin(_28=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=bat[:oid |:sht |
:sql.resultSet(_38=3 | | | |
+--------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+
|22 usec # |_40="sys.pag |_20="channel |_41="smallin |_42=16 |_7=0 |_25=bat[:oid | |
:(_37=4 | | | | | | | |
+-----------------------------------------------------------------------------------------------------------------------+
|16 usec # _25 := nil; |
+--------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+
|17 usec # |_40="sys.pag |_44="count_n |_45="int" |_46=15 |_7=0 |_35=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=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>
