mysql postgresql 性能_Postgresql从表性能与MySql中选择*

我有一个

MySQL数据库,我正在移植到Postgresql(因为GIS功能).

许多表都有数十万行,因此我需要牢记性能.

我的问题是Postgresql看起来非常缓慢……

例如,如果我在MysqL数据库中的特定表上执行简单的SELECT * FROM [table],假设有一个包含113,000行的表,则查询大约需要2秒才能返回数据.

在Postgresql中,同一个表上完全相同的查询需要大约10秒钟.

同样,我有另一个表,行数较少(88,000),而且更糟糕！ MysqL需要1.3秒,Postgresql需要30秒！

这是我对Postgresql的期望,还是我可以做些什么来让它变得更好？

我的操作系统是XP,我正在运行一个带有3GB内存的2.7ghz双代码.

MysqL数据库是5.1版,运行库存标准.

Postgresql数据库是版本8.4,我编辑了如下配置：

shared_buffers = 128MB

effective_cache_size = 512MB

谢谢！

这是第二个表的结构,有大约88,000行：

CREATE TABLE nodelink

(

nodelinkid serial NOT NULL,workid integer NOT NULL,modifiedbyid integer,tabulardatasetid integer,fromnodeid integer,tonodeid integer,materialid integer,componentsubtypeid integer,crosssectionid integer,"name" character varying(64) NOT NULL,description character varying(256) NOT NULL,modifiedbyname character varying(64) NOT NULL,-- Contains the values from the old engine's ModifiedBy field,since they don't link with any user

linkdiameter double precision NOT NULL DEFAULT 0,-- The diameter of the Link

height double precision NOT NULL,width double precision NOT NULL,length double precision NOT NULL,roughness double precision NOT NULL,upstreaminvert double precision NOT NULL,upstreamloss double precision NOT NULL,downstreaminvert double precision NOT NULL,downstreamloss double precision NOT NULL,averageloss double precision NOT NULL,pressuremain double precision NOT NULL,flowtogauge double precision NOT NULL,cctvgrade double precision NOT NULL,installdate timestamp without time zone NOT NULL,whencreated timestamp without time zone NOT NULL,whenmodified timestamp without time zone NOT NULL,ismodelled boolean NOT NULL,isopen boolean NOT NULL,shapenative geometry,shapewgs84 geometry,CONSTRAINT nodelink_pk PRIMARY KEY (nodelinkid),CONSTRAINT componentsubtype_nodelink_fk FOREIGN KEY (componentsubtypeid)

REFERENCES componentsubtype (componentsubtypeid) MATCH SIMPLE

ON UPDATE NO ACTION ON DELETE NO ACTION,CONSTRAINT crosssection_nodelink_fk FOREIGN KEY (crosssectionid)

REFERENCES crosssection (crosssectionid) MATCH SIMPLE

ON UPDATE NO ACTION ON DELETE NO ACTION,CONSTRAINT fromnode_nodelink_fk FOREIGN KEY (fromnodeid)

REFERENCES node (nodeid) MATCH SIMPLE

ON UPDATE NO ACTION ON DELETE NO ACTION,CONSTRAINT material_nodelink_fk FOREIGN KEY (materialid)

REFERENCES material (materialid) MATCH SIMPLE

ON UPDATE NO ACTION ON DELETE NO ACTION,CONSTRAINT tabulardataset_nodelink_fk FOREIGN KEY (tabulardatasetid)

REFERENCES tabulardataset (tabulardatasetid) MATCH SIMPLE

ON UPDATE NO ACTION ON DELETE NO ACTION,CONSTRAINT tonode_nodelink_fk FOREIGN KEY (tonodeid)

REFERENCES node (nodeid) MATCH SIMPLE

ON UPDATE NO ACTION ON DELETE NO ACTION,CONSTRAINT user_nodelink_fk FOREIGN KEY (modifiedbyid)

REFERENCES awtuser (userid) MATCH SIMPLE

ON UPDATE NO ACTION ON DELETE NO ACTION,CONSTRAINT work_modellink_fk FOREIGN KEY (workid)

REFERENCES "work" (workid) MATCH SIMPLE

ON UPDATE NO ACTION ON DELETE NO ACTION

)

WITH (

OIDS=FALSE

);

ALTER TABLE nodelink OWNER TO postgres;

COMMENT ON TABLE nodelink IS 'Contains all of the data that describes a line between any two nodes.';

COMMENT ON COLUMN nodelink.modifiedbyname IS 'Contains the values from the old engine''s ModifiedBy field,since they don''t link with any user';

COMMENT ON COLUMN nodelink.linkdiameter IS 'The diameter of the Link';

我用select语句玩了一下.如果我只是“从NodeLink中选择NodeLinkID”,则查询速度要快得多 – 不到一秒钟即可获得88,000行.

如果我执行“选择NodeLinkID,从NodeLink中删除”,则查询需要很长时间 – 大约8秒.

这是否能说明我做错了什么？

更多发现：

CREATE INDEX nodelink_lengthIDX on

nodelink(length);

analyze nodelink

— Executing query: SELECT * FROM nodelink WHERE Length BETWEEN 0 AND

3.983 Total query runtime: 3109 ms. 10000 rows retrieved.

— Executing query: SELECT nodelinkID FROM nodelink WHERE Length BETWEEN 0

AND 3.983 Total query runtime: 125

ms. 10000 rows retrieved.

在MysqL中,第一个查询在大约120毫秒内完成,第二个查询在大约0.02毫秒内完成.

问题解决：

好吧,伙计们,似乎这都是茶杯里的风暴……

mjy是对的：

“How did you measure those timings – in your application or the respective command line interfaces?“

为了测试这个理论,我整理了一个简单的控制台应用程序,它在MysqL数据库和PGsql数据库上运行相同的查询.这是输出：

Running MysqL query: [SELECT * FROM l_model_ldata]

MysqL duration = [2.296875]

Running PGsql query: [SELECT * FROM nodelink]

PGsql duration = [2.875]

所以结果是可比的.

似乎postgresql附带的pgadmin工具非常慢.

感谢大家的建议和帮助！

mjy,如果你想发一个答案,我可以把它作为正确的答案,以备将来参考.