Re: looking for some real world performance numbers

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