combining multiple partial indices

------=_Part_16472_31121433.1213387983704
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

------=_Part_16472_31121433.1213387983704
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi,<br><br>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:<br><br>1.&nbsp; select * from friends where<br>2.&nbsp; age &gt;= ?
and<br>3.&nbsp; country = ? and <br>
4.&nbsp; status = ? and<br>5.&nbsp; height &gt; ? and height &lt; ?
and<br>6.&nbsp; id in (?) and <br>7.&nbsp; id not in (select id from
blocked_friends )<br>8.&nbsp; limit 1<br><br>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. <br>
I also created a partial index: CREATE INDEX friends_search_index_us ON
friends (age,height) WHERE country = 0 and status = 1<br><br>My questions
are regarding join optimization:<br><br>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<br>
outer query? Or,&nbsp; will it make a huge nasty outer-join like MySQL?
Or, will the optimizer choose different strategies?<br><br>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 <br>
result set from line 7 is huge (around 3,000,000). Will constraints 6 and
7 be combined before hitting the database?
<br><br><br>Thanks,<br>Ram<br><br><br>

------=_Part_16472_31121433.1213387983704--