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 3CD833A45DA for ; Fri, 12 Nov 2004 20:12:46 +0000 (GMT) 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 62014-02 for ; Fri, 12 Nov 2004 20:12:35 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.193]) by svr1.postgresql.org (Postfix) with ESMTP id 12BFB3A47E7 for ; Fri, 12 Nov 2004 20:12:36 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 36so105249wra for ; Fri, 12 Nov 2004 12:12:36 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=myYV7/mbR7ztK+EKNHm/8TnY1fM0KWvVkcFbf8afjXQ0M83x6/52AdOMJXx8X04j9b+GeSEAc8R55SKS7LuyZygNsGL/gOWVyqkGAgI8Vh0ZctJ5Q+VFtuk9VFze/OUnOBCKdXn7iXWmEEyy+BPlZUaQoEJI5CN0I85CZJMDF/Q= Received: by 10.54.54.78 with SMTP id c78mr501405wra; Fri, 12 Nov 2004 12:12:35 -0800 (PST) Received: by 10.54.43.36 with HTTP; Fri, 12 Nov 2004 12:12:35 -0800 (PST) Message-ID: <88f1825a04111212123c20086@mail.gmail.com> Date: Fri, 12 Nov 2004 15:12:35 -0500 From: Allen Landsidel Reply-To: Allen Landsidel To: pgsql-performance@postgresql.org Subject: Re: Strange (?) Index behavior? In-Reply-To: <11884.1100209311@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <88f1825a041105095631064cb0@mail.gmail.com> <008401c4c366$127d5260$8300a8c0@solent> <88f1825a04110513028759dd3@mail.gmail.com> <418C1757.70909@ymogen.net> <25492.1099713863@sss.pgh.pa.us> <88f1825a04110523276f83653b@mail.gmail.com> <88f1825a041111082176f13033@mail.gmail.com> <873bzgdsb9.fsf@stark.xeocode.com> <88f1825a04111113125a0fc443@mail.gmail.com> <11884.1100209311@sss.pgh.pa.us> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/193 X-Sequence-Number: 9147 On Thu, 11 Nov 2004 16:41:51 -0500, Tom Lane wrote: > Allen Landsidel writes: > > Clustering is really unworkable in this situation. > > Nonetheless, please do it in your test scenario, so we can see if it has > any effect or not. It did not, not enough to measure anyway, which does strike me as pretty odd.. Here's what I've got, after the cluster. Note that this is also on a new filesystem, as I said, have been taking the chance to experiment. The other two results were from a filesystem with 64KB block size, 8KB fragment size. This one is 8KB and 8KB. search=# explain analyze search-# SELECT sname FROM testtable WHERE sname LIKE 'AA%'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using sname_unique on "testtable" (cost=0.00..642138.83 rows=160399 width=20) (actual time=0.088..514438.470 rows=74612 loops=1) Index Cond: ((sname >= 'AA'::text) AND (sname < 'AB'::text)) Filter: (sname ~~ 'AA%'::text) Total runtime: 514818.837 ms (4 rows) Time: 514821.993 ms > > The speed you're getting works out to about 7.2 msec/row, which would be > about right if every single row fetch caused a disk seek, which seems > improbable unless the table is just huge compared to your available RAM. > > regards, tom lane The CSV for the table is "huge" but not compared to RAM. The dump of the database in native/binary format is ~1GB; the database currently has only this table and the system stuff. The time to fetch the first row was much faster with the cluster in place, but after that, it's pretty much the same. 537s vs. 515s