The following bug has been logged online:
Bug reference: 4341
Logged by: Lampa
Email address: lampacz@gmail.com
PostgreSQL version: 8.3.3
Operating system: Debian 2.6.18-6-amd64 #1 SMP Sun Feb 10 17:50:19 UTC
2008 x86_64 GNU/Linux
Description: planner doesn't using index for = operation
Details:
on rodne_cislo is created index with varchar_pattern_ops flag.
With varchar_pattern_ops planner is not using index(first explain) but when
use LIKE index is used.
I must create another index without varchar_pattern_ops flag to get equal
speed results.
explain analyze SELECT * FROM pacienti WHERE rodne_cislo = '8203070007';
QUERY PLAN
----------------------------------------------------------------------------
---------------------------------
Seq Scan on pacienti (cost=0.00..69155.35 rows=2 width=1294) (actual
time=221.901..303.158 rows=1 loops=1)
Filter: ((rodne_cislo)::text = '8203070007'::text)
Total runtime: 303.196 ms
(3 rows)
explain analyze SELECT * FROM pacienti WHERE rodne_cislo LIKE '8203070007';
QUERY PLAN
----------------------------------------------------------------------------
---------------------------------------------------------
Index Scan using i_pacienti_rodne_cislo on pacienti (cost=0.00..12.36
rows=2 width=1294) (actual time=0.032..0.036 rows=1 loops=1)
Index Cond: ((rodne_cislo)::text ~=~ '8203070007'::text)
Filter: ((rodne_cislo)::text ~~ '8203070007'::text)
Total runtime: 0.066 ms
(4 rows)