What I have is a very simple database that stores paths, extensions and names of files from UNC shares. For testing, I inserted about 1,5 mio rows and the below query uses a GiST index as you see, but still it takes 5 seconds to return. Expected would be a few (like 100) milliseconds.
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM residentfiles WHERE parentpath LIKE 'somevalue'
%% in the query, it takes the not that long, even when using sequential scan (?!)
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM residentfiles WHERE parentpath LIKE '%a%'
I also have the same setup for the
name (filename) column, when executing a similar query on that one, it only takes half of the time, even when using
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM residentfiles WHERE name LIKE '%a%'
What I already tried cannot be written here in short words. Whatever I do, it gets slow starting from about 1 mio rows. As there is basically never anything deleted, of course vacuuming and reindexing does not help at all.
I cannot really use any other type of search than
LIKE %% and a GIN or GiST index because I need to be able to find any character in the columns of interest, not only “words for a specific human language”.
Is my expectation that this should work in around 100 milliseconds even for many million more rows that wrong?