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

List:       pgsql-performance
Subject:    Re: Why Postgres doesn't use TID scan?
From:       Rick Otten <rottenwindfish () gmail ! com>
Date:       2018-12-20 13:46:04
Message-ID: CAMAYy4KC+epqkZLgxgyv+hy9Cwp+RwjuaO_7mb0HhPZC+74TrQ () mail ! gmail ! com
[Download RAW message or body]

On Wed, Dec 19, 2018 at 6:45 PM Vladimir Ryabtsev <greatvovan@gmail.com>
wrote:

> > The fundamental issue is that "ANY" has two meanings in PG, one of them
> following the SQL standard and one not:
>
> Oh yes, I was aware about two forms but it did not come into my mind, I
> was thinking I use the same form in both cases since my query returns only
> one row and column.
> Thanks for pointing me into that.
>
> --
> Vlad
>

For what it is worth, I have found that if I am checking for the presence
of an object in an array, while this syntax is easy to understand and more
intuitive to craft:

    select
      *
    from
      mytable
    where
      ' test' = ANY (my_varchar_array_column)
    ;

This syntax is almost always much faster:

    select
        *
    from
        mytable
    where
      ARRAY['test'::varchar] <@ my_varchar_array_column
    ;

(Since this is a performance list after all.)

[Attachment #3 (text/html)]

<div dir="ltr"><div dir="ltr"><div dir="ltr"><br><br><div class="gmail_quote"><div \
dir="ltr">On Wed, Dec 19, 2018 at 6:45 PM Vladimir Ryabtsev &lt;<a \
href="mailto:greatvovan@gmail.com">greatvovan@gmail.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>&gt; The fundamental issue is that &quot;ANY&quot; has two meanings in \
PG, one of them following the SQL standard and one not:</div><div><br></div>Oh yes, I \
was aware about two forms but it did not come into my mind, I was thinking I use the \
same form in both cases since my query returns only one row and column.<div>Thanks \
for pointing me into \
that.</div><div><br></div><div>--</div><div>Vlad</div></div></blockquote><div><br></div><div>For \
what it is worth, I have found that if I am checking for the presence of an object in \
an array, while this syntax is easy to understand and more intuitive to \
craft:</div><div><div><br></div><div>      select</div><div>         *</div><div>     \
from</div><div>         mytable  </div><div>      where</div><div>         &#39; \
test&#39; = ANY (my_varchar_array_column)</div><div>      \
;</div></div><div><br></div><div>This syntax is almost always much \
faster:<br></div><div><div><br></div><div>      select</div><div>            \
*</div><div>      from</div><div>            mytable</div><div>      where</div><div> \
ARRAY[&#39;test&#39;::varchar] &lt;@ my_varchar_array_column</div><div>      \
;</div></div><div><br></div><div>(Since this is a performance list after \
all.)</div><div><br></div></div></div></div></div>



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