index for custom properties

[tasiotas]

Hi,

I am attaching custom props to each event and later on I am using it for filtering.
Would it make sense to add indexes for meta.key and meta.value to speed up query?

I actually tried it, but didn't notice any difference. Its highly possible that I did something wrong.

here are indexes:

SHOW INDEXES FROM events_v2 FROM plausible

Query id: 2235c6bc-decc-4bf7-a42e-152dafdd9089

┌─table─────┬─non_unique─┬─key_name───────┬─seq_in_index─┬─column_name─┬─collation─┬─cardinality─┬─sub_part─┬─packed─┬─null─┬─index_type─┬─comment─┬─index_comment─┬─visible─┬─expression─┐
│ events_v2 │          1 │ meta_key_idx   │            1 │             │ ᴺᵁᴸᴸ      │           0 │ ᴺᵁᴸᴸ     │ ᴺᵁᴸᴸ   │ ᴺᵁᴸᴸ │ MINMAX     │         │               │ YES     │ meta.key   │
│ events_v2 │          1 │ meta_value_idx │            1 │             │ ᴺᵁᴸᴸ      │           0 │ ᴺᵁᴸᴸ     │ ᴺᵁᴸᴸ   │ ᴺᵁᴸᴸ │ MINMAX     │         │               │ YES     │ meta.value │
└───────────┴────────────┴────────────────┴──────────────┴─────────────┴───────────┴─────────────┴──────────┴────────┴──────┴────────────┴─────────┴───────────────┴─────────┴────────────┘
┌─table─────┬─non_unique─┬─key_name─┬─seq_in_index─┬─column_name───────┬─collation─┬─cardinality─┬─sub_part─┬─packed─┬─null─┬─index_type─┬─comment─┬─index_comment─┬─visible─┬─expression─┐
│ events_v2 │          1 │ PRIMARY  │            1 │ name              │ A         │           0 │ ᴺᵁᴸᴸ     │ ᴺᵁᴸᴸ   │ ᴺᵁᴸᴸ │ PRIMARY    │         │               │ YES     │            │
│ events_v2 │          1 │ PRIMARY  │            2 │ site_id           │ A         │           0 │ ᴺᵁᴸᴸ     │ ᴺᵁᴸᴸ   │ ᴺᵁᴸᴸ │ PRIMARY    │         │               │ YES     │            │
│ events_v2 │          1 │ PRIMARY  │            3 │ toDate(timestamp) │ A         │           0 │ ᴺᵁᴸᴸ     │ ᴺᵁᴸᴸ   │ ᴺᵁᴸᴸ │ PRIMARY    │         │               │ YES     │            │
│ events_v2 │          1 │ PRIMARY  │            4 │ user_id           │ A         │           0 │ ᴺᵁᴸᴸ     │ ᴺᵁᴸᴸ   │ ᴺᵁᴸᴸ │ PRIMARY    │         │               │ YES     │            │
└───────────┴────────────┴──────────┴──────────────┴───────────────────┴───────────┴─────────────┴──────────┴────────┴──────┴────────────┴─────────┴───────────────┴─────────┴────────────┘

I tried both minmax and set index types.

I can see indexes are used during quering.

EXPLAIN indexes = 1
SELECT *
FROM plausible.events_v2
WHERE meta.value = ['xxx']

Query id: 175c741a-00ce-412b-8837-15eb3a5af85e

┌─explain─────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY)) │
│   ReadFromMergeTree (plausible.events_v2)   │
│   Indexes:                                  │
│     MinMax                                  │
│       Condition: true                       │
│       Parts: 61/61                          │
│       Granules: 218/218                     │
│     Partition                               │
│       Condition: true                       │
│       Parts: 61/61                          │
│       Granules: 218/218                     │
│     PrimaryKey                              │
│       Condition: true                       │
│       Parts: 61/61                          │
│       Granules: 218/218                     │
│     Skip                                    │
│       Name: meta_value_idx                  │
│       Description: minmax GRANULARITY 1     │
│       Parts: 61/61                          │
│       Granules: 216/218                     │
└─────────────────────────────────────────────┘

Or perhaps this idea is just out of place?
Thanks!

[ruslandoga]

👋 @tasiotas

EXPLAIN shows that meta_value_idx index wasn't particularly useful as it only filtered out two granules which means that 216 granules had rows with meta.value = ['xxx']. Not sure what can be done about it...

[tasiotas]

After some more digging, I noticed that I added a secondary index (Skip Index), that is not the same as Primary Key.
Probably this index didn't do much because of high cardinality of the meta.value and Skip works differently than PK.

I tried to recreate events_v2 table with Primary Key index on meta.value, but couldn't confirm if index was being used or if query got faster at all.

That led me to another question. Primary Key indexes are added only to name, site_id, timestamp and user_id.
How about more common filtered field like pathname? Looks like this is by design, just curious why so few indexes?

[rpmcoding]

@ruslandoga I am having problems with filtering by custom properties in the dashboard. It's a high traffic site. But I wonder if as mentioned above, if indexes are missing?