Re: Terrible performance after deleting/recreating indexes

Thanks for this tip. Turns out there is a difference.
I am using cursors (i.e. calling setFetchSize(5000) on
my Statement) in JDBC. So the SQL statement is
preceded by:

DECLARE JDBC_CURS_1 CURSOR FOR ...

which is then followed by the SQL statemnt.

This is followed by the separate statement:

FETCH FORWARD 5000 FROM JDBC_CURS_1;

Also, don't know if this is significant but there
are a few lines before both of these:

set datestyle to 'ISO'; select version(), case when
pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN'
else getdatabaseencoding() end;
set client_encoding = 'UNICODE
begin;

Only thing is, though, none of this is new. I was
using cursors before as well.

Here is the output from "EXPLAIN ANALYZE". Hope it
comes out readable:

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=50466.04..50470.45 rows=1765 width=114)
(actual time=87237.003..88235.011 rows=108311 loops=1)
Sort Key: iso_nep_data_update_events.lds
-> Merge Join (cost=49240.03..50370.85 rows=1765
width=114) (actual time=56658.356..65221.995
rows=108311 loops=1)
Merge Cond: ("outer".obj_id = "inner".obj_id)
-> Sort (cost=198.01..198.16 rows=61
width=65) (actual time=175.947..181.172 rows=3768
loops=1)
Sort Key: iso_nep_control.obj_id
-> Seq Scan on iso_nep_control
(cost=0.00..196.20 rows=61 width=65) (actual
time=0.056..108.151 rows=3768 loops=1)
Filter: ((real_name)::text ~~
'NEPOOL%REAL%'::text)
-> Sort (cost=49042.02..49598.46
rows=222573 width=69) (actual
time=56482.073..58642.901 rows=216528 loops=1)
Sort Key:
iso_nep_data_update_events.obj_id
-> Index Scan using iso_nep_due_idx1
on iso_nep_data_update_events (cost=0.00..7183.18
rows=222573 width=69) (actual time=0.179..11739.104
rows=216671 loops=1)
Index Cond: (lds >
1088554754000::numeric)
Total runtime: 88643.330 ms
(13 rows)

Here is the actual query:

select iso_nep_DATA_UPDATE_EVENTS.lds,
iso_nep_DATA_UPDATE_EVENTS.tsds,
iso_nep_DATA_UPDATE_EVENTS.value,
iso_nep_DATA_UPDATE_EVENTS.correction,
iso_nep_DATA_UPDATE_EVENTS.delta_lds_tsds,
iso_nep_CONTROL.real_name,
iso_nep_CONTROL.freq,
iso_nep_CONTROL.type from
iso_nep_DATA_UPDATE_EVENTS, iso_nep_CONTROL
where iso_nep_CONTROL.real_name like
'NEPOOL%REAL%' escape '/' and
iso_nep_DATA_UPDATE_EVENTS.obj_id =
iso_nep_CONTROL.obj_id and
iso_nep_DATA_UPDATE_EVENTS.lds > 1088554754000
order by lds;

Two tables: iso_nep_data_update_events and
iso_nep_control. Basically getting all columns from
both tables. Joining the tables on obj_id = obj_id.
Have unique indexes on iso_nep_control.obj_id
(clustered) and iso_nep_control.real_name. Have
non-unique indexes on iso_nep_data_update_events.lds
and iso_nep_data_update_events.obj_id.

thanks,

Bill

--- Mark Kirkwood <markir(at)coretech(dot)co(dot)nz> wrote:
> That is interesting - both psql and JDBC merely
> submit statements for
> the backend to process, so generally you would
> expect no difference in
> execution plan or performance.
>
> It might be worth setting "log_statement=true" in
> postgresql.conf and
> checking that you are executing *exactly* the same
> statement in both
> JDBC and psql.
>
> regards
>
> Mark
>
> P.s : lets see the output from EXPLAIN ANALYZE :-)
>
> Bill Chandler wrote:
>
> >Thanks for the advice.
> >
> >On further review it appears I am only getting this
>
> >performance degradation when I run the command via
> >a JDBC app. If I do the exact same query from
> >psql, the performance is fine. I've tried both the
> >JDBC2 and JDBC3 jars. Same results.
> >
> >
> >
> >
> >
> >
> >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail