trivial like clauses and indexes question

From: jens <jens(dot)theisen(at)transversal(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: trivial like clauses and indexes question
Date: 2005-10-19 19:28:16
Message-ID: 43569E50.5070808@transversal.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hello,

I'm trying to do a simple select with a like clause,

explain select * from test where name like '_100_100_100%';
QUERY PLAN
-------------------------------------------------------
Seq Scan on test (cost=0.00..589.54 rows=1 width=68)
Filter: ((name)::text ~~ '_100_100_100%'::text)

but it's not using any key though

explain select * from test where name = '_100_100_100';
QUERY PLAN
-------------------------------------------------------------------------
Index Scan using idx_nrm on test (cost=0.00..360.11 rows=104 width=68)
Index Cond: ((name)::text = '_100_100_100'::text)

is and my keys are defined as

Table "public.test"
Column | Type | Modifiers
--------+------------------------+-----------
name | character varying(100) |
Indexes:
"idx" btree (name varchar_pattern_ops)
"idx_nrm" btree (name)

What's the problem here? My locale isn't C, if this makes some difference.

Thanks for any help,

Jens

Browse pgsql-novice by date

  From Date Subject
Next Message operationsengineer1 2005-10-19 19:36:10 Re: FATAL: missing or erroneous pg_hba.conf file
Previous Message Cristian Prieto 2005-10-19 18:24:45 SQL return value...