Re: Indices types, what to use. Btree, Hash, Gin or Gist - Mailing list pgsql-general
| From | Mohamed |
|---|---|
| Subject | Re: Indices types, what to use. Btree, Hash, Gin or Gist |
| Date | |
| Msg-id | 861fed220902011111k2717a023nb327fd26ea1e2f29@mail.gmail.com Whole thread Raw |
| In response to | Re: Indices types, what to use. Btree, Hash, Gin or Gist (Gregory Stark <stark@enterprisedb.com>) |
| List | pgsql-general |
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@enterprisedb.com> wrote:
> My Gmail(bloody gmail!) has been auto-replying to the last messager (Scott)There is an option in the Google Labs tab to make "Reply All" the default
> so I think we have been having a private discussion on this topic.
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!
pgsql-general by date: