Re: Question about index usage

On 3/7/06, Guido Neitzer <guido(dot)neitzer(at)pharmaline(dot)de> wrote:
> Hi.
>
> Is there a reason why this query:
>
> select id from dga_dienstleister where plz in ('45257', '45259');
>
> doesn't use this index:
>
> "dga_dienstleister_plz_index" btree (plz varchar_pattern_ops)
>
> but uses this index:
>
> "dga_dienstleister_plz_index2" btree (plz)
>
> I had the first index setup for queries with "plz like '4525%'" but I
> never tested the "in" query until I saw in the logs that these
> queries where slow compared to the rest. Query plans at the end.
>
> cug
>
>
> DGADB=# explain analyse select id from dga_dienstleister where plz
> like
> '45257'; Q
> UERY PLAN
> ------------------------------------------------------------------------
> ----------------------------------------------------------------
> Bitmap Heap Scan on dga_dienstleister (cost=2.07..82.41 rows=21
> width=8) (actual time=13.489..14.211 rows=16 loops=1)
> Filter: ((plz)::text ~~ '45257'::text)
> -> Bitmap Index Scan on dga_dienstleister_plz_index
> (cost=0.00..2.07 rows=21 width=0) (actual time=13.323..13.323 rows=16
> loops=1)
> Index Cond: ((plz)::text ~=~ '45257'::character varying)
> Total runtime: 14.328 ms
> (5 rows)
>
>
> DGADB=# explain analyse select id from dga_dienstleister where plz =
> '45257';
> QUERY
> PLAN
> ------------------------------------------------------------------------
> ---------------------------------------------------------------
> Bitmap Heap Scan on dga_dienstleister (cost=2.07..82.41 rows=21
> width=8) (actual time=0.486..0.663 rows=16 loops=1)
> Recheck Cond: ((plz)::text = '45257'::text)
> -> Bitmap Index Scan on dga_dienstleister_plz_index2
> (cost=0.00..2.07 rows=21 width=0) (actual time=0.424..0.424 rows=16
> loops=1)
> Index Cond: ((plz)::text = '45257'::text)
> Total runtime: 0.826 ms
> (5 rows)
>
>
>
>

Try without the quotes:

select id from dga_dienstleister where plz in (45257, 45259);

What is the table structure for dga_dienstleister ?

--
Postgresql & php tutorials
http://www.designmagick.com/