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 80A493A43BC for ; Thu, 11 Nov 2004 20:49:58 +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 36476-06 for ; Thu, 11 Nov 2004 20:49:48 +0000 (GMT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by svr1.postgresql.org (Postfix) with ESMTP id 9B9263A43AD for ; Thu, 11 Nov 2004 20:49:49 +0000 (GMT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1CSLt5-0007zo-00; Thu, 11 Nov 2004 15:49:47 -0500 To: Allen Landsidel Cc: pgsql-performance@postgresql.org Subject: Re: Strange (?) Index behavior? 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> In-Reply-To: <88f1825a041111082176f13033@mail.gmail.com> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 11 Nov 2004 15:49:46 -0500 Message-ID: <873bzgdsb9.fsf@stark.xeocode.com> Lines: 33 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 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.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/186 X-Sequence-Number: 9140 Allen Landsidel writes: > QUERY PLAN > ----------------------------------------------------------------------------------------------------------------------------------------------- > Index Scan using sname_unique on "testtable" (cost=0.00..34453.74 > rows=8620 width=20) (actual time=77.004..537065.079 rows=74612 > loops=1) > Index Cond: ((sname >= 'AA'::text) AND (sname < 'AB'::text)) > Filter: (sname ~~ 'AA%'::text) > Total runtime: 537477.737 ms > (4 rows) > > Time: 537480.571 ms Nothing you're going to do to the query is going to come up with a more effective plan than this. It's using the index after all. It's never going to be lightning fast because it has to process 75k rows. However 75k rows shouldn't be taking nearly 10 minutes. It should be taking about 10 seconds. The 77ms before finding the first record is a bit suspicious. Have you vacuumed this table regularly? Try a VACUUM FULL VERBOSE, and send the results. You might try to REINDEX it as well, though I doubt that would help. Actually you might consider clustering the table on sname_unique. That would accomplish the same thing as the VACUUM FULL command and also speed up the index scan. And the optimizer knows (if you analyze afterwards) it so it should be more likely to pick the index scan. But currently you have to rerun cluster periodically. -- greg