Re: looking for some real world performance numbers

From: Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com>
To: Guy Rouillier <guyr-ml1(at)burntmail(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: looking for some real world performance numbers
Date: 2007-10-22 05:37:09
Message-ID: 1193031429.1104.35.camel@neuromancer.home.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


On Mon, 2007-10-22 at 01:23 -0400, Guy Rouillier wrote:
> Ow Mun Heng wrote:
> > On Sun, 2007-10-21 at 22:54 -0400, Guy Rouillier wrote:
> >> The current app uses stored procedures
> >> for all inserts, and PG didn't do well with that approach; substituting
> >> embedded inserts fixed that problem. So PG can definitely "handle" very
> >
> >
> > Can you explain what is embedded inserts?
>
> Insert via embedded SQL insert statements in our Java code, as opposed
> to embedded SQL stored proc invocations, which in turn do the inserts.

AH.. so you sort of hard-code

insert into table values($x,$y,$z,$w)

instead of

execute sp_insert($x,$y,$z,$w)

> The existing code base used the latter approach, which didn't work well
> with PG. I suspect it has to do with PG's stored proc overload
> capability. The short of it is that *any* DBMS you use will have its
> own quirks that you become acquainted with and learn to work around.

Yeah..

I found out that PG doesn't like this statement.

where audit_key_dtime >= (select last_refreshed from denorm_log where tablename = 'zon')
and audit_key_dtime < (select last_refreshed + refresh_interval from denorm_log where tablename = 'zon')

Index Cond: ((audit_key_dtime >= $0) AND (audit_key_dtime < $1))

but prefers this

where audit_key_dtime >= '2007-08-08 18:00:00'
and audit_key_dtime < '2007-08-08 18:01:00'

Index Cond: ((audit_key_dtime >= '2007-08-08 18:00:00'::timestamp without time zone)
AND (audit_key_dtime < '2007-08-08 18:01:00'::timestamp without time zone))

even though they are of the same 1 min interval

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Kevin Hunter 2007-10-22 05:51:33 Re: SQL spec/implementation question: UPDATE
Previous Message Guy Rouillier 2007-10-22 05:23:34 Re: looking for some real world performance numbers