X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id BC9F147633C for ; Tue, 15 Apr 2003 15:05:20 -0400 (EDT) Received: from sabre.velocet.net (sabre.velocet.net [216.138.209.205]) by postgresql.org (Postfix) with ESMTP id 02512476333 for ; Tue, 15 Apr 2003 15:05:20 -0400 (EDT) Received: from stark.dyndns.tv (H162.C233.tor.velocet.net [216.138.233.162]) by sabre.velocet.net (Postfix) with ESMTP id BB74513832C; Tue, 15 Apr 2003 15:04:14 -0400 (EDT) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian)) id 195Viw-0005Er-00; Tue, 15 Apr 2003 15:04:06 -0400 To: pgsql-performance@postgresql.org Subject: Using indexes for like foo% type queries when foo isn't constant (not a locale issue) From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 15 Apr 2003 15:04:04 -0400 Message-ID: <87of37oa57.fsf@stark.dyndns.tv> Lines: 41 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Spam-Status: No, hits=-12.9 required=5.0 tests=BAYES_01,USER_AGENT_GNUS_UA autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/137 X-Sequence-Number: 1643 In the following query postgres doesn't use the index. In the hard-coded version below it does. I suppose it can't because it's possible the "target" could have wildcards etc in them. Is there any way to indicate the postgres that that won't happen? This is going to be even more of an issue when preparsed queries happen because even in the hard coded example it will be an issue. I know in Oracle if you parse a query with a LIKE :1||'%' type expression it still plans to use the index and that's extremely useful. I don't know what it does if there's a % in the parameter, it either takes the performance hit or it doesn't treat them as special? db=> explain analyze select postalcode, abs(substr(target,6,1)::integer-substr(postalcode,6,1)::integer) as dist from postalcodes, (select 'L6C2M6'::text as target) as t where postalcode like substr(target,1,5)||'%' order by dist asc limit 2; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=12182.77..12182.77 rows=2 width=42) (actual time=9226.17..9226.18 rows=2 loops=1) -> Sort (cost=12182.77..12186.16 rows=1359 width=42) (actual time=9226.16..9226.16 rows=2 loops=1) Sort Key: abs(((substr(t.target, 6, 1))::integer - (substr((postalcodes.postalcode)::text, 6, 1))::integer)) -> Nested Loop (cost=0.00..12112.04 rows=1359 width=42) (actual time=3262.89..9205.25 rows=8 loops=1) Join Filter: ("inner".postalcode ~~ (substr("outer".target, 1, 5) || '%'::text)) -> Subquery Scan t (cost=0.00..0.01 rows=1 width=0) (actual time=0.04..0.05 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.02..0.02 rows=1 loops=1) -> Seq Scan on postalcodes (cost=0.00..7335.69 rows=271769 width=10) (actual time=5.52..3268.74 rows=271769 loops=1) Total runtime: 9241.92 msec (9 rows) db=> explain analyze select postalcode, abs(substr('L6C2M6',6,1)::integer-substr(postalcode,6,1)::integer) as dist from postalcodes where postalcode like substr('L6C2M6',1,5)||'%' order by dist asc limit 2; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=3.29..3.29 rows=1 width=10) (actual time=36.54..36.55 rows=2 loops=1) -> Sort (cost=3.29..3.29 rows=1 width=10) (actual time=36.53..36.54 rows=2 loops=1) Sort Key: abs((6 - (substr((postalcode)::text, 6, 1))::integer)) -> Index Scan using idx_postalcodes_postalcodeon on postalcodes (cost=0.00..3.28 rows=1 width=10) (actual time=35.91..36.33 rows=8 loops=1) Index Cond: ((postalcode >= 'L6C2M'::bpchar) AND (postalcode < 'L6C2N'::bpchar)) Filter: (postalcode ~~ 'L6C2M%'::text) Total runtime: 36.93 msec (7 rows) -- greg