长时间运行的查询PostgreSQL

我需要一些PostgreSQL的帮助。 我有一张32列的表格和200多万行的表格。 我有两个外键和一个索引。 当我查询表时，需要8+秒才能返回60,000行。 我已经尝试了我在网上找到的所有内容，包括Stack Overflow的提示，并且可以在查询中得到改善。 顺便说一句，查询是一个简单的选择一半的列没有分组或按顺序排列。 我运行了EXPLAIN ANALYZE命令，结果如下。 谁能告诉我我在这里失踪了什么？ 非常感谢你提前。

"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"

对不起，我忘了添加查询：

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/86075.html

上一篇: long running query PostgreSQL

下一篇: Improving query performance in PostgreSQL with nested not in