3

To create my table and index I use the following code:

CREATE TABLE IF NOT EXISTS users (
    id SERIAL NOT NULL,
    name VARCHAR(512) NOT NULL,
    PRIMARY KEY (id));

CREATE INDEX users_name_idx ON users (lower(name::varchar(16)));

My question - is users_name_idx index used in the following queries:

  1. SELECT * FROM users WHERE LOWER(name) LIKE 'somename%'?
  2. SELECT * FROM users ORDER BY name?
Improve this question
2
  • 1
    Check the execution plan and you will know. But if only the first 16 characters are important then why did you define name with a limit of 512? Commented Apr 20, 2018 at 7:00
  • postgresql.org/docs/current/static/using-explain.html Commented Apr 20, 2018 at 7:01

1 Answer 1

Reset to default
2

Your index can be used by none of your queries, because the expression is not the same as in the queries:

test=> \d users
                             Table "laurenz.users"
 Column |          Type          | Nullable |              Default              
--------+------------------------+----------+-----------------------------------
 id     | integer                | not null | nextval('users_id_seq'::regclass)
 name   | character varying(512) | not null | 
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_name_idx" btree (lower(name::character varying(16)::text))

test=> SET enable_seqscan = off;

test=> EXPLAIN SELECT * FROM users WHERE LOWER(name) LIKE 'somename%';
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Seq Scan on users  (cost=10000000000.00..10000000012.10 rows=1 width=520)
   Filter: (lower((name)::text) ~~ 'somename%'::text)
(2 rows)

test=> EXPLAIN SELECT * FROM users ORDER BY name;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Sort  (cost=10000000016.39..10000000016.74 rows=140 width=520)
   Sort Key: name
   ->  Seq Scan on users  (cost=10000000000.00..10000000011.40 rows=140 width=520)
(3 rows)

For the index to be used, you would have to use the same expression, including the type cast, in your queries.

In addition to that, unless your column has collation C, your index cannot be userd in LIKE queries. You'd gave to use the text_pattern_ops operator class.

I guess that the reason behind creating such an index is to reduce the size of the index, which is a commendable thing to do.

I would recommend an index like this:

CREATE INDEX ON users (lower(name::varchar(16)) text_pattern_ops);

Then use this query:

SELECT * FROM users
WHERE lower(name) LIKE 'somename%'
  AND lower(name::varchar(16)) LIKE substr('somename%', 1, 16);

The second condition could be lossy if somename is longer than 15 characters, but it can use the index. The first condition filters out the false positives.

Unfortunately, there is no trick like this when it comes to ordering.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thank you very much for your post. It explains a lot of. I thought that I am getting crazy.
As an aside, it would have been better to ask about your real problem instead about the problems with your solution. See here.
Unfortunately, I can't agree with you, because the type of the question depends on what the man needs. If one needs to find a solution he is saying about his problem and asking about solution for it. If a man trying to find the answer why some technology works this way but not that way he asked about this technology feature.
I didn't realize it was a theoretical question (which is fine). The question looked like you had an actual problem.
I agree that reducing index size is good, but as I use ORM I have to refuse from it. So I do CREATE INDEX users_name_idx ON users (lower(name), text_pattern_ops); With such index when I do WHERE LOWER(name) LIKE '...%' index is used - that's ok. The problem is that in ORDER BY LOWER(name) and in ORDER BY name this index is not used. Or I understand something wrong?
|

Your Answer

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