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 183643A2B27 for ; Fri, 5 Nov 2004 18:34:29 +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 17885-08 for ; Fri, 5 Nov 2004 18:34:19 +0000 (GMT) Received: from neptune.lgu.ac.uk (neptune.lgu.ac.uk [140.97.30.6]) by svr1.postgresql.org (Postfix) with ESMTP id D30453A202A for ; Fri, 5 Nov 2004 18:34:18 +0000 (GMT) Received: from solent (cal42-234.lgu.ac.uk [140.97.42.234]) by neptune.lgu.ac.uk (8.12.10/8.12.10) with ESMTP id iA5IYAO6009354; Fri, 5 Nov 2004 18:34:10 GMT Reply-To: From: "Matt Clark" To: "'Allen Landsidel'" , Subject: Re: Strange (?) Index behavior? Date: Fri, 5 Nov 2004 18:34:23 -0000 Organization: Ymogen Ltd Message-ID: <008401c4c366$127d5260$8300a8c0@solent> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 In-Reply-To: <88f1825a041105095631064cb0@mail.gmail.com> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Importance: Normal X-LGU-MailScanner: Found to be clean X-MailScanner-From: matt@ymogen.net 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/106 X-Sequence-Number: 9060 > With that many rows, and a normal index on the field,=20 > postgres figures the best option for say "I%" is not an index=20 > scan, but a sequential scan on the table, with a filter --=20 > quite obviously this is slow as heck, and yes, I've run=20 > 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... 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?) 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=20 Or you could have the app rewrite "like 'I%'" to "like 'IA%' or like = 'IB%' ... ", or do that as a stored proc. > With the partial index the index scan is used and the cost=20 > 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. 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) =3D = 'DE') AS approx WHERE field LIKE 'DE%'; Any good?