Re: Indices types, what to use. Btree, Hash, Gin or Gist

Yeah, but reply-all will still send private messages :O .. its strange
because this is the only mailing list that gmail behaves like this with.. it
must have to with how postgre sends messages out.
But anyways. Back to topic :)

Yeah, I think that a partial index is something that would be smart. The
problem is that sometimes I want to search for both genders (that is no
match for that column at all) and my index will then not be used on the
partial ones that includes that one which leads me to another thought.
Does this mean that the DB will hit all the blocks anyways? Does that mean
that as soon as I search for one column that is not a good index or not
indexed at all, the other indexed fields become useless since the DB will
have to go through all rows anyway?

Will I have to make a partial index and include the gender together with all
other fields each ?

Here is my indexes as of now (in one of my relations).

region index:'region_id_index' // Searched for or not
included in query

district index:'district_id_index' // Searched for
or not included in query
category index:'category_id_index' // Searched for
alone or not included in query
subCategory index:'sub_category_id_index' // Searched for or not
included in query

languageOfAd index:'language_of_ad_index' // Searched for
values 1,2 and sometimes don't search this field if all lang should be shown

name index:'name_index'
phoneNumber index:'phone_number_index' // Always with name, I
guess partial index could work here (often not searched for)
email index:'email_index' // Always
with name, I guess partial index could work here (often not searched for)
price index:'price_index' //
typeOfAd index:'type_of_ad_index' // 1,2,3,4
Always one of these in the query
rentingPeriod index:'renting_period_index' // if 3.4 then
1,7,30,365 if 1,2 then value is 0 (but not always used in query)
time index:'time_index' // Date with no timezone,
newest first in index, read about it ? ordered index..?
statusOfAd index:'status_of_ad_index' // Always in the
query, guess could be included in all indexes as partial

Thats only the index fields of this relation. Things that are searched for.
I will create a Gin index on description also but thats for the fulltext
that is coming together but is a bit of a struggle :)

/ Moe

On Sun, Feb 1, 2009 at 7:23 PM, Gregory Stark <stark(at)enterprisedb(dot)com>wrote:

> Mohamed <mohamed5432154321(at)gmail(dot)com> writes:
>
> > My Gmail(bloody gmail!) has been auto-replying to the last messager
> (Scott)
> > so I think we have been having a private discussion on this topic.
>
> There is an option in the Google Labs tab to make "Reply All" the default
> button -- of course then there's always a chance you'll make the opposite
> mistake which can be a lot worse.
>
> Earlier I suggested with a boolean column you could consider making it the
> condition on a partial index with some other key. For example you could
> have
>
> CREATE INDEX partial_age_male on tab(age) WHERE gender = 'M';
> CREATE INDEX partial_age_female on tab(age) WHERE gender = 'F';
>
> Then if you always search on age with gender the optimizer can use the
> index
> which only includes the records for the appropriate gender. It's basically
> a
> "free" index key column since it doesn't actually have to store the extra
> column.
>
> Note that in this example if you were to search on just age it wouldn't be
> able to use either of these indexes however. In theory it could use the
> indexes if you search on just gender but it would be unlikely to for all
> the
> same reasons as previously mentioned for regular indexes.
>
> --
> Gregory Stark
> EnterpriseDB http://www.enterprisedb.com
> Ask me about EnterpriseDB's RemoteDBA services!
>