From: | Guido Neitzer <guido(dot)neitzer(at)pharmaline(dot)de> |
---|---|
To: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Question about index usage |
Date: | 2006-03-07 11:05:32 |
Message-ID: | 24BD8F80-24C0-4C12-B3C3-32C2F502300B@pharmaline.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | chris smith | 2006-03-07 11:09:17 | Re: Question about index usage |
Previous Message | Guido Neitzer | 2006-03-07 10:59:18 | Logging seq scans |