Re: Terrible performance after deleting/recreating indexes - Mailing list pgsql-performance

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@coretech.co.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