I've got a one to one relation from users to addresses table. Where one user can have one search address and one verified address.
I've got two indexes on addresses table :
- Index on state field
- Index on user_id
I'm trying to fetch addresses for certain users only, and those addresses whose state is other than
Here is my query:
SELECT users.id FROM "users" INNER JOIN addresses ON addresses.user_id = users.id and addresses.type = 'VerifiedAddress' WHERE ("users".deleted_at IS NULL) AND (users.id in (11144,10569,21519,783,15671,21726,17787,11665, 19579,12226,1324,9413,5461,20981,12906) and addresses.state != 'manual_verification')
Explain for the query above : http://explain.depesz.com/s/rTj
It takes 37 ms. Sometimes more depending on the number of users.
I think this is a good query, however our team needs to investigate around this, and I'm looking for some optimization tips. I mean I did the one field select, there is a index on user_id (addresses) and state (addresses).
Is there anything else I can do/try?
I found out that this query works much faster:
SELECT "addresses"."user_id" FROM "addresses" WHERE "addresses"."type" IN ('VerifiedAddress') AND (user_id in (9681,23824,23760,20098,962,14730,12294,9552,534, 553,5837,6768,6583,956,24179) and state != 'manual_verification')
Explain for this query: http://explain.depesz.com/s/nHrr