2

I have a table with 1000 records. I created two indexes with different collation.

create INDEX test_en on webshop.address (tenant_id, city collate "en-x-icu");
create INDEX test_de on webshop.address (tenant_id, city collate "de-x-icu-phonebk");

But when I try explain analyze the used indexes seem to be switched

EXPLAIN ANALYZE SELECT city, firstname, zip FROM webshop.address 
  WHERE tenant_id = 1 AND city > ('An' collate "en-x-icu") 
  ORDER BY city collate "en-x-icu";

gives the following query plan where the index test_de is chosen:

┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                      QUERY PLAN                                                       │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Sort  (cost=16.06..16.07 rows=5 width=20) (actual time=0.079..0.081 rows=31 loops=1)                                  │
│   Sort Key: city COLLATE "en-x-icu"                                                                                   │
│   Sort Method: quicksort  Memory: 26kB                                                                                │
│   ->  Bitmap Heap Scan on address  (cost=4.31..16.00 rows=5 width=20) (actual time=0.035..0.054 rows=31 loops=1)      │
│         Recheck Cond: (tenant_id = 1)                                                                                 │
│         Filter: (city > 'An'::text COLLATE "en-x-icu")                                                                │
│         Rows Removed by Filter: 44                                                                                    │
│         Heap Blocks: exact=2                                                                                          │
│         ->  Bitmap Index Scan on test_de  (cost=0.00..4.31 rows=5 width=0) (actual time=0.024..0.024 rows=75 loops=1) │
│               Index Cond: (tenant_id = 1)                                                                             │
│ Planning Time: 0.398 ms                                                                                               │
│ Execution Time: 0.146 ms                                                                                              │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

When I create the same query plan with collate "de-x-icu-phonebk" the index test_en is chosen.

When I drop the index test_de and repeat the first query I get the following query plan:

┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                      QUERY PLAN                                                       │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Sort  (cost=16.07..16.09 rows=5 width=20) (actual time=0.062..0.064 rows=31 loops=1)                                  │
│   Sort Key: city COLLATE "en-x-icu"                                                                                   │
│   Sort Method: quicksort  Memory: 26kB                                                                                │
│   ->  Bitmap Heap Scan on address  (cost=4.33..16.02 rows=5 width=20) (actual time=0.030..0.038 rows=31 loops=1)      │
│         Recheck Cond: ((tenant_id = 1) AND (city > 'An'::text COLLATE "en-x-icu"))                                    │
│         Heap Blocks: exact=2                                                                                          │
│         ->  Bitmap Index Scan on test_en  (cost=0.00..4.33 rows=5 width=0) (actual time=0.021..0.021 rows=31 loops=1) │
│               Index Cond: ((tenant_id = 1) AND (city > 'An'::text COLLATE "en-x-icu"))                                │
│ Planning Time: 0.206 ms                                                                                               │
│ Execution Time: 0.109 ms                                                                                              │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

2
  • 1
    It seems that your table is too small to make a meaningful statement about index usage; look at the execution times. With a couple of thousand rows, things should look different. Commented Apr 9 at 8:16
  • So what you are saying is that even though he has two indexes for two collations, Postgres can still choose "incorrect" when the table has not enough rows? I mean, the documentation states ".. However, this index cannot accelerate queries that involve some other collation. ..." and this would be quite surprising. Commented Apr 11 at 0:54

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.