X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 08F08D1B2D9 for ; Fri, 9 Jul 2004 12:18:55 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 41069-03 for ; Fri, 9 Jul 2004 15:18:51 +0000 (GMT) Received: from web51410.mail.yahoo.com (web51410.mail.yahoo.com [206.190.38.189]) by svr1.postgresql.org (Postfix) with SMTP id 53B21D1B2A4 for ; Fri, 9 Jul 2004 12:18:49 -0300 (ADT) Message-ID: <20040709151848.89738.qmail@web51410.mail.yahoo.com> Received: from [192.88.67.254] by web51410.mail.yahoo.com via HTTP; Fri, 09 Jul 2004 08:18:48 PDT Date: Fri, 9 Jul 2004 08:18:48 -0700 (PDT) From: Bill Chandler Subject: Re: Terrible performance after deleting/recreating indexes To: pgsql-performance@postgresql.org In-Reply-To: <40EDE368.90603@coretech.co.nz> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.9 tagged_above=0.0 required=5.0 tests=FROM_ENDS_IN_NUMS, LINES_OF_YELLING X-Spam-Level: X-Archive-Number: 200407/45 X-Sequence-Number: 7436 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 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