PostgreSQL is slow...HELP - Mailing list pgsql-performance

Hi,

I'm working on a project to make an application run on MySQL and PostgreSQL.
I find that PostgreSQL runs up to 10 times slower than MySQL.  For small records
it is not much problems.  But as the records grew (up to 12,000 records) the
difference is quite significant.  We are talking about 15s (MySQL) vs 111s
(PostgreSQL).  Someone suggest that my way of implementing PostgreSQL is not
efficient and someone out there might be able to help.

FYI, I running the application on ASP, XP Professional and Pentium 4 machine.

Below is the exact statement I used:

        strSQL = "CREATE TABLE temp1 SELECT accposd.item,items.name,Sum(accposd.qty)
as Quantity " & _
             ",accposd.loose,AVG(accposd.price) as price, Sum(accposd.amount) as
sum_amount  " & _
             ",Sum(accposd.cost) as sum_cost FROM  " & _
             "accposd left join items on accposd.item = items.fcc " & _
             "where accposd.date between '" &  varStartDate & "' AND '" &
varStopDate & "'"  & _
             " GROUP by accposd.item,items.name,accposd.loose ORDER by items.name"

Below is the information about the fields:

CREATE TABLE accposd (
  fcc double precision default NULL,
  date date default NULL,
  recvbch double precision default NULL,
  type int default NULL,
  item double precision default NULL,
  qty double precision default NULL,
  price double precision default NULL,
  amount double precision default NULL,
  discamt double precision default NULL,
  cost double precision default NULL,
  loose varchar(10) default NULL,
  discflg varchar(10) default NULL,
  hour smallint default NULL,
  min smallint default NULL,
  sec smallint default NULL,
  who varchar(50) default NULL,
  promoter varchar(50) default NULL,
  userID double precision default '0',
  batchno double precision default '0'
);


CREATE TABLE items (
  fcc serial,
  code varchar(20) default NULL,
  name varchar(40) default NULL,
  description varchar(255) default NULL,
  barcode varchar(15) default NULL,
  brand varchar(30) default NULL,
  sub_category double precision default NULL,
  schedule char(1) default NULL,
  price double precision default NULL,
  lprice double precision default NULL,
  avgcost double precision default NULL,
  gname varchar(40) default NULL,
  strength varchar(10) default NULL,
  packsize double precision default NULL,
  whspack varchar(15) default NULL,
  packing varchar(10) default NULL,
  lowstock double precision default NULL,
  lstockls double precision default NULL,
  orderqty double precision default NULL,
  creation date default NULL,
  shelfno varchar(8) default NULL,
  status char(1) default NULL,
  q_cust double precision default NULL,
  ql_cust double precision default NULL,
  qoh double precision default NULL,
  qohl double precision default NULL,
  poison double precision default NULL,
  candisc double precision default NULL,
  maxdisc double precision default NULL,
  chkdate date default NULL,
  chkby varchar(5) default NULL,
  isstock double precision default NULL,
  wprice double precision default '0',
  wlprice double precision default '0',
  PRIMARY KEY  (fcc)
);


I appreciate your advice.  Thank you.

Regards,
AZLIN.

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com