[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Trying to fetch records only if preceded by at least another one
From:       Michael Lewis <mlewis () entrata ! com>
Date:       2019-10-25 22:39:12
Message-ID: CAHOFxGrx9=pp2Lcj4MXBTFaYv6DZ5Un+83HcxvxaZKJEXPiNDQ () mail ! gmail ! com
[Download RAW message or body]

On Fri, Oct 25, 2019 at 2:20 PM Alexander Farber <alexander.farber@gmail.com>
wrote:

> Thank you Michael -
>
> 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;

[Attachment #3 (text/html)]

<div dir="ltr"><div dir="ltr">On Fri, Oct 25, 2019 at 2:20 PM Alexander Farber &lt;<a \
href="mailto:alexander.farber@gmail.com">alexander.farber@gmail.com</a>&gt; \
wrote:<br></div><div class="gmail_quote"><blockquote class="gmail_quote" \
style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div dir="ltr">Thank you Michael \
-</div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Fri, Oct 25, \
2019 at 7:28 PM Michael Lewis &lt;<a href="mailto:mlewis@entrata.com" \
target="_blank">mlewis@entrata.com</a>&gt; wrote:<br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div \
class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div \
dir="ltr"><div>But it prints too many records: all moves cross-multiplied with each \
other.<br></div><div><br></div><div>As if I have forgotten to add 1 more condition to \
the JOIN LATERAL<br></div></div></blockquote><div><br></div><div>LIMIT 1 inside your \
lateral should resolve that. Personally, I&#39;d move that condition to EXISTS \
condition inside WHERE clause instead. Just a style thing as far as I know.  \
</div></div></div></blockquote><div><br></div><div>How would you do that with EXISTS? \
I tried, but my query failed to \
compile</div></div></div></blockquote><div><br></div><div>Ooops. I didn&#39;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 &quot;interesting moves&quot; 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.<br></div><div><br></div><div>CREATE INDEX idx_interesting_moves ON words_moves \
USING btree( played ) WHERE action=&#39;play&#39; AND length(letters) = 7 and \
length(hand)=7;</div></div></div>



[prev in list] [next in list] [prev in thread] [next in thread]