In postgres I could use explain analyze to see actions performed by database and their cost. I was not able to run explain analyze. I ran EXPLAIN, but can not see any plan or cost. Any ideas how to get costs and query plan like in Postgres?
See the documentation:https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryTiming
MonetDB does not contain a cost-based optimizer as you find in other systems.
You can use both 'EXPLAIN SELECT....' and 'TRACE SELECT ....' to get insight into the expensive components.
On 30/04/16 12:41, Shmagi Kavtaradze wrote:
In postgres I could use explain analyze to see actions performed by database and their cost. I was not able to run explain analyze. I ran EXPLAIN, but can not see any plan or cost. Any ideas how to get costs and query plan like in Postgres?
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Thanks for answer. I ran "trace select count(*) from view" and it returned "user | statement" that I really could not understand. In documentation I saw it should return :
create function sys.tracelog() returns table ( event integer, -- event counter clk varchar(20), -- wallclock, no mtime in kernel pc varchar(50), -- module.function[nr] thread int, -- thread identifier ticks bigint, -- time in microseconds rrsMB bigint, -- resident memory in MB vmMB bigint, -- virtual size in MB reads bigint, -- number of blocks read writes bigint, -- number of blocks written minflt bigint, -- minor page faults majflt bigint, -- major page faults nvcsw bigint, -- non-volantary conext switch stmt string -- actual statement executed )
Any idea how to get user friendly answer for trace?
On Sat, Apr 30, 2016 at 2:18 PM, Martin Kersten martin.kersten@cwi.nl wrote:
See the documentation: https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryTiming
MonetDB does not contain a cost-based optimizer as you find in other systems.
You can use both 'EXPLAIN SELECT....' and 'TRACE SELECT ....' to get insight into the expensive components.
On 30/04/16 12:41, Shmagi Kavtaradze wrote:
In postgres I could use explain analyze to see actions performed by database and their cost. I was not able to run explain analyze. I ran EXPLAIN, but can not see any plan or cost. Any ideas how to get costs and query plan like in Postgres?
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
On 30/04/16 23:52, Shmagi Kavtaradze wrote:
Thanks for answer. I ran "trace select count(*) from view" and it returned "user | statement" that I really could not understand. In documentation I saw it should return :
What you see is the projection over clk/stmt of the underlying tracelog table. This is sufficient for understanding where the time goes. See updated doc page or further https://www.monetdb.org/Documentation/Manuals/MonetDB/Profiler/TraceFormat
create function sys.tracelog() returns table ( event integer, -- event counter clk varchar(20), -- wallclock, no mtime in kernel pc varchar(50), -- module.function[nr] thread int, -- thread identifier ticks bigint, -- time in microseconds rrsMB bigint, -- resident memory in MB vmMB bigint, -- virtual size in MB reads bigint, -- number of blocks read writes bigint, -- number of blocks written minflt bigint, -- minor page faults majflt bigint, -- major page faults nvcsw bigint, -- non-volantary conext switch stmt string -- actual statement executed )
Any idea how to get user friendly answer for trace?
On Sat, Apr 30, 2016 at 2:18 PM, Martin Kersten <martin.kersten@cwi.nl mailto:martin.kersten@cwi.nl> wrote:
See the documentation:https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryTiming MonetDB does not contain a cost-based optimizer as you find in other systems. You can use both 'EXPLAIN SELECT....' and 'TRACE SELECT ....' to get insight into the expensive components. On 30/04/16 12:41, Shmagi Kavtaradze wrote: In postgres I could use explain analyze to see actions performed by database and their cost. I was not able to run explain analyze. I ran EXPLAIN, but can not see any plan or cost. Any ideas how to get costs and query plan like in Postgres? _______________________________________________ users-list mailing list users-list@monetdb.org <mailto:users-list@monetdb.org> https://www.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list@monetdb.org <mailto:users-list@monetdb.org> https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Thanks for update, but I see only usec and statement fields, how can I display other fields like inblock, outblock?
On Sun, May 1, 2016 at 8:40 AM, Martin Kersten martin@monetdb.org wrote:
On 30/04/16 23:52, Shmagi Kavtaradze wrote:
Thanks for answer. I ran "trace select count(*) from view" and it returned "user | statement" that I really could not understand. In documentation I saw it should return :
What you see is the projection over clk/stmt of the underlying tracelog
table. This is sufficient for understanding where the time goes. See updated doc page or further https://www.monetdb.org/Documentation/Manuals/MonetDB/Profiler/TraceFormat
create function sys.tracelog()
returns table ( event integer, -- event counter clk varchar(20), -- wallclock, no mtime in kernel pc varchar(50), -- module.function[nr] thread int, -- thread identifier ticks bigint, -- time in microseconds rrsMB bigint, -- resident memory in MB vmMB bigint, -- virtual size in MB reads bigint, -- number of blocks read writes bigint, -- number of blocks written minflt bigint, -- minor page faults majflt bigint, -- major page faults nvcsw bigint, -- non-volantary conext switch stmt string -- actual statement executed )Any idea how to get user friendly answer for trace?
On Sat, Apr 30, 2016 at 2:18 PM, Martin Kersten <martin.kersten@cwi.nl mailto:martin.kersten@cwi.nl> wrote:
See the documentation:https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryTiming
MonetDB does not contain a cost-based optimizer as you find in othersystems.
You can use both 'EXPLAIN SELECT....' and 'TRACE SELECT ....' to getinsight into the expensive components.
On 30/04/16 12:41, Shmagi Kavtaradze wrote: In postgres I could use explain analyze to see actions performedby database and their cost. I was not able to run explain analyze. I ran EXPLAIN, but can not see any plan or cost. Any ideas how to get costs and query plan like in Postgres?
_______________________________________________ users-list mailing list users-list@monetdb.org <mailto:users-list@monetdb.org> https://www.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list@monetdb.org <mailto:users-list@monetdb.org> https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
On 01/05/16 20:45, Shmagi Kavtaradze wrote:
Thanks for update, but I see only usec and statement fields, how can I display other fields like inblock, outblock?
On Sun, May 1, 2016 at 8:40 AM, Martin Kersten <martin@monetdb.org mailto:martin@monetdb.org> wrote:
On 30/04/16 23:52, Shmagi Kavtaradze wrote: Thanks for answer. I ran "trace select count(*) from view" and it returned "user | statement" that I really could not understand. In documentation I saw it should return : What you see is the projection over clk/stmt of the underlying tracelog table. This is sufficient for understanding where the time goes. See updated doc page or further https://www.monetdb.org/Documentation/Manuals/MonetDB/Profiler/TraceFormat create function sys.tracelog()
It is a table returning function that you can use in the FROM clause
returns table ( event integer, -- event counter clk varchar(20), -- wallclock, no mtime in kernel pc varchar(50), -- module.function[nr] thread int, -- thread identifier ticks bigint, -- time in microseconds rrsMB bigint, -- resident memory in MB vmMB bigint, -- virtual size in MB reads bigint, -- number of blocks read writes bigint, -- number of blocks written minflt bigint, -- minor page faults majflt bigint, -- major page faults nvcsw bigint, -- non-volantary conext switch stmt string -- actual statement executed ) Any idea how to get user friendly answer for trace? On Sat, Apr 30, 2016 at 2:18 PM, Martin Kersten <martin.kersten@cwi.nl <mailto:martin.kersten@cwi.nl> <mailto:martin.kersten@cwi.nl <mailto:martin.kersten@cwi.nl>>> wrote: See the documentation:https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryTiming MonetDB does not contain a cost-based optimizer as you find in other systems. You can use both 'EXPLAIN SELECT....' and 'TRACE SELECT ....' to get insight into the expensive components. On 30/04/16 12:41, Shmagi Kavtaradze wrote: In postgres I could use explain analyze to see actions performed by database and their cost. I was not able to run explain analyze. I ran EXPLAIN, but can not see any plan or cost. Any ideas how to get costs and query plan like in Postgres? _______________________________________________ users-list mailing list users-list@monetdb.org <mailto:users-list@monetdb.org> <mailto:users-list@monetdb.org <mailto:users-list@monetdb.org>> https://www.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list@monetdb.org <mailto:users-list@monetdb.org> <mailto:users-list@monetdb.org <mailto:users-list@monetdb.org>> https://www.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list@monetdb.org <mailto:users-list@monetdb.org> https://www.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list@monetdb.org <mailto:users-list@monetdb.org> https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Still not sure how to do it. Tried "TRACE inblock, outblock from (select bitand(a.sentddec, b.sentddec) as res from chunks10000 a, chunks10000 b where a.chunkid=b.chunkid AND a.sentid<b.sentid);" But I guess I am doing something wrong.
sql>trace select 1; +--------------+ | single_value | +==============+ | 1 | +--------------+ 1 tuple (3.545ms) +-------+--------------------------------------------------------------------------------------+ | ticks | statement | +=======+======================================================================================+ | 1 | X_10=0@0:void := querylog.define("trace select 1;":str,"default_pipe":str,4:int); | | 6 | sql.resultSet(".L":str,"single_value":str,"tinyint":str,8:int,0:int,7:int,A0=1:bte); | | 1 | end user.s2_1; | | 751 | function user.s2_1{autoCommit=true}(A0:bte):void; | | 886 | X_4=0@0:void := user.s2_1(1:bte); | +-------+--------------------------------------------------------------------------------------+ 5 tuples (3.659ms)
sql>select * from sys.tracelog(); +-------+---------------+---------------+--------+-------+-------+------+-------+--------+--------+--------+-------+--------------------------------------------------------------------------------------+ | event | clk | pc | thread | ticks | rrsmb | vmmb | reads | writes | minflt | majflt | nvcsw | stmt | +=======+===============+===============+========+=======+=======+======+=======+========+========+========+=======+======================================================================================+ | 0 | 10:37:07.375 | user.s2_1[1]5 | 3 | 1 | 53 | 0 | 0 | 0 | 0 | 0 | 0 | X_10=0@0:void := querylog.define("trace select 1;":str,"default_pipe":str,4:int); | | 1 | 10:37:07.375 | user.s2_1[2]5 | 3 | 6 | 53 | 0 | 0 | 80 | 10 | 0 | 0 | sql.resultSet(".L":str,"single_value":str,"tinyint":str,8:int,0:int,7:int,A0=1:bte); | | 2 | 10:37:07.375 | user.s2_1[3]5 | 3 | 1 | 53 | 0 | 0 | 0 | 0 | 0 | 0 | end user.s2_1; | | 3 | 10:37:07.375 | user.s2_1[0]5 | 3 | 751 | 53 | 0 | 0 | 0 | 0 | 0 | 0 | function user.s2_1{autoCommit=true}(A0:bte):void; | | 4 | 10:37:07.375 | user.main[3]4 | 3 | 886 | 53 | 0 | 0 | 0 | 0 | 0 | 0 | X_4=0@0:void := user.s2_1(1:bte); | +-------+---------------+---------------+--------+-------+-------+------+-------+--------+--------+--------+-------+--------------------------------------------------------------------------------------+ 5 tuples (4.906ms)
----- On May 1, 2016, at 9:12 PM, Shmagi Kavtaradze kavtaradze.s@gmail.com wrote:
Still not sure how to do it. Tried "TRACE inblock, outblock from (select bitand(a.sentddec, b.sentddec) as res from chunks10000 a, chunks10000 b where a.chunkid=b.chunkid AND a.sentid<b.sentid);" But I guess I am doing something wrong.
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (4)
-
Martin Kersten -
Martin Kersten -
Shmagi Kavtaradze -
Stefan Manegold