I have a table with about 200.000 entries. Among other things, it contains an integer field I use as a timestamp, and a variable character field I use for user names. Certain queries are taking too long IMO. I'm trying this on both 7.4 and 8.0.
If I do a direct comparison (using =) on the user name field and I sort by the numeric field, I get about 5 ms. If I do a LIKE on the user name and I don't sort at all, I get about 5 ms too. But if I use
both LIKE on the user name and sorting on the timestamp, the time jumps to 2000 ms.
explain analyze select * from log_entries where user_name like '%john_doe%' limit 100 offset 0;
This will not (and never will be able to) use an index on user_name. Think about it, you'd need an index that ordered use_name so that (john_doe, AAAAAjohn_doe, Zjohn_doe1234) were all next to each other.
If you anchor the search (LIKE 'john_doe%') and are using the C locale then an index can be used (because it's the same as >='john_doe' AND <'john_dof').
If you really need to do indexed searches anywhere in a text-field you'll need to look at tsearch2 in the contrib/ directory. That lets you build a full-text index, but it's really meant for documents rather than user names.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
