PostgreSQL over internet - Mailing list pgsql-performance

Hi All,

I think this is not new topic, I try to look for discussions about same subject I find something like

http://postgresql.1045698.n5.nabble.com/libpq-or-postgresql-performance-td5723158.html
http://www.postgresql.org/message-id/504DAC68.30005@wp.pl

I will talk to you about my experiment with same issue


I have table Materials created as:

postgre version: 9.1

create domain ID as integer;
^
create domain BIG_ID as bigint;
^
create domain SMALL_ID as smallint;
^
create domain GUID as varchar(32);
^
create domain GUIDSTRING as varchar(38);
^
create domain CURRENCY as numeric(18,4) default 0;
^
create domain AMOUNT as numeric(18,4) default 0;
^
create domain NAME as varchar(250);
^
create domain CODE as varchar(60);
^
create domain FOREIGNCODE as varchar(60);
^
create domain doc as varchar(40);
^
create domain NOTE as varchar;
^
create domain MEMO as varchar;
^
create domain BARCODE as varchar(40);
^
create domain IMAGE as bytea;
^
create domain OBJECT as varchar;
^
create domain CUR_PART_NAME as varchar(40);
^
create domain STRNAME as varchar(60);
^
create domain STRCODE as varchar(30);
^
create domain STRVALUE as varchar(250);

^
create domain SERIAL as varchar(60);

^
create domain D_DATE as DATE;

^
create domain D_TIME as TIME;

^
create domain D_DATETIME as timestamp;

^
create domain D_INTEGER as INTEGER;

^
create domain SHORT_BARCODE as varchar(20);

^
create domain XML as varchar;
^
create domain D_FLOAT as double precision default 0;

^
create domain D_DOUBLE as double precision default 0;

^
create domain KIND as smallint;

^
create domain D_LEVEL as smallint;

^
create domain D_SIGN as smallint;

^
create domain D_EXCHANGE as double precision;

^
create domain Rarefy as varchar(5);
^
create domain LONGNAME as varchar(250);
^
create table "Materials"
(
 "MatID" serial,
 "MatSite" SMALL_ID,
 "MatChanged" BIG_ID,
 "MatParent" D_INTEGER default 0 not null ,
 "MatIsBook" BOOLEAN default 0 not null ,
 "MatConsist" D_INTEGER,
 "MatClass" D_INTEGER,
 "MatName" Name default  not null ,
 "MatCode" Code default  not null ,
 "MatForeignCode" ForeignCode,
 "MatBarcode" BARCODE,
 "MatMaxLimit" AMOUNT,
 "MatMinLimit" AMOUNT,
 "MatAge" AMOUNT,
 "MatPack" D_INTEGER,
 "MatLevel" D_LEVEL default 0 not null ,
 "MatUnity" D_INTEGER,
 "MatDefUnity" D_INTEGER,
 "MatPackUnity" D_INTEGER,
 "MatPackSize" Amount,
 "MatDefWeight" AMOUNT,
 "MatApproxWeight" AMOUNT,
 "MatDiscount" AMOUNT,
 "MatNoDiscount" BOOLEAN,
 "MatQntRebate" AMOUNT,
 "MatIsQntRebate" BOOLEAN default 0 not null ,
 "MatDefGroup" D_INTEGER,
 "MatSpecification" Note,
 "MatBonus" AMOUNT,
 "MatBonusBase" AMOUNT,
 "MatRarefy" Rarefy,
 "MatDefQnt" AMOUNT,
 "MatIsUnbounded" BOOLEAN,
 "MatIsActive" BOOLEAN default 0 not null ,
 "MatIsSerial" BOOLEAN default 0 not null ,
 "MatIsPacked" BOOLEAN default 0 not null ,
 "MatIsBatched" BOOLEAN default 0 not null ,
 "MatIsWeb" BOOLEAN,
 "MatIsAssist" BOOLEAN default 0 not null ,
 "MatIsIgnored" BOOLEAN default 0 not null ,
 "MatAccVendor" D_INTEGER,
 "MatIsConsignment" BOOLEAN default 0 not null ,
 "MatIsVariety" BOOLEAN default 0 not null ,
 "MatIsMeal" BOOLEAN default 0 not null ,
 "MatIsMaintain" BOOLEAN default 0 not null ,
 "MatIsCategory" BOOLEAN default 0 not null ,
 "MatAccCustomer" D_INTEGER,
 "MatDepartment" ID,
 "MatChain" D_INTEGER default 0 not null ,
 "MatPhotoType" STRCODE default 0 not null ,
 "MatPhoto" LONGNAME,
 "MatCommission" AMOUNT,
 "MatFactor" AMOUNT,
 "MatTax" AMOUNT,
 "MatFees" AMOUNT,
 "MatExpiredThrough" D_INTEGER,
 "MatExpiredBy" D_INTEGER,
 "MatActivateDate" D_DATE default 'NOW' not null ,
 "MatCreatedDate" D_DATE default 'NOW' not null ,
 "MatModel" D_INTEGER,
 "MatNote" Note,
 "MatPoint" D_INTEGER,
 "MatOriginal" D_INTEGER,
 "MatRevision" BIG_ID default 0 not null
)
^
alter table "Materials" add constraint "pkMaterials" primary key ("MatID")
^
create  index "IdxMatIsBook" on "Materials" ("MatIsBook" )
^
create  index "IdxMatName" on "Materials" ("MatName" )
^
create unique index "IdxMatCode" on "Materials" ("MatCode" )
^
create unique index "IdxMatBarcode" on "Materials" ("MatBarcode" )
^
create  index "IdxMatIsWeb" on "Materials" ("MatIsWeb" )
^
create  index "IdxMatIsAssist" on "Materials" ("MatIsAssist" )
^
create  index "IdxMatIsConsignment" on "Materials" ("MatIsConsignment" )
^

with 31000 record

I connect to my server through ADSL connection 4Mbps

I try this query

select "MatID", "MatName", "MatCode"
from "Materials"
where "MatCode" ~* '^1101'
order by "MatCode"
limit 2

by wireshark I monitor TCP packets I found total data transmit/received 400B
I took about 2.5s to fetch results  why ??????

after trying every solution mentioned in previous messages (DNS, tcpip, postgresql.conf, ...) not found any improve,

I tried this one:

using Zebedee(http://www.winton.org.uk/zebedee/)
I build an IP tunnel between me and my data server (I used compression level 9)

surprisingly same query now took about 600 ms, "very impressive"

same thing with this query
select "MatID", "MatName", "MatCode", "MatParent" from "Materials"
from 48s down to  17s

all these tests done on same connection with same devices so same dns, tcp-ip, ....

now I am sure there is something wrong with libpq.