Re: PostgreSQL not using index for statement with group by

On Thu, Sep 3, 2009 at 7:33 AM, Mark Starkman<mark(dot)starkman(at)activant(dot)com> wrote:
> I am new to PostgreSQL and I am evaluating it for use as a data  warehouse.
> I am really struggling to get a simple query to perform well.  I have put
> the appropriate indexes on the table (at least they are appropriate from my
> use with several other RDBMS’s).

Ok, first things first. Pgsql isn't like most other dbms. It's
indexes do not contain visibility info, which means that if the db
were to use the indexes to look up entries in a table, it still has to
go back to the table to look those values up to see if they are
visible to the current transation.

So, if you're retrieving a decent percentage of the table, it's
cheaper to just hit the table. Note that this makes PostgreSQL poorly
suited for very wide tables.

Generally the trick to making large accesses run fast in pgsql is to
increase work_mem. But some queries just aren't efficient in pgsql
that can be efficient in other dbs.

Possibly clustering on product_group_id would help.