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 AEB4A5355B for ; Wed, 23 Mar 2005 09:16:31 +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 96943-10 for ; Wed, 23 Mar 2005 09:16:18 +0000 (GMT) Received: from mail.census.gov.ph (mail.census.gov.ph [203.172.28.120]) by svr1.postgresql.org (Postfix) with ESMTP id B559A53551 for ; Wed, 23 Mar 2005 09:16:01 +0000 (GMT) Received: from notnot ([192.168.1.70]) by mail.census.gov.ph (8.13.1/8.13.1) with ESMTP id j2N42ax1022249 for ; Wed, 23 Mar 2005 12:02:36 +0800 Message-Id: <200503230402.j2N42ax1022249@mail.census.gov.ph> From: "Michael Ryan S. Puncia" To: Subject: Re: best practices with index on varchar column Date: Wed, 23 Mar 2005 12:11:56 +0800 MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 In-Reply-To: Thread-Index: AcUuzPGCUiY9hn5hSmiI4tDc+4rTygAkQb9Q X-NSO-MailScanner: Found to be clean by NSO MailScanner. X-MailScanner-From: mpuncia@census.gov.ph X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.137 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/486 X-Sequence-Number: 11191 I have an experience using LIKE in a VARCHAR column and select statement suffers a lot so I decided to go back in CHAR Note: my database has about 50 millions records a b tree index > Can I use an index on a varchar column to optimize the SELECT queries > that > use " column LIKE 'header%' "? Yes > If yes what is the best tree algotithm to use ? Btree Note that if you want case insensitive matching you need to make an index on lower(column) and SELECT WHERE lower(column) LIKE 'header%' Locales may bite you. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org __________ NOD32 1.1023 (20050310) Information __________ This message was checked by NOD32 Antivirus System. http://www.nod32.com