Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 2924B9FBBA4 for ; Thu, 5 Apr 2007 02:36:52 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 28591-05-2 for ; Thu, 5 Apr 2007 02:36:40 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.186]) by postgresql.org (Postfix) with ESMTP id 2A9CA9FBB72 for ; Thu, 5 Apr 2007 02:36:41 -0300 (ADT) Received: from [194.25.154.138] (helo=webserv.wug.de) by mrelayeu.kundenserver.de (node=mrelayeu6) with ESMTP (Nemesis), id 0ML29c-1HZKeF3HHo-0003T3; Thu, 05 Apr 2007 07:36:40 +0200 Received: from kretschmer by webserv.wug.de with local (Exim 3.36 #1) id 1HZKeE-0006fL-00 for pgsql-sql@postgresql.org; Thu, 05 Apr 2007 07:36:38 +0200 Date: Thu, 5 Apr 2007 07:36:38 +0200 From: "A. Kretschmer" To: pgsql-sql@postgresql.org Subject: Re: slow query Message-ID: <20070405053637.GC24814@a-kretschmer.de> References: <7539aebb0704042017l70d83d53u9c2e5105620a3fa7@mail.gmail.com> <20070405051127.GA24814@a-kretschmer.de> <19157.1175750845@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <19157.1175750845@sss.pgh.pa.us> X-OS: Debian/GNU Linux - weil ich es mir Wert bin! X-Message-Flag: "Windows" is not the answer. "Windows" is the question and the answer is "no"! User-Agent: Mutt/1.5.9i X-Provags-ID: V01U2FsdGVkX1++eLNtjT+MBU0sCPMKf6drnOe2u/z/heNm1+g g03SQTqAHUYC0FAH6+aMWxB9x/I6cpzCjbAmo8pleliJz2vReN k6h9gEwuvoyfLOm4dFmbldheeecAXxz X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/66 X-Sequence-Number: 28066 am Thu, dem 05.04.2007, um 1:27:25 -0400 mailte Tom Lane folgendes: > "A. Kretschmer" writes: > > am Wed, dem 04.04.2007, um 23:17:54 -0400 mailte Sumeet folgendes: > >> sm=> explain analyze select * from ma limit 10; > >> QUERY > >> PLAN > >> ------------------------------------------------------------------------------------------------------------------------------------- > >> Limit (cost=0.00..0.45 rows=10 width=76) (actual time=21985.292..22204.340 > >> rows=10 loops=1) > >> -> Seq Scan on ma (cost=0.00..2181956.92 rows=48235392 width=76) (actual > >> time=21985.285..22204.308 rows=10 loops=1) > >> Total runtime: 22204.476 ms > >> (3 rows) > > > which version? > > I'm betting the problem is poor vacuuming practice leading to lots of > dead space. There's no way it takes 22 sec to read 10 rows if the > table is reasonably dense. This was my first thought, but: ,----[ Quote ] | I've tried | vacuuming this table many time `---- Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net