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);
