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 EFD6D3A428A for ; Thu, 11 Nov 2004 21:48:53 +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 59546-03 for ; Thu, 11 Nov 2004 21:48:43 +0000 (GMT) Received: from hosting.commandprompt.com (128.commandprompt.com [207.173.200.128]) by svr1.postgresql.org (Postfix) with ESMTP id A2A3B3A3D32 for ; Thu, 11 Nov 2004 21:48:44 +0000 (GMT) Received: from [192.168.1.51] (dsl093-038-087.pdx1.dsl.speakeasy.net [66.93.38.87]) (authenticated) by hosting.commandprompt.com (8.11.6/8.11.6) with ESMTP id iABLmY828287; Thu, 11 Nov 2004 13:48:34 -0800 Message-ID: <4193DE2B.5060407@commandprompt.com> Date: Thu, 11 Nov 2004 13:48:27 -0800 From: "Joshua D. Drake" Organization: Command Prompt, Inc. User-Agent: Mozilla Thunderbird 0.9 (X11/20041103) X-Accept-Language: en-us, en MIME-Version: 1.0 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> <873bzgdsb9.fsf@stark.xeocode.com> <88f1825a04111113125a0fc443@mail.gmail.com> In-Reply-To: <88f1825a04111113125a0fc443@mail.gmail.com> Content-Type: multipart/mixed; boundary="------------010602020107000303060208" 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/190 X-Sequence-Number: 9144 This is a multi-part message in MIME format. --------------010602020107000303060208 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit >>>----------------------------------------------------------------------------------------------------------------------------------------------- >>> 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. I am confused about this statement. I have a table with 1.77 million rows that I use gist indexes on (TSearch) and I can pull out of it in less than 2 seconds. Are you saying it should be taking 10 seconds because of the type of plan? 10 seconds seems like an awfullong time for this. Sincerely, Joshua D. Drake > > > That's my feeling as well, I thought the index was to blame because it > will be quite large, possibly large enough to not fit in memory nor be > quickly bursted up. > > >>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. > > > This table is *brand spanking new* for lack of a better term. I have > the data for it in a CSV. I load the CSV up which takes a bit, then > create the indexes, do a vacuum analyze verbose, and then posted the > results above. I don't think running vacuum a more times is going to > change things, at least not without tweaking config settings that > affect vacuum. Not a single row has been inserted or altered since the > initial load.. it's just a test. > > I can't give vacuum stats right now because the thing is reloading > (again) with different newfs settings -- something I figure I have the > time to fiddle with now, and seldom do at other times. These numbers > though don't change much between 8K on up to 64K 'cluster' sizes. I'm > trying it now with 8K page sizes, with 8K "minimum fragment" sizes. > Should speed things up a tiny bit but not enough to really affect this > query. > > Do you still see a need to have the output from the vacuum? > > >>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. > > > Clustering is really unworkable in this situation. It would work now, > in this limited test case, but using it if this were to go into > production is unrealistic. It would have to happen fairly often since > this table is updated frequently, which will break the clustering > quickly with MVCC. > > Running it often.. well.. it has 70M+ rows, and the entire table is > copied, reordered, and rewritten.. so that's a lot of 'scratch space' > needed. Finally, clustering locks the table.. > > Something I'd already considered but quickly ruled out because of > these reasons.. > > More ideas are welcome though. ;) > > -Allen > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL --------------010602020107000303060208 Content-Type: text/x-vcard; charset=utf-8; name="jd.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="jd.vcf" begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:jd@commandprompt.com title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard --------------010602020107000303060208--