Thread: Reasonable amount of indices

Hi,
    I've a question about amount of indices.

    I explain my issue based on an example:
    Table which contains person information, one row per person.
    There will be lots of SELECTS doing search by special criteria,
e.g.: Age, Gender.

    Now there will be 4 User groups which will select on the table:
    Group 1) Always doing reads on specific continents.
    Group 2) Always doing reads in specific country.
    Group 3) Always doing reads in specific region within a country.
    Group 4) Always doing reads in specific city.

    I 'm indexing the the important attributes. Would be about 5 to 6
independent indexes.
    As there will be millions of rows, quite a lot of hits will be
returned, I guess
    it will generate big bitmaps to calculate the intersection of the
indices.

    Ok to prevent this from happening I'd wanted to create 4 Indexes per
attribute, with
    special predicate, so users which only query for a country don't
scan an index
    which indexed the entire globe:

    e.g ..
    CREATE index BlaBla_city on table tblusers(dtage) WHERE dtcity='London';
    CREATE index BlaBla_country on table tblusers(dtage) WHERE
dtcountry='uk';
    CREATE index BlaBla_continent on table tblusers(dtage) WHERE
dtcontinent='europe';
    etc.

    SELECT * FROM tblusers WHERE dtcontinent='europe' and age='23'
    would then postgres lead to use the special index made for europe.

    Now that I've 4 Indexes. an Insert or update will lead to some more
overhead,  but which would be ok.

    My Question now is: Is it wise to do so, and create hundreds or
maybe thousands of Indices
    which partition the table for the selections.

    Does postgres scale good on the selecton of indices or is the
predicate for indices not
    layed out for such a usage?

    (PS: Don't want partition with method postgres offers..)

thanks in advance,
patric

Patric wrote:

>    My Question now is: Is it wise to do so, and create hundreds or maybe
> thousands of Indices
>    which partition the table for the selections.

No, this is not helpful -- basically what you are doing is taking the
first level (the first couple of levels maybe) of the index out of it,
and charging the planner with the selection of which one is the best for
each query.

Of course, I am assuming you were simplifing your queries and don't
actually store the name of the continent etc on each on every row.
Because if you actually do that, then there's your first oportunity for
actual optimization (which happens to be a more normalized model), far
more effective than the partial indexes you are suggesting.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Patric wrote:
>> My Question now is: Is it wise to do so, and create hundreds or maybe
>> thousands of Indices
>> which partition the table for the selections.

> No, this is not helpful -- basically what you are doing is taking the
> first level (the first couple of levels maybe) of the index out of it,

Right --- you'd be *far* better off using a small number of multicolumn
indexes.  I wouldn't want to bet that the planner code scales
effectively to thousands of indexes, and even if it does, you're
throwing away any chance of using parameterized queries.

The update overhead is unpleasant to contemplate as well (or is this a
read-only table?)

            regards, tom lane