On Fri, Oct 25, 2019 at 7:28 PM Michael Lewis <mlewis@entrata.com> wrote:
But it prints too many records: all moves cross-multiplied with each other.
As if I have forgotten to add 1 more condition to the JOIN LATERAL
LIMIT 1 inside your lateral should resolve that. Personally, I'd move that condition to EXISTS condition inside WHERE clause instead. Just a style thing as far as I know.
How would you do that with EXISTS? I tried, but my query failed to compile
Ooops. I didn't notice you had selected the previous move ID. In that case, at the end of the lateral you want ORDER BY mid DESC LIMIT 1, or perhaps order by played field and you should be good to go. Particularly if you have an index on (gid, mid) then it should be very fast. If you want to quickly find these "interesting moves" and they are very rare, a partial index would be quite helpful. Highly stylized, but when it is indexing 1-5% of a huge table perhaps, it can be a very handy tool.
CREATE INDEX idx_interesting_moves ON words_moves USING btree( played ) WHERE action='play' AND length(letters) = 7 and length(hand)=7;
