Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 78627475E2B for ; Tue, 15 Oct 2002 04:12:08 -0400 (EDT) Received: from relay.icomedias.com (relay.icomedias.com [62.99.232.66]) by postgresql.org (Postfix) with ESMTP id 351B6475E1F for ; Tue, 15 Oct 2002 04:12:07 -0400 (EDT) Received: from 10.192.17.128 ([10.192.17.128]) by relay.icomedias.com (8.12.5/8.12.5) with ESMTP id g9F8C4cj024426; Tue, 15 Oct 2002 10:12:06 +0200 Content-Type: text/plain; charset="iso-8859-1" From: Mario Weilguni To: pginfo Subject: Re: query optimization Date: Tue, 15 Oct 2002 10:12:13 +0200 User-Agent: KMail/1.4.3 References: <3DABBE6F.67930574@t1.unisoftbg.com> In-Reply-To: <3DABBE6F.67930574@t1.unisoftbg.com> Cc: pgsql-general@postgresql.org MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200210151012.13724.mweilguni@sime.com> avpresult: 0, ok, ok X-Scanned-By: MIMEDefang 2.16 (www . roaringpenguin . com / mimedefang) X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/665 X-Sequence-Number: 31663 > Sort (cost=3D37.44..37.44 rows=3D1 width=3D1118) (actual > time=3D12140.96..12141.02 rows=3D48 loops=3D1) > -> Nested Loop (cost=3D0.00..37.43 rows=3D1 width=3D1118) (actual > time=3D115.02..12138.57 rows=3D48 loops=3D1) > -> Nested Loop (cost=3D0.00..33.42 rows=3D1 width=3D886) (actual > time=3D0.58..20.77 rows=3D48 loops=3D1) > -> Index Scan using i_sklad_ids_doc on a_sklad n > (cost=3D0.00..28.58 rows=3D1 width=3D760) (actual time=3D0.36..8.59 rows= =3D48 > loops=3D1) > -> Index Scan using a_location_pkey on a_location sk > (cost=3D0.00..4.82 rows=3D1 width=3D126) (actual time=3D0.09..0.12 rows= =3D1 > loops=3D48) > -> Seq Scan on a_nomen nom (cost=3D0.00..3.45 rows=3D45 width= =3D232) > (actual time=3D0.03..165.45 rows=3D6702 loops=3D48) > Total runtime: 12142.07 msec It seems the query planner is completly wrong here, look on the line=20 Seq Scan on a_nomen nom (cost=3D0.00..3.45 rows=3D45 width=3D232) (actual = time=3D0.03..165.45 rows=3D6702 loops=3D48) This means the planner expects 45 return rows (guessed from statistics), bu= t actually gets 6702 rows.=20 Do "VACUUM ANALYZE a_nomen" and try your query again.=20 If it fails: Do you have a unique index on a_nomen(ids)? Regards, Mario Weilguni > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html