On Thu, 2007-10-11 at 16:04 -0400, Merlin Moncure wrote: > On 10/11/07, Andrew - Supernews <andrew+nonews@xxxxxxxxxxxxx> wrote: > > On 2007-10-10, Theo Kramer <theo@xxxxxxxxxxx> wrote: > > > When doing a 'manual' prepare and explain analyze I get the following > > > > > > rascal=# prepare cq (char(12), smallint, integer) as SELECT oid, > > > calllog_mainteng, calllog_phase, calllog_self FROM calllog > > > WHERE calllog_mainteng = $1 > > > AND calllog_phase = $2 > > > AND calllog_self < $3 > > > OR calllog_mainteng = $1 > > > AND calllog_phase < $2 > > > ORDER BY calllog_mainteng DESC, > > > calllog_phase DESC, > > > calllog_self DESC limit 25; > > > PREPARE > > > > When you do this from the application, are you passing it 3 parameters, > > or 5? The plan is clearly taking advantage of the fact that the two > > occurrences of $1 and $2 are known to be the same value; if your app is > > using some interface that uses ? placeholders rather than numbered > > parameters, then the planner will not be able to make this assumption. > > > > Also, from the application, is the LIMIT 25 passed as a constant or is that > > also a parameter? > > also, this looks a bit like a drilldown query, which is ordering the > table on 2+ fields. if that's the case, row wise comparison is a > better and faster approach. Agreed - and having a look into that. > is this a converted cobol app? :) - on the right track - it is a conversion from an isam based package where I have changed the backed to PostgreSQL. Unfortunately there is way too much legacy design and application code to change things at a higher level. -- Regards Theo ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
