How to understand 'explain analyze' output?

#1

I try to understand why my queries are so slow. It takes several seconds to execute aggregation functions or LAST-selector over series of about several hundred thousand points.

So using “explain analyze” i got:

> EXPLAIN ANALYZE SELECT COUNT(delta),SUM(delta) FROM inouts WHERE address='AG6MCBRozzcQMMi2SXdi4TJhhLeycQERyk'
EXPLAIN ANALYZE
---------------
.
└── select
    ├── execution_time: 772.546679ms
    ├── planning_time: 4.280391913s
    ├── total_time: 5.052938592s
    └── build_cursor
        ├── labels
        │   └── statement: SELECT count(delta::float), sum(delta::float) FROM trittium_explorer.autogen.inouts WHERE address::tag = 'AG6MCBRozzcQMMi2SXdi4TJhhLeycQERyk'
        ├── iterator_scanner
        │   ├── labels
        │   │   └── expr: count(delta::float)
        │   └── create_iterator
        │       ├── labels
        │       │   ├── cond: address::tag = 'AG6MCBRozzcQMMi2SXdi4TJhhLeycQERyk'
        │       │   ├── measurement: inouts
        │       │   └── shard_id: 34
        │       ├── cursors_ref: 410301
        │       ├── cursors_aux: 0
        │       ├── cursors_cond: 0
        │       ├── float_blocks_decoded: 409112
        │       ├── float_blocks_size_bytes: 14019122
        │       ├── integer_blocks_decoded: 0
        │       ├── integer_blocks_size_bytes: 0
        │       ├── unsigned_blocks_decoded: 0
        │       ├── unsigned_blocks_size_bytes: 0
        │       ├── string_blocks_decoded: 0
        │       ├── string_blocks_size_bytes: 0
        │       ├── boolean_blocks_decoded: 0
        │       ├── boolean_blocks_size_bytes: 0
        │       └── planning_time: 2.012647913s
        └── iterator_scanner
            ├── labels
            │   └── expr: sum(delta::float)
            └── create_iterator
                ├── labels
                │   ├── cond: address::tag = 'AG6MCBRozzcQMMi2SXdi4TJhhLeycQERyk'
                │   ├── measurement: inouts
                │   └── shard_id: 34
                ├── cursors_ref: 410301
                ├── cursors_aux: 0
                ├── cursors_cond: 0
                ├── float_blocks_decoded: 409112
                ├── float_blocks_size_bytes: 14019122
                ├── integer_blocks_decoded: 0
                ├── integer_blocks_size_bytes: 0
                ├── unsigned_blocks_decoded: 0
                ├── unsigned_blocks_size_bytes: 0
                ├── string_blocks_decoded: 0
                ├── string_blocks_size_bytes: 0
                ├── boolean_blocks_decoded: 0
                ├── boolean_blocks_size_bytes: 0
                └── planning_time: 2.267512106s

Length of this queried series - about 800000.

Why plannig_time is so big compared to execution_time?

Also just EXPLAIN:

> EXPLAIN SELECT COUNT(delta),SUM(delta) FROM inouts WHERE address='AG6MCBRozzcQMMi2SXdi4TJhhLeycQERyk'
QUERY PLAN
----------
EXPRESSION: count(delta::float)
NUMBER OF SHARDS: 1
NUMBER OF SERIES: 410313
CACHED VALUES: 1339
NUMBER OF FILES: 408974
NUMBER OF BLOCKS: 409112
SIZE OF BLOCKS: 14019122

EXPRESSION: sum(delta::float)
NUMBER OF SHARDS: 1
NUMBER OF SERIES: 410313
CACHED VALUES: 1339
NUMBER OF FILES: 408974
NUMBER OF BLOCKS: 409112
SIZE OF BLOCKS: 14019122

It seems wierld to me “NUMBER OF SERIES: 410313”. Could it be due to additional tags, not used in this query? (Additional tags allow me to query more specific sets of points or even individual point when i need to, and it works quickly, but these tags are not used in this query).

Other wierld thing: “NUMBER OF SHARDS: 1”, although this series lasts over several month and default retention policy is used (shardGroupDuration=1week). Could it be due to the fact i have many points stored “into past” with explicit specification of timestamp when importing data from other database, not in real time? (Though, import is almost time-ordered). Could it affect the speed of query?

Server with SSD, “tsi1” indexing is used instead of “inmem” due to high cardinality of tags (hundreds of millions).