Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id AA3D49FA173 for ; Wed, 13 Dec 2006 15:44:56 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 58950-10 for ; Wed, 13 Dec 2006 15:44:42 -0400 (AST) X-Greylist: delayed 01:02:08.604259 by SQLgrey-1.7.4 Received: from mail.optiosoftware.com (mail.optio.com [192.216.93.10]) by postgresql.org (Postfix) with ESMTP id 791D89FA334 for ; Wed, 13 Dec 2006 15:44:44 -0400 (AST) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Subject: Re: strange query behavior Date: Wed, 13 Dec 2006 14:44:36 -0500 Message-ID: <47668A1334CDBF46927C1A0DFEB223D39F79FA@mail.optiosoftware.com> In-Reply-To: <10648.1166037392@sss.pgh.pa.us> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [PERFORM] strange query behavior Thread-Index: Acce6zYT6JChOP4BRNyTJ6DJduAuuAAA372w References: <47668A1334CDBF46927C1A0DFEB223D39F79A5@mail.optiosoftware.com> <10648.1166037392@sss.pgh.pa.us> From: "Tim Jones" To: "Tom Lane" Cc: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/338 X-Sequence-Number: 22394 The tables for theses queries are vacuumed and analyzed regularly. I just did an analyze to be sure and here are the results explain analyze select * from battery join observationresults on battery.batteryidentifier =3D observationresults.batteryidentifier left outer join observationcomment on observationresults.observationidentifier =3D observationcomment.observationidentifier left outer join batterycomment on battery.batteryidentifier=3Dbatterycomment.batteryidentifier where battery.batteryidentifier in (1177470, 1177469); =20 QUERY PLAN ------------------------------------------------------------------------ ------------------------------------------------------------------------ --------------- Nested Loop Left Join (cost=3D5.03..12553.00 rows=3D13 width=3D248) = (actual time=3D0.362..1.345 rows=3D30 loops=3D1) -> Nested Loop Left Join (cost=3D4.01..12424.13 rows=3D13 = width=3D208) (actual time=3D0.307..0.927 rows=3D30 loops=3D1) -> Nested Loop (cost=3D4.01..9410.49 rows=3D13 width=3D145) = (actual time=3D0.227..0.416 rows=3D30 loops=3D1) -> Bitmap Heap Scan on battery (cost=3D4.01..11.64 = rows=3D2 width=3D69) (actual time=3D0.135..0.138 rows=3D2 loops=3D1) Recheck Cond: ((batteryidentifier =3D 1177470) OR (batteryidentifier =3D 1177469)) -> BitmapOr (cost=3D4.01..4.01 rows=3D2 = width=3D0) (actual time=3D0.106..0.106 rows=3D0 loops=3D1) -> Bitmap Index Scan on ix_battery_id (cost=3D0.00..2.00 rows=3D1 width=3D0) (actual time=3D0.091..0.091 = rows=3D1 loops=3D1) Index Cond: (batteryidentifier =3D 1177470) -> Bitmap Index Scan on ix_battery_id (cost=3D0.00..2.00 rows=3D1 width=3D0) (actual time=3D0.011..0.011 = rows=3D1 loops=3D1) Index Cond: (batteryidentifier =3D 1177469) -> Index Scan using ix_obresults_bat on observationresults (cost=3D0.00..4682.40 rows=3D1362 width=3D76) = (actual time=3D0.047..0.091 rows=3D15 loops=3D2) Index Cond: ("outer".batteryidentifier =3D observationresults.batteryidentifier) -> Index Scan using ix_obcomment_obid on observationcomment (cost=3D0.00..227.73 rows=3D327 width=3D63) (actual time=3D0.013..0.013 = rows=3D0 loops=3D30) Index Cond: ("outer".observationidentifier =3D observationcomment.observationidentifier) -> Bitmap Heap Scan on batterycomment (cost=3D1.02..9.84 rows=3D6 width=3D40) (actual time=3D0.007..0.007 rows=3D0 loops=3D30) Recheck Cond: ("outer".batteryidentifier =3D batterycomment.batteryidentifier) -> Bitmap Index Scan on ix_batcomment (cost=3D0.00..1.02 = rows=3D6 width=3D0) (actual time=3D0.005..0.005 rows=3D0 loops=3D30) Index Cond: ("outer".batteryidentifier =3D batterycomment.batteryidentifier) Total runtime: 1.585 ms explain analyze select * from battery join observationresults on battery.batteryidentifier =3D observationresults.batteryidentifier left outer join observationcomment on observationresults.observationidentifier =3D observationcomment.observationidentifier left outer join batterycomment on battery.batteryidentifier=3Dbatterycomment.batteryidentifier where battery.batteryidentifier =3D 1177470; =20 QUERY PLAN ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------ Hash Left Join (cost=3D4733.62..269304.43 rows=3D1348 width=3D248) = (actual time=3D19275.506..19275.568 rows=3D9 loops=3D1) Hash Cond: ("outer".batteryidentifier =3D "inner".batteryidentifier) -> Merge Right Join (cost=3D4723.75..269287.81 rows=3D1348 = width=3D208) (actual time=3D19275.432..19275.473 rows=3D9 loops=3D1) Merge Cond: ("outer".observationidentifier =3D "inner".observationidentifier) -> Index Scan using ix_obcomment_obid on observationcomment (cost=3D0.00..245841.14 rows=3D7484253 width=3D63) (actual time=3D0.094..13403.300 rows=3D4361601 loops=3D1) -> Sort (cost=3D4723.75..4727.12 rows=3D1348 width=3D145) = (actual time=3D0.270..0.278 rows=3D9 loops=3D1) Sort Key: observationresults.observationidentifier -> Nested Loop (cost=3D0.00..4653.67 rows=3D1348 = width=3D145) (actual time=3D0.166..0.215 rows=3D9 loops=3D1) -> Index Scan using ix_battery_id on battery (cost=3D0.00..5.81 rows=3D1 width=3D69) (actual time=3D0.079..0.082 = rows=3D1 loops=3D1) Index Cond: (batteryidentifier =3D 1177470) -> Index Scan using ix_obresults_bat on observationresults (cost=3D0.00..4634.38 rows=3D1348 width=3D76) = (actual time=3D0.079..0.102 rows=3D9 loops=3D1) Index Cond: (1177470 =3D batteryidentifier) -> Hash (cost=3D9.85..9.85 rows=3D6 width=3D40) (actual = time=3D0.039..0.039 rows=3D0 loops=3D1) -> Bitmap Heap Scan on batterycomment (cost=3D1.02..9.85 = rows=3D6 width=3D40) (actual time=3D0.037..0.037 rows=3D0 loops=3D1) Recheck Cond: (batteryidentifier =3D 1177470) -> Bitmap Index Scan on ix_batcomment = (cost=3D0.00..1.02 rows=3D6 width=3D0) (actual time=3D0.032..0.032 rows=3D0 loops=3D1) Index Cond: (batteryidentifier =3D 1177470) Total runtime: 19275.838 ms (18 rows) Tim Jones Healthcare Project Manager Optio Software, Inc. (770) 576-3555 -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20 Sent: Wednesday, December 13, 2006 2:17 PM To: Tim Jones Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] strange query behavior=20 "Tim Jones" writes: > I have a query that uses an IN clause and it seems in perform great=20 > when there is more than two values in it but if there is only one it=20 > is really slow. Also if I change the query to use an =3D instead of IN = > in the case of only one value it is still slow. Please provide EXPLAIN ANALYZE output for both cases. > Possibly I need to reindex this > particular index? More likely you need to ANALYZE the table so that the planner has up-to-date stats ... regards, tom lane