X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 17E5FD1B461 for ; Tue, 25 Nov 2003 19:47:00 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 25699-01 for ; Tue, 25 Nov 2003 15:46:28 -0400 (AST) Received: from jhuml2.jhmi.edu (jhuml2.jhmi.edu [162.129.234.21]) by svr1.postgresql.org (Postfix) with ESMTP id 97E0ED1B454 for ; Tue, 25 Nov 2003 15:46:27 -0400 (AST) Received: from jhuml2.jhmi.edu (jhuml2.jhmi.edu [162.129.234.21]) by jhuml2.jhmi.edu (PMDF V6.2-X17 #30840) with SMTP id <0HOX005BYAR30W@jhuml2.jhmi.edu> for pgsql-performance@postgresql.org; Tue, 25 Nov 2003 14:45:44 -0500 (EST) Received: from jhuml2.jhmi.edu ([162.129.234.21]) by jhuml2.jhmi.edu (SAVSMTP 3.1.0.29) with SMTP id M2003112514454426245 for ; Tue, 25 Nov 2003 14:45:44 -0500 Received: from jhmimail.jhmi.edu (jhem2.jhmi.edu [162.129.8.23]) by jhuml2.jhmi.edu (PMDF V6.2-X17 #30840) with ESMTP id <0HOX0039TAW7XU@jhuml2.jhmi.edu> for pgsql-performance@postgresql.org; Tue, 25 Nov 2003 14:45:44 -0500 (EST) Received: from [162.129.178.60] by jhmimail.jhmi.edu (mshttpd); Tue, 25 Nov 2003 19:48:49 +0000 (GMT) Date: Tue, 25 Nov 2003 19:48:49 +0000 (GMT) From: LIANHE SHAO Subject: why index scan not working when using 'like'? To: pgsql-performance@postgresql.org Message-id: <3d83693d9fe0.3d9fe03d8369@jhmimail.jhmi.edu> MIME-version: 1.0 X-Mailer: iPlanet Messenger Express 5.2 HotFix 1.17 (built Jun 23 2003) Content-type: text/plain; charset=us-ascii Content-language: en Content-transfer-encoding: 7bit Content-disposition: inline X-Accept-Language: en X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200311/249 X-Sequence-Number: 4809 Hi all, I want to use index on the gene_symbol column in my query and gene_symbol is indexed. but when I use lower (gene_symbol) like lower('%mif%'), the index is not used. While when I change to lower(gene_symbol) = lower('mif'), the index is used and index scan works, but this is not what I like. I want all the gene_symbols containing substring 'mif' are pulled out, and not necessarily exactly match. could anybody give me some hints how to deal with this. If I do not used index, it take too long for the query. PGA> explain select distinct probeset_id, chip, gene_symbol, title, sequence_description, pfam from affy_array_annotation where lower(gene_symbol) like upper('%mif%'); QUERY PLAN ----------------------------------------------------------------------------------------- Unique (cost=29576.44..29591.44 rows=86 width=265) -> Sort (cost=29576.44..29578.59 rows=857 width=265) Sort Key: probeset_id, chip, gene_symbol, title, sequence_description, pfam -> Seq Scan on affy_array_annotation (cost=0.00..29534.70 rows=857 width=265) Filter: (lower((gene_symbol)::text) ~~ 'MIF%'::text) (5 rows) PGA=> explain select distinct probeset_id, chip, gene_symbol, title, sequence_description, pfam from affy_array_annotation where lower(gene_symbol) = upper('%mif%'); QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Unique (cost=3433.44..3448.44 rows=86 width=265) -> Sort (cost=3433.44..3435.58 rows=857 width=265) Sort Key: probeset_id, chip, gene_symbol, title, sequence_description, pfam -> Index Scan using gene_symbol_idx_fun1 on affy_array_annotation (cost=0.00..3391.70 rows=857 width=265) Index Cond: (lower((gene_symbol)::text) = '%MIF%'::text) (5 rows) Regards, William