Thread: PostgreSQL is slow...HELP

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

On Wed, Sep 03, 2003 at 06:08:57AM -0700, Azlin Ghazali wrote:
> I find that PostgreSQL runs up to 10 times slower than MySQL.  For small records

Have you done any tuning on PostgreSQL?  Have you vacuumed, &c.?  All
the usual questions.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

> For small records
> it is not much problems.  But as the records grew (up to 12,000
> records) the
> difference is quite significant.

Although there are many tuning options, I'd suggest starting by making sure
you have an index (unique in cases where appropriate) on accposd.date
accposd.item, items.name, accposd.loose and items.name. Then do an
"analyze;" on the DB to make sure the database takes advantage of the
indexes where appropriate.

If this doesn't help, there are other options to pursue, but this is where I
would start.

-Nick

Azlin Ghazali <azlin_ghazali@yahoo.com> writes:
> Below is the exact statement I used:

That's not very informative.  Could we see the results of EXPLAIN ANALYZE
on that SELECT?  Also, what PG version are you running?

            regards, tom lane

On 3 Sep 2003 at 6:08, Azlin Ghazali wrote:

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

Are you running postgresql on windows? That's not an performance monster
exactly? Is it under cygwin?

BTW, did you do any performance tuning to postgresql?

HTH

Bye
 Shridhar

--
Vulcans do not approve of violence.        -- Spock, "Journey to Babel", stardate
3842.4