POSTGRE查询满

sam10000 2020-12-02 06:14:14

各位：
我发现POSTGRE数据库查询慢，比SQLSERVER多两倍，这是怎么回事？
我用的是C#，
新建数据库：
string connectionString = "Server=localhost;Port=5432;Username=postgres;Password=12345";
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
conn.Open();
string sql = "CREATE DATABASE " + Name + " WITH OWNER = postgres ENCODING='UTF8' LC_COLLATE = 'Chinese (Simplified)_China.936' LC_CTYPE = 'Chinese (Simplified)_China.936' TABLESPACE = pg_default CONNECTION LIMIT = -1;";
NpgsqlCommand objCommand = new NpgsqlCommand(sql, conn);
objCommand.ExecuteNonQuery();
conn.Close();
conn.Dispose();
新建表：
string connectionString = "Server=localhost;Port=5432;Username=postgres;Password=12345;Database=" + DataBaseName + ";";
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
conn.Open();
string sql = "CREATE TABLE " + TableName +
"( 时间 timestamp without time zone, TempratureA integer, PowerA integer,ACVoltageA integer,CoilVoltageA integer,DCCurrentA integer,HzA integer,DCVoltageA integer,StoveWaterFlowA integer,MachingWaterFlowA integer,CapVoltageA integer,INVCurrentA integer,TPreheatSA integer,TColdSA integer,RESA1 integer,RESA2 integer) WITH(OIDS = FALSE);";

NpgsqlCommand objCommand = new NpgsqlCommand(sql, conn);
objCommand.ExecuteNonQuery();

sql = "CREATE INDEX x" + TableName + " ON " + TableName + " USING btree(时间 ASC NULLS LAST);";
objCommand = new NpgsqlCommand(sql, conn);
objCommand.ExecuteNonQuery();

sql = "ALTER TABLE " + TableName + " CLUSTER ON x" + TableName + ";";
objCommand = new NpgsqlCommand(sql, conn);
objCommand.ExecuteNonQuery();

conn.Close();
查询：

string connectionString = "Server=localhost;Port=5432;Username=postgres;Password=12345;Database=" + DataBaseName + ";";
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
conn.Open();

DateTime StTime, EdTime;
StTime = DateTime.Parse(StartTime);
EdTime = DateTime.Parse(EndTime);

string sql = "SELECT * FROM " + TableName + " where 时间> '" + StTime + "' and 时间<'" + EdTime + "';";
NpgsqlCommand objCommand = new NpgsqlCommand(sql, conn);

da = new NpgsqlDataAdapter(objCommand);
ds = new DataSet();
da.Fill(ds, "ds");
objCommand.Parameters.Clear();

objCommand.Dispose();
ds.Dispose();

conn.Close();
conn.Dispose();
每次查询两万多条记录，为什么比较慢，是SQLSERVER的三倍。
大师们能帮忙看一下吗？

...全文