Обсуждение: combining multiple partial indices
Hi,
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?
Thanks,
Ram
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?
Thanks,
Ram
"Ram Ravichandran" <ramkaka@gmail.com> writes:
> My questions are regarding join optimization:
It's difficult to answer any of your questions without more information
than you've provided. Have you tried looking at EXPLAIN ANALYZE output
for your query?
regards, tom lane