long running query PostgreSQL

I need some help with PostgreSQL. I have a table with 32 Columns, and 2 million plus rows. I have two foreign keys and one index. When I query the table it takes 8+ seconds to return 60,000+ rows. I have tried everything that I have found on the web including tips on Stack Overflow and can get no improvement on the query. BTW, the query is a simple select of half of the columns with no group by or order by statement. I ran the EXPLAIN ANALYZE command and the results are below. Can anyone tell me what I am missing here? Thank you very much in advance.

"Seq Scan on mydatatable a  (cost=0.00..336085.02 rows=40944 width=156) (actual time=1244.135..7139.903 rows=60576 loops=1)"
"  Filter: ((customer_id = 123) AND (vip_period_id = 555))"
"  Rows Removed by Filter: 2070509"
"  SubPlan 1"
"    ->  Limit  (cost=0.00..5.05 rows=1 width=11) (actual time=0.065..0.068 rows=1 loops=60576)"
"          ->  Seq Scan on countries b  (cost=0.00..5.05 rows=1 width=11) (actual time=0.056..0.056 rows=1 loops=60576)"
"                Filter: ((a.endcustomercountry)::text = (iso3)::text)"
"                Rows Removed by Filter: 231"
"Total runtime: 7326.915 ms"

Sorry, I forgot to add the query:

select a.id, a.color, a.grade, round((a.allocationpercentage)::numeric, 2) AS percent, 
    a.product, a.customer, a.actualshipmentdate, 
    round((a.booknet)::numeric, 2) AS allocated, 
    round((a.cost)::numeric, 2) AS cost, 
    round((a.rebate)::numeric, 2) AS rebate, 
    COALESCE(round((a.forecast)::numeric, 2), 0.00) AS forecast, 
    round((a.estimatedforecast)::numeric, 2) AS estforecast, 
    c.countryname, a.transactiondate, a.sellerpo, a.sopo, a.quantity 
    from mydatatable as a left outer join countries as c on a.endcustomercountry = c.iso3
    where a.customer_id = 123 AND a.vip_period_id = 555;

CREATE TABLE mydatatable
(
  id serial NOT NULL,
  sopo integer,
  transactiondate date,
  partner character varying(110),
  color character varying(100),
  grade character varying(100),
  allocationpercentage double precision,
  technologytier character varying(250),
  product character varying(100),
  promotionname character varying(100),
  dealiddartid integer,
  dartdescriptor character varying(250),
  quantity integer,
  routetomarket character varying(100),
  endcustomername character varying(200),
  endcustomerparentname character varying(200),
  endcustomercountry character varying(20),
  shipmentflag character varying(20),
  actualshipmentdate date,
  oipsipflag character varying(250),
  reasondescription character varying(200),
  totalbooknet double precision,
  booknet double precision,
  rebate double precision,
  customer_id integer,
  entry_datetime timestamp without time zone,
  forecast double precision,
  sellerpo character varying(111),
  vip_period_id integer DEFAULT 1,
  pollersessionid integer,
  estimatedcost double precision DEFAULT 0,
  estimatedforecast double precision DEFAULT 0,
  CONSTRAINT mydatatable_pkey PRIMARY KEY (id),
  CONSTRAINT mydatatable_customer_id_fkey FOREIGN KEY (customer_id)
      REFERENCES customer (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT mydatatable_vip_period_id_fkey FOREIGN KEY (vip_period_id)
      REFERENCES vipperiod (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE mydatatable
  OWNER TO myowner;

CREATE INDEX idx_mydatatable
  ON mydatatable
  USING btree
  (customer_id, vip_period_id);

{
"Hash Left Join  (cost=11101.47..81592.05 rows=39533 width=164) (actual time=73.286..434.876 rows=60576 loops=1)"
"  Hash Cond: ((a.endcustomercountry)::text = (c.iso3)::text)"
"  ->  Bitmap Heap Scan on mydatatable a  (cost=11093.98..79266.39 rows=39299 width=157) (actual time=73.174..104.951 rows=60576 loops=1)"
"        Recheck Cond: ((customer_id = 123) AND (vip_period_id = 555))"
"        ->  BitmapAnd  (cost=11093.98..11093.98 rows=39299 width=0) (actual time=72.700..72.700 rows=0 loops=1)"
"              ->  Bitmap Index Scan on idx_cid_mydatatable  (cost=0.00..4038.51 rows=218411 width=0) (actual time=41.377..41.377 rows=221793 loops=1)"
"                    Index Cond: (customer_id = 123)"
"              ->  Bitmap Index Scan on idx_vip_mydatatable  (cost=0.00..7035.57 rows=380685 width=0) (actual time=28.829..28.829 rows=372877 loops=1)"
"                    Index Cond: (vip_period_id = 555)"
"  ->  Hash  (cost=4.44..4.44 rows=244 width=15) (actual time=0.075..0.075 rows=235 loops=1)"
"        Buckets: 1024  Batches: 1  Memory Usage: 11kB"
"        ->  Seq Scan on countries c  (cost=0.00..4.44 rows=244 width=15) (actual time=0.011..0.037 rows=244 loops=1)"
"Total runtime: 436.599 ms"
}

链接地址: http://www.djcxy.com/p/86076.html

上一篇: Postgresql在索引列上查询非常慢

下一篇: 长时间运行的查询PostgreSQL