Re: Reasonable amount of indices - Mailing list pgsql-performance

From Tom Lane
Subject Re: Reasonable amount of indices
Date
Msg-id 5736.1189120726@sss.pgh.pa.us
Whole thread Raw
In response to Re: Reasonable amount of indices  (Alvaro Herrera <alvherre@commandprompt.com>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: "Carlo Stonebanks"
Date:
Subject: Re: Performance on 8CPU's and 32GB of RAM
Next
From: "Carlo Stonebanks"
Date:
Subject: How planner decides left-anchored LIKE can use index