Re^7: Documentation of REGEXP support in DBD::SQLite?

Thanks :)

> Because, of course, if there are too many hits (here: 897633), the system switches to SeqScan

This doesn't decisively answer the question if trigrams are indexed by bigrams... 🤔

What if you search a bigram which is - by design- only in few rows?

Or an and combination of multiple bigrams?

And to be sure, please use the like '%ab%' form again.

Cheers Rolf
_{(addicted to the Perl Programming Language :)

see Wikisyntax for the Monastery}

Comment on Re^7: Documentation of REGEXP support in DBD::SQLite? Select or Download Code

Replies are listed 'Best First'.
Re^8: Documentation of REGEXP support in DBD::SQLite? by erix (Prior) on Nov 16, 2024 at 08:31 UTC
(For decisive answers, read the postgres code ;)) And to be sure, please use like '%ab%' again. I can't get bigrams to respond quickly; also not when there is only one matching value; with this data it will always Seq scan (sometimes with parallel workers: just under 1 second). In postgres, 'LIKE' doesn't allow regex (although its simple pattern search can sometimes use the trigram or btree index). Postgres uses the tilde for regex search (~ case sensitive, ~* case insensitive).	[reply]
Re^9: Documentation of REGEXP support in DBD::SQLite? by LanX (Saint) on Nov 16, 2024 at 08:35 UTC
> I can't get bigrams to respond quickly Hence they are not indexed. > although its simple pattern search can sometimes use the trigram or btree index That's why I wanted LIKE, to be sure the trigram optimization can be chosen Cheers Rolf _{(addicted to the Perl Programming Language :) see Wikisyntax for the Monastery}	[reply]