Sobes.tech
Junior — Senior

Determining the use of a composite index without a filter on the first field

livecode

Task condition

In the database schema, there is a table public.carts with a composite index covering the columns (customer_id, country, sku). It is necessary to determine whether the query planner will utilize this index if the query does not include a filter condition on the customer_id field.

Task conditions:

  • The composite index is declared as (customer_id, country, sku).
  • The SQL query does not have a WHERE customer_id = ... condition.

Table structure:

Table "public.carts"
+-------------+-----------------------------+-------------------------+
| Column      | Type                        | Modifiers               |
+-------------+-----------------------------+-------------------------+
| sku         | bigint                      | not null                |
| country     | country_enum                | not null                |
| customer_id | bigint                      | not null                |
| amount      | bigint                      | not null                |
| updated_at  | timestamp without time zone | default now() not null |
+-------------+-----------------------------+-------------------------+

Table "public.customer"
+--------+--------+----------+
| Column | Type   | Modifiers |
+--------+--------+----------+
| id     | bigint | not null |
| email  | text   | not null |
+--------+--------+----------+