Oliver Jowett <oliver@opencloud.com> writes: > Tom Duffey wrote: >> This makes a huge difference, thanks. However, is there anything I can >> do to help improve the performance of that query when using a newer >> protocol? > We need to work out what is going wrong under the newer protocol first, It looks pretty obvious from the peanut gallery: in the parameterized query, the planner daren't choose an indexscan, because for the vast majority of the possible values of the parameter an indexscan would suck. Tom's apparently only interested in the case where the parameter is close to the end of the range, so that only a few rows need to be retrieved. In this case the indexscan wins big, but the planner can't count on that. A possible hack is to put a reasonably small LIMIT on the query. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate

Oliver Jowett <oliver@opencloud.com> writes: > The JDBC driver is using an unnamed statement in this case, shouldn't > the planner end up using index selectivity estimates based on the actual > parameter values? If he's using a recent enough backend, it should. > From the explain output that just came through it looks like a type > mismatch problem on the timestamp parameter. Yeah, no question. Implicit casts to text strike again :-(. He was probably getting the wrong answers, not only a slow query. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings

Tom Duffey <tduffey@techbydesign.com> writes: > On Dec 13, 2007, at 7:03 PM, Tom Lane wrote: >> Yeah, no question. Implicit casts to text strike again :-(. He was >> probably getting the wrong answers, not only a slow query. > What do you think about adding type information to the log? Something > like: > DETAIL: parameters: $1 = '21001'::integer, $2 = '2007-12-11 > 22:22:45'::text > or similar would have revealed the source of my problem earlier. 8.3 will already reveal your problem quicker ;-) regression=# select now() < '2007-12-14 11:15:02.284223-05'::text; ERROR: operator does not exist: timestamp with time zone < text LINE 1: select now() < '2007-12-14 11:15:02.284223-05'::text; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend