Re: Slow query after upgrades

On Dec 13, 2007, at 5:05 PM, Oliver Jowett wrote:

> 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, which unfortunately can be a bit tricky because psql doesn't
> use the query protocol in the same way as the JDBC driver.
>
> Can you write a little JDBC app that runs the query with EXPLAIN
> ANALYZE, setting parameters in the same way that your real app does,
> and dump out the resultset it gets back? (which should be the
> EXPLAIN results rather than the real data, IIRC)

QUERY PLAN=Sort (cost=289075.73..289147.11 rows=28554 width=20)
(actual time=141222.112..141222.294 rows=192 loops=1)
QUERY PLAN= Sort Key: "timestamp"
QUERY PLAN= -> Bitmap Heap Scan on point_history
(cost=1847.73..286962.53 rows=28554 width=20) (actual
time=140998.758..141221.691 rows=192 loops=1)
QUERY PLAN= Recheck Cond: (point_id = 21001)
QUERY PLAN= Filter: (("timestamp")::text >= '2007-12-11
23:20:57'::text)
QUERY PLAN= -> Bitmap Index Scan on point_history_pkey
(cost=0.00..1840.59 rows=85663 width=0) (actual time=122.393..122.393
rows=65262 loops=1)
QUERY PLAN= Index Cond: (point_id = 21001)
QUERY PLAN=Total runtime: 141222.791 ms

Cool, this makes it look like the problem is that the "timestamp"
parameter as a string instead of a timestamp. What do you think?

Tom