CODEWITHSUNDEEP
sqlpostgresql
0bj3ct
0bj3ct

Reputation: 1490

How does query with multiple where condition work in PostgreSQL?

I have a table account_config where I keep key-value configs for accounts with columns:

  1. id - pk
  2. account_id - fk
  3. key
  4. value

Table may have configs for thousands of accounts, but for each account it may have 10-20 configs max. I am using query:

select id, key, value from account_config t where t.account_id = ? and t.key = ?;

I already have index for account_id field, do I need another index for key field here? Will second filter (key = ?) apply to already filtered result set (account_id = ?) or it scans whole table?

Upvotes: 0

Views: 544

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95082

Indexes are used when only a small percentage of the table's rows get accessed and the index helps finding those rows quickly.

You say there are thousands of accounts in your table, each with 10 to 20 rows.

Let's say there are 3000 accounts and 45,000 rows in your table, then accessing data via an index on the account ID means with the index we access about 0,03 % of the rows to find the one row in question. That makes it extremely likely that the index will be used.

Of course, if there were an index on (account_id, key), that index would be preferred, as we would only have to read one row from the table which the index points to.

So, yes, your index should suffice for the query shown, but if you want to get this faster, then provide the two-column index.

Upvotes: 2

Related Questions