Re: Alternatives to very large tables with many performance-killing indicies?

On Thu, Aug 16, 2012 at 2:54 PM, Wells Oliver <wellsoliver(at)gmail(dot)com> wrote:
> Hey folks, a question. We have a table that's getting large (6 million rows
> right now, but hey, no end in sight). It's wide-ish, too, 98 columns.
>
> The problem is that each of these columns needs to be searchable quickly at
> an application level, and I'm far too responsible an individual to put 98
> indexes on a table. Wondering what you folks have come across in terms of
> creative solutions that might be native to postgres. I can build something
> that indexes the data and caches it and runs separately from PG, but I
> wanted to exhaust all native options first.

I submit that you're far better off working with the users to see
which fields they really need indexes on, and especially which
combinations of fields with functional and / or partial indexes serve
them the best.

To start with you can create indexes willy nilly if you want and then
use the pg_stat*index tables to see which are or are not getting used
and start pruning them as time goes by. But keep an eye out for long
running queries with your logging and investigate to see what
specialized indexes might help the most for those queries. Often a
simple index on (a,b) where x is not null or something can give great
improvements over any bitmap hash scans of multiple indexes ever
could, especially on large data sets.