Skip to main content
    • Knowledge Base/
    Why Is My Primary Key Not Used

    Why is my primary key not used? How can I check?

    Covers a common reason why a primary key is not used in ordering and how we can confirm

    Checking your Primary Key​

    Users may see cases where their query is slower than expected, in the belief they are ordering or filtering by a primary key. In this article we show how users can confirm the key is used, highlighting common reasons its not.

    Create table​

    Consider the following simple table:

    Note how our ordering key includes toUnixTimestamp(timestamp) as the second entry.

    Populate data​

    Populate this table with 100m rows:

    Basic filtering​

    If we filter by code we can see the number of rows scanned in the output. - 49.15 thousand. Notice how this is a subset of the total 100m rows.

    Furthermore, we can confirm the use of the index with the EXPLAIN indexes=1 clause:

    Notice how the number of granules scanned 8012 is a fraction of the total 12209. The section higlighted below, confirms use of the primary key code.

    Granules are the unit of data processing in ClickHouse, with each typically holding 8192 rows. For further details on granules and how they are filtered we recommend reading this guide.

    Note

    Filtering on keys later in an ordering key will not be as efficient as filtering on those that are earlier in the tuple. For reasons why, see here

    Multi-key filtering​

    Suppose we filter, by code and timestamp:

    In this case both ordering keys are used to filter rows, resulting in the need to only read 87 granules.

    Using keys in sorting​

    ClickHouse can also exploit ordering keys for efficient sorting. Specifically,

    When the optimize_read_in_order setting is enabled (by default), the ClickHouse server uses the table index and reads the data in order of the ORDER BY key. This allows us to avoid reading all data in case of specified LIMIT. So, queries on big data with small limits are processed faster. See here and here for further details.

    This, however, requires alignment of the keys used.

    For example, consider this query:

    We can confirm that the optimization has not been exploited here by using EXPLAIN pipeline:

    The line MergeTreeSelect(pool: ReadPool, algorithm: Thread) here does not indicate the use of the optimization but rather a standard read. This is caused by our table ordering key using toUnixTimestamp(Timestamp) NOT timestamp. Rectifying this mismatch addresses the issue:

    · 6 min read