Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 96D0264FF57 for ; Wed, 10 Dec 2008 18:37:01 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 35129-05 for ; Wed, 10 Dec 2008 18:36:55 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f28.google.com (mail-bw0-f28.google.com [209.85.218.28]) by mail.postgresql.org (Postfix) with ESMTP id EF62064FC4F for ; Wed, 10 Dec 2008 18:36:54 -0400 (AST) Received: by bwz9 with SMTP id 9so735959bwz.19 for ; Wed, 10 Dec 2008 14:36:52 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=i7TzET12QvCI5S5M1Q8yHXsaziWrGYBaH1cOSOdSDA4=; b=eHCOkrygLqh33SE1DypdnrUi6z6IkdtyLA4Z0/v9p8x7J4ZrM3ICl2sm7w2x5/6qmJ TZEp/BY7dSnlpFnKz363oqvFB/1C/SkocA5/kbT/6bhUfLMV0KM0GCw59/7Zj1CxsYt7 y/E3vAz5qyXFdJW+/NruQ7oUhj8/NptZg/Kww= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=lrQmWBIr4UahFEqKRy0W/jnPU3j+7SbtIwchviL6UtrVcJfRn1mjBxqJw60MvyK3nN UikdEKbDGMZ1lOYHJdvqsOfBrKPdp0CQFlPqQLmbgBznWFF1QEo8RhqCtLBzt7mYNW/X lEg7Ut28zQ2RFH84bq6rQwa5aOnfX+Y2IaS/U= Received: by 10.86.84.5 with SMTP id h5mr919016fgb.59.1228948611724; Wed, 10 Dec 2008 14:36:51 -0800 (PST) Received: by 10.86.1.8 with HTTP; Wed, 10 Dec 2008 14:36:51 -0800 (PST) Message-ID: <1d709ecc0812101436s1b2b53fdp64620af6af4f9d25@mail.gmail.com> Date: Thu, 11 Dec 2008 01:36:51 +0300 From: "Vladimir Sitnikov" To: "Richard Yen" Subject: Re: query plan with index having a btrim is different for strings of different length Cc: "Robert Haas" , pgsql-performance@postgresql.org In-Reply-To: <129E8D79-341B-411A-83B7-E2B509A7B6E2@richyen.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_24535_5758981.1228948611686" References: <751F49DC-1142-4E9C-95C8-BC3C229B3F0B@richyen.com> <22937.1228865243@sss.pgh.pa.us> <603c8f070812101139p189cc828hef8702f37f952b6d@mail.gmail.com> <129E8D79-341B-411A-83B7-E2B509A7B6E2@richyen.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200812/131 X-Sequence-Number: 32167 ------=_Part_24535_5758981.1228948611686 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline > > tii=# explain analyze SELECT m_object_paper.id FROM m_object_paper, > m_assignment WHERE m_object_paper.assignment = m_assignment.id AND > m_object_paper.owner=-1 AND m_assignment.class = 2450798 AND > lower(btrim(x_firstname)) = lower(btrim('Jordan')) and > lower(btrim(x_lastname)) = lower(btrim('Smith')); Is there an index on "m_object_paper.assignment"? It could solve the problem. With current indices on "btrim(last_name)" and "owner" you are just throwing the rows away (you have 521382 rows with "smith", 15494737 with owner=-1 and only 58 of them have both "smith"/"jordan" and -1). Consider creating index on m_object_paper using btree(lower(btrim(x_lastname))) where owner=-1; (it might add firstname column there as per Tom's suggestion) Or just index on (owner, lower(...)) if you have other queries with different values for owner. One more point that could improve bitmap scans is greater value for work_mem. You'll need 8*15494737 ~ 130Mb == 130000 for work_mem (however, that is way too high unless you have lots of RAM and just couple of active database sessions) Regards, Vladimir Sitnikov ------=_Part_24535_5758981.1228948611686 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

tii=# explain analyze SELECT m_object_paper.id FROM m_object_paper, m_assignment WHERE m_object_paper.assignment = m_assignment.id AND m_object_paper.owner=-1 AND m_assignment.class = 2450798 AND lower(btrim(x_firstname)) = lower(btrim('Jordan')) and lower(btrim(x_lastname)) = lower(btrim('Smith'));
Is there an index on "m_object_paper.assignment"? It could solve the problem.

With current indices on "btrim(last_name)" and "owner" you are just throwing the rows away (you have 521382 rows with "smith", 15494737 with owner=-1 and only 58 of them have both "smith"/"jordan" and -1).

Consider creating index on m_object_paper using btree(lower(btrim(x_lastname))) where owner=-1; (it might add firstname column there as per Tom's suggestion)

Or just index on (owner, lower(...)) if you have other queries with different values for owner.

One more point that could improve bitmap scans is greater value for work_mem. You'll need 8*15494737 ~ 130Mb == 130000 for work_mem (however, that is way too high unless you have lots of RAM and just couple of active database sessions)


Regards,
Vladimir Sitnikov 
------=_Part_24535_5758981.1228948611686--