In CockroachDB, how do I figure out how much memory a query is using?

Question

I'm trying to figure out how to attribute memory use in CockroachDB to a particular query. How do I do that? I've tried using EXPLAIN, but that doesn't seem to show memory usage.

Answer 1

You can use EXPLAIN ANALYZE to show the answer to this question. Here's a transcript from CockroachDB 20.2.2 that shows the method:

> ./cockroach-v20.2.2.darwin-10.9-amd64/cockroach demo
# Enter \? for a brief introduction.
#
[email protected]:64830/demo> create table a (a int primary key, b int);
CREATE TABLE

Time: 3ms total (execution 3ms / network 0ms)

[email protected]:64830/demo> insert into a select g, 1 from generate_series(1, 10000) g(g);
INSERT 10000

Time: 81ms total (execution 81ms / network 0ms)

[email protected]:64830/demo> select count(*) from a group by b;
  count
---------
  10000
(1 row)

Time: 7ms total (execution 7ms / network 0ms)

[email protected]:64830/demo> explain analyze select count(*) from a group by b;
  automatic |                                                                                                                                                                                                                                                                                    url
------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    true    | https://cockroachdb.github.io/distsqlplan/decode.html#eJyUkV-Pk0AUxd_9FDf3qTWjO6Cb6DyVXashIlSg0WpIM2VuCAkwODPEbRq-uwHinzVx4z6ec--5c36ZC9pvDQrcft5FQRhDEAfR4csWVm_CLM8-RmvIttH2NodSD51bPV3D2zT5ABLepcl-BzcHOCHDTiuKZUsWxVf0sGDYG12StdpM1mVeCNUdCs6w7vrBTXbBsNSGUFzQ1a4hFJjLU0MpSUXmiiNDRU7WzXxWbnpTt9KckWHWy84KeIYMk8EJ2PjI0OjvFgxJJcDjnE9x62TTgKtbEnD93L9-1VpkeDo7-rn5gvvwvr7BYmSoB_e7mXWyIhTeyP6_fVBVhirptLny7pffTDqID8c4yY_xPopWG2-NDG-TfZwf0-RTtlo_BublL5hW3kFLrTZnGCwpAa_5g0D-Y4BSsr3uLN2D-ddlPhYMSVW0fLnVgylpZ3Q5P7PIZM7NhiLrlqm3iLBbRlPBP8Peg2H_r3AxPvkRAAD__3pr4n4=
(1 row)

Time: 13ms total (execution 13ms / network 0ms)

The link shows the following pictures, which contains the memory usage per operator. Notice that the aggregator (the group by operator) shows 70 kb, the quantity that we were looking for: