how you would set the index in this schema postgresql-CodePudding

I have a table like this:

CREATE TABLE shop_webhooks_logs (
  id UUID NOT NULL PRIMARY KEY,
  shop_id UUID not null REFERENCES shop(id),
  topic VARCHAR,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
)

My querie would be this:

SELECT id, shop_id FROM shop_webhook_logs WHERE id = $1 AND shop_id = $2

So my where statements has two select so should I make another index on shop_id like this ?

CREATE INDEX I_WEBHOOK_LOGS ON shop_webhook_logs(shop_id)

CodePudding user response：

If you have two conditions in your WHERE clause that are linked with AND, each result row must satisfy both conditions. That means that query execution is already very efficient if there is at most one table row that satisfied the first condition, and you have an index for that condition.

That is the case here: since id is the primary key, there is automatically a unique index on that column, so scanning that index will return at most one row. So there is no need to index the other condition; PostgreSQL will apply an additional filter that may eliminate the row found with the index scan if necessary.