Re: Jdbc/postgres performance

> -----Original Message-----
> From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-
> owner(at)postgresql(dot)org] On Behalf Of Merlin Moncure
> Sent: Tuesday, October 17, 2006 4:29 PM
> To: Rohit_Behl
> Cc: pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] Jdbc/postgres performance
>
> On 10/17/06, Rohit_Behl <Rohit_Behl(at)infosys(dot)com> wrote:
> > Select events.event_id, ctrl.real_name, events.tsds, events.value,
> events.lds, events.correction, ctrl.type, ctrl.freq from table events,
> iso_midw_control ctrl where events.obj_id = ctrl.obj_id and
> events.event_id > ?::bigint order by events.event_id limit ?
>
> unfortunately parameterized limit statements cause problems due to the
> fact the planner has a hard coded 'guess' of 10% of rows returned when
> the plan is generated. I mention this everyime query hints proposal
> comes up :-).

I'm not sure that this has anything to do with hints (yes, I know hints
are a popular topic as of late..) but from the 8.1 Manual:

"This is because when the statement is planned and the planner attempts
to determine the optimal query plan, the actual values of any parameters
specified in the statement are unavailable."

After a quick search on the JDBC list, it looks like there's some recent
discussion on the subject of how to give the planner better insight for
prepared statements (the subject is "Blind Message" if you're
looking...).

So, I'm off to go read there and perhaps join the jdbc mailing list too.

But, a more general postgres question. I assume if I want to turn
prepared statements off altogether (say I'm using a jdbc abstraction
layer that likes parameterized statements, and there's other benefits to
parameterizing other than just saving on db parse/plan) can I set
max_prepared_transactions to 0? Is there any other option outside of
JDBC? (I'll be moving my other questions over to the JDBC list...)

Also, others might be interested in the JDBC documentation, which is
separate from the main Postgres manual and can be found at:
http://jdbc.postgresql.org/documentation/

- Bucky

> best you can do is to try turning off seqscan and possibly bitmap scan
> when the plan is generated.
>