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 782853A3E83 for ; Fri, 5 Nov 2004 21:02:54 +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 60742-01 for ; Fri, 5 Nov 2004 21:02:45 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.196]) by svr1.postgresql.org (Postfix) with ESMTP id E3ED23A3E39 for ; Fri, 5 Nov 2004 21:02:46 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 58so70875wri for ; Fri, 05 Nov 2004 13:02:43 -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=XqaLFQnXPiv8BEB5fij9ovPGHDoKr70wtdrnVGl/5ZR0tvdc6c4aji1BNCJaNcYxAqFwXh7vxhARmB8YMXRwhyj6Z3xuEDo80nZzbn7BM0aIqrKpfm6NLrwS3Eut/+/EEUDu2UN8nygPACeIDvvlu5rsPbp/t0AX4UNVaXaJhI8= Received: by 10.54.21.71 with SMTP id 71mr54360wru; Fri, 05 Nov 2004 13:02:43 -0800 (PST) Received: by 10.54.43.36 with HTTP; Fri, 5 Nov 2004 13:02:43 -0800 (PST) Message-ID: <88f1825a04110513028759dd3@mail.gmail.com> Date: Fri, 5 Nov 2004 16:02:43 -0500 From: Allen Landsidel Reply-To: Allen Landsidel To: pgsql-performance@postgresql.org Subject: Re: Strange (?) Index behavior? In-Reply-To: <008401c4c366$127d5260$8300a8c0@solent> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <88f1825a041105095631064cb0@mail.gmail.com> <008401c4c366$127d5260$8300a8c0@solent> 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/112 X-Sequence-Number: 9066 On Fri, 5 Nov 2004 18:34:23 -0000, Matt Clark wrote: > > With that many rows, and a normal index on the field, > > postgres figures the best option for say "I%" is not an index > > scan, but a sequential scan on the table, with a filter -- > > quite obviously this is slow as heck, and yes, I've run > > analyze several times and in fact have the vacuum analyze automated. > > Ah, so "like 'I%'" uses a very slow seq scan, but "like 'ABC%'" uses an > ordinary index OK? If so then... That is correct. > The planner would usually assume (from what Tom usually says) that 1/26 > selectivity isn't worth doing an index scan for, but in your case it's wrong > (maybe because the rows are very big?) The rows aren't big, it's a text field, a few ints, and a few timestamps. That's all. The text field is the one we're querying on here and lengthwise it's typically not over 32 chars. > You may be able to get the planner to go for an index scan on "like 'I%'" by > tweaking the foo_cost variables in postgresql.conf That's true but I'd rather not, there are times when the seqscan will have a faster net result (for other queries) and I'd rather not have them suffer. > Or you could have the app rewrite "like 'I%'" to "like 'IA%' or like 'IB%' > ... ", or do that as a stored proc. Holy cow. Yeah that seems a little outrageous. It would be cleaner looking in "\d table" than having all these indexes at the cost of having one very ugly query. > > With the partial index the index scan is used and the cost > > drops from 0..2million to 0..9000 -- a vast improvement. > > So there are really only 9000 rows out of 76 million starting with 'I'? How > about combining some techniques - you could create an index on the first two > chars of the field (should be selective enough to give an index scan), > select from that, and select the actual data with the like clause. I was talking about the cost, not the number of rows. About 74,000 rows are returned but the query only takes about 8 seconds to run. -- with the partial index in place. > CREATE INDEX idx_firstletters ON table (substr(field, 1, 2)); > CREATE INDEX idx_all ON table (field); > SELECT field FROM (SELECT field FROM table WHERE substr(field, 1, 2) = 'DE') > AS approx WHERE field LIKE 'DE%'; That looks like a pretty slick way to create an index, I didn't know there was such a way to do it.. but It appears that this will not work with queries where the WHERE clause wants to find substrings longer than 2 characters. I will give it a try and see how it goes though I think I'm fairly "settled" on creating all the other indexes, unless there is some specific reason I shouldn't -- they are used in all cases where the substring is >= 1 character, so long as I make sure the first where clause (or inner select in a subquery) is the most ambiguous from an index standpoint. Going back to the initial problem -- having only one large, complete index on the table (no partial indexes) the query "SELECT field FROM table WHERE field LIKE 'A%';" does not use the index. The query "SELECT field FROM table WHERE field LIKE 'AB%';" however, does use the single large index if it exists. Adding the partial index "CREATE INDEX idx_table_substrfield_A ON table (field) WHERE field LIKE 'A%';" causes all queries with substrings of any length to do index scans.provided I issue the query as: SELECT field FROM table WHERE field LIKE 'A%' AND field LIKE 'AB%'; -- or even -- SELECT field FROM table WHERE field LIKE 'A%'; The latter query, without the partial index described, does a sequential scan on the table itself instead of an index scan. -Allen