I have a very large table (in the order of millions of rows). The most expensive search I do on that table is something like this:
1. select * from friends where 2. age >= ? and 3. country = ? and 4. status = ? and 5. height > ? and height < ? and 6. id in (?) and 7. id not in (select id from blocked_friends ) 8. limit 1
id is the primary key. The country field is an enum of 5 different values, and the only time I run this query is when status = 1 and country = 0. I also created a partial index: CREATE INDEX friends_search_index_us ON friends (age,height) WHERE country = 0 and status = 1
My questions are regarding join optimization:
1. If the subqueries are uncorrelated (line 7) will postgres run the inner query first (i.e. select id from blocked_friends ) and then use the results with the larger outer query? Or, will it make a huge nasty outer-join like MySQL? Or, will the optimizer choose different strategies?
2. The partial index only indexes age and height. This result set from the index is small (Say around 2000). The result from line 6 is also small (say around 3000) but the result set from line 7 is huge (around 3,000,000). Will constraints 6 and 7 be combined before hitting the database?