Обсуждение: Question about index usage

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)

On 3/7/06, Guido Neitzer <guido.neitzer@pharmaline.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/

On 07.03.2006, at 12:09 Uhr, chris smith wrote:

> Try without the quotes:
>
> select id from dga_dienstleister where plz in (45257, 45259);

Same result, second index is used.

> What is the table structure for dga_dienstleister ?

For the relevant column:

plz                                | character varying(256)      |
not null

Thanks,
cug

--
PharmaLine, Essen, GERMANY
Software and Database Development

Guido Neitzer <guido.neitzer@pharmaline.de> writes:
> 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)

Because IN means "=", which is a member of the index opclass for the
second index but not the first.

Why do you care?  Should be about the same result either way.

            regards, tom lane

On 07.03.2006, at 16:04 Uhr, Tom Lane wrote:

> Because IN means "=", which is a member of the index opclass for the
> second index but not the first.
>
> Why do you care?  Should be about the same result either way.

Only because I haven't set up the second index because I wasn't aware
of this fact.

cug


--
PharmaLine, Essen, GERMANY
Software and Database Development