From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Marcin Krol <mrkafk(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: why is index not used? |
Date: | 2008-10-28 16:14:15 |
Message-ID: | 7952.1225210455@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Marcin Krol <mrkafk(at)gmail(dot)com> writes:
> booktown=# explain analyze select * from auth where first_name like 'a11%';
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------
> Seq Scan on auth (cost=0.00..56796.68 rows=1 width=42) (actual
> time=0.091..983.665 rows=1111 loops=1)
> Filter: ((first_name)::text ~~ 'a11%'::text)
> Total runtime: 986.314 ms
> (3 rows)
> FAQ says that in order to use index, LIKE statements cannot begin with
> %, so I should be fine?
The other point is that in non-C locales, a standard varchar index isn't
usable for LIKE (the sort order is usually wrong). You can re-initdb
in C locale or create a varchar_pattern_ops index.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | JORGE MALDONADO | 2008-10-28 16:28:26 | DATABASE INDEX DESIGN |
Previous Message | Sean Davis | 2008-10-28 16:11:46 | Re: why is index not used? |