PostgreSQL not using index for statement with group by

From: Mark Starkman <mark(dot)starkman(at)activant(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: PostgreSQL not using index for statement with group by
Date: 2009-09-03 13:33:10
Message-ID: AE221A281AFE87459D0A20486DFBFD354584B256@exch4-yar-pen.northamerica.corporate-domain.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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). However, the
query doesn't perform well, and I'm not sure how to get it to return in
reasonable amount of time. Right now the query takes between 2 - 3
minutes to return. There are about 39 million rows in the table. Here
is all of the information that I have. Please let me know if you I have
done anything wrong or what needs to change.

Thanks,

Mark

Table Definition:

CREATE TABLE temp_inventory_fact

(

item_id integer NOT NULL,

date_id timestamp with time zone NOT NULL,

"CBL_Key" integer NOT NULL,

product_group_id integer NOT NULL,

supplier_id numeric(19) NOT NULL,

"Cost" numeric(19,9) NOT NULL,

qty_on_hand numeric(19,9) NOT NULL,

qty_allocated numeric(19,9) NOT NULL,

qty_backordered numeric(19,9) NOT NULL,

qty_on_po numeric(19,9) NOT NULL,

qty_in_transit numeric(19,9) NOT NULL,

qty_reserved numeric(19,9) NOT NULL,

nonstock_id boolean NOT NULL

)

WITH (

OIDS=FALSE

);

Query:

select product_group_id, SUM("Cost")

FROM temp_inventory_Fact

where product_group_id < 100

group by product_group_id

order by product_group_id

limit 50;

Indexes on table:

CREATE INDEX idx_temp_inventory_fact_product_cost ON temp_inventory_fact
(product_group_id, "Cost");

CREATE INDEX idx_temp_inventory_fact_product ON temp_inventory_fact
(product_group_id);

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tguru 2009-09-03 13:52:53 Re: SAAS and MySQL
Previous Message Joshua Tolley 2009-09-03 13:05:46 Re: pg_stat_activity.current_query explanation?