[GENERAL] Question about index usage

[Guido Neitzer]

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)



cug



   Filter: ((plz)::text ~~ '45257'::text)

         Index Cond: ((plz)::text ~=~ '45257'::character varying)
Total runtime: 14.328 ms
(5 rows)



   Recheck Cond: ((plz)::text = '45257'::text)

         Index Cond: ((plz)::text = '45257'::text)
Total runtime: 0.826 ms
(5 rows)

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. 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) -> 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) 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) -> 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)

smime.p7s
Description: S/MIME cryptographic signature