PostgreSQL not using index for statement with group by

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux