0

Given:

postgres=# \d b
                Table "public.b"
 Column | Type | Collation | Nullable | Default 
--------+------+-----------+----------+---------
 a      | uuid |           | not null | 
Indexes:
    "b_a_idx" btree (a)

postgres=# \d c
                Table "public.c"
 Column | Type | Collation | Nullable | Default 
--------+------+-----------+----------+---------
 a      | text |           | not null | 
Indexes:
    "c_a_idx" btree (a)

postgres=# explain b;
ERROR:  syntax error at or near "b"
LINE 1: explain b;
                ^
postgres=# analyze b; 
ANALYZE
postgres=# analyze c;
ANALYZE

Why does the following quering not use b.a's index?

postgres=# explain (select * from b join c on (b.a :: text) = c.a);
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Merge Join  (cost=129.05..455.25 rows=12580 width=48)
   Merge Cond: (c.a = ((b.a)::text))
   ->  Index Only Scan using c_a_idx on c  (cost=0.15..64.55 rows=1360 width=32)
   ->  Sort  (cost=128.89..133.52 rows=1850 width=16)
         Sort Key: ((b.a)::text)
         ->  Seq Scan on b  (cost=0.00..28.50 rows=1850 width=16)
(6 rows)
4
  • How many records in each table? – Vérace Oct 13 '20 at 12:31
  • 1
    As a side note: it's not necessary to enclose the actual query in parentheses when using explain or explain (analyze) – a_horse_with_no_name Oct 13 '20 at 12:46
  • @Vérace there are 0 rows in each table – Kevin Meredith Oct 14 '20 at 3:02
  • 2
    Why would you expect the optimiser to choose an index if there are no records? Even if you follow @a_horse_with_no_name 's strategy - the index will be ignored anyway - the optimiser will say something like "no records - no need to use an index - it's faster to just scan the whole table" - it will say the same for a small (not sure where the tipping point is!) number of records. If you want to test this sort of thing, you should use the generate_series function to produce tables with large amounts of data! – Vérace Oct 14 '20 at 6:21
3

Why does the following query not use b.a's index?

Because the index only stores the UUID value of a, not the result of the expression a::text

To make the index eligible for use, you would need to cast the other column to the data type stored in the index, e.g.

select * 
from b 
   join c on b.a = c.a::uuid;

Note that this doesn't guarantee that the index is used, it just makes it possible.

But as the query retrieves all rows from both tables, it's highly unlikely that the index is used anyway.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.