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