[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-admin
Subject: Fwd: Temp tables
From: jose fuenmayor <jafn82 () gmail ! com>
Date: 2020-08-15 0:09:05
Message-ID: CAK74dbnrX5xp7zuUA8Lks-hJunOApvy_M554a1tpOJ_UAR_N3A () mail ! gmail ! com
[Download RAW message or body]
---------- Forwarded message ---------
De: jose fuenmayor <jafn82@gmail.com>
Date: vie., 14 de agosto de 2020 20:07
Subject: Re: Temp tables
To: 2.andriychuk <2.andriychuk@gmail.com>, <psql-admin@postgresql.org>
Could you show me the modified query using with and join??
El vie., 14 de agosto de 2020 20:01, 2.andriychuk <2.andriychuk@gmail.com>
escribió:
> Hi Jose,
>
> If table2 is really big and you run this update on regular base just make
> sure you have a covering index on pair x and field2 with field1 in include
> option, then put both x=z and a.field2=b.field2 to where clause.
> You don't have to use subquery, just table straight forward, index will do
> the trick for you.
>
> Create index <index name> on table2(x, field2) include(field1);
>
> But include is available starting from v11.
>
> If version of your PostgreSQL < 11, use with to express your subquery,
> then use it in join.
>
> Best,
> Igor
>
>
> Sent from my Verizon, Samsung Galaxy smartphone
>
>
> -------- Original message --------
> From: jose fuenmayor <jafn82@gmail.com>
> Date: 8/14/20 16:27 (GMT-08:00)
> To: pgsql-admin@postgresql.org
> Subject: Temp tables
>
> Hi all , i have a doubt wich query performs better
> Update table a set field1=b.field 1
> From (select field1,field2
>
> From table2 where x=z
> ) b
> Where a.field2=b.field2
>
> Or
>
> Create temp table y as
> select field1,field2
> From table2 where x=z;
>
> Update table a
> set field1=b.field1
> From y as b
> Where a.field2=b.field2
>
> I ve been told not to use subquerys, but i think this refers to
>
> Select * from table where field1 in (select field1 from table2)
>
> Any thoughts?
> Thanks people y'all
>
[Attachment #3 (text/html)]
<div dir="auto"></div><br><div class="gmail_quote"><div dir="ltr" \
class="gmail_attr">---------- Forwarded message ---------<br>De: <strong \
class="gmail_sendername" dir="auto">jose fuenmayor</strong> <span dir="auto"><<a \
href="mailto:jafn82@gmail.com">jafn82@gmail.com</a>></span><br>Date: vie., 14 de \
agosto de 2020 20:07<br>Subject: Re: Temp tables<br>To: 2.andriychuk <<a \
href="mailto:2.andriychuk@gmail.com">2.andriychuk@gmail.com</a>>, <<a \
href="mailto:psql-admin@postgresql.org">psql-admin@postgresql.org</a>><br></div><br><br><div \
dir="auto">Could you show me the modified query using with and join??</div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">El vie., 14 de agosto de 2020 \
20:01, 2.andriychuk <<a href="mailto:2.andriychuk@gmail.com" target="_blank" \
rel="noreferrer">2.andriychuk@gmail.com</a>> escribió:<br></div><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="auto"> <div dir="auto">Hi Jose,</div><div \
dir="auto"><br></div><div dir="auto">If table2 is really big and you run this update \
on regular base just make sure you have a covering index on pair x and field2 with \
field1 in include option, then put both x=z and a.field2=b.field2 to where clause. \
</div><div dir="auto">You don't have to use subquery, just table straight \
forward, index will do the trick for you.</div><div dir="auto"><br></div><div \
dir="auto">Create index <index name> on table2(x, field2) \
include(field1);</div><div dir="auto"><br></div><div dir="auto">But include is \
available starting from v11. </div><div dir="auto"><br></div><div dir="auto">If \
version of your PostgreSQL < 11, use with to express your subquery, then use it in \
join.</div><div dir="auto"><br></div><div dir="auto">Best,</div><div \
dir="auto">Igor</div><div dir="auto"><br></div><div dir="auto"><br></div><div \
id="m_-7002088116744665468m_8480789356813073033composer_signature" dir="auto"><div \
style="font-size:85%;color:#575757" dir="auto">Sent from my Verizon, Samsung Galaxy \
smartphone</div></div><div dir="auto"><br></div><div><br></div><div dir="auto" \
style="font-size:100%;color:#000000"><div>-------- Original message \
--------</div><div>From: jose fuenmayor <<a href="mailto:jafn82@gmail.com" \
rel="noreferrer noreferrer" target="_blank">jafn82@gmail.com</a>> </div><div>Date: \
8/14/20 16:27 (GMT-08:00) </div><div>To: <a \
href="mailto:pgsql-admin@postgresql.org" rel="noreferrer noreferrer" \
target="_blank">pgsql-admin@postgresql.org</a> </div><div>Subject: Temp tables \
</div><div><br></div></div><div dir="auto"><span \
style="font-family:sans-serif;font-size:12.8px">Hi all , i have a doubt wich query \
performs better</span><div style="font-family:sans-serif;font-size:12.8px" \
dir="auto">Update table a set field1=b.field 1</div><div \
style="font-family:sans-serif;font-size:12.8px" dir="auto">From (select \
field1,field2</div><div style="font-family:sans-serif;font-size:12.8px" \
dir="auto"><br></div><div style="font-family:sans-serif;font-size:12.8px" \
dir="auto">From table2 where x=z</div><div \
style="font-family:sans-serif;font-size:12.8px" dir="auto">) b</div><div \
style="font-family:sans-serif;font-size:12.8px" dir="auto">Where \
a.field2=b.field2</div><div style="font-family:sans-serif;font-size:12.8px" \
dir="auto"><br></div><div style="font-family:sans-serif;font-size:12.8px" \
dir="auto">Or</div><div style="font-family:sans-serif;font-size:12.8px" \
dir="auto"><br></div><div style="font-family:sans-serif;font-size:12.8px" \
dir="auto">Create temp table y as</div><div \
style="font-family:sans-serif;font-size:12.8px" dir="auto"><div dir="auto">select \
field1,field2</div><div dir="auto">From table2 where x=z;</div><div \
dir="auto"><br></div><div dir="auto">Update table a </div><div dir="auto">set \
field1=b.field1<br></div><div dir="auto">From y as b</div><div dir="auto">Where \
a.field2=b.field2<br></div><div dir="auto"><br></div><div dir="auto">I ve been told \
not to use subquerys, but i think this refers to</div><div dir="auto"><br></div><div \
dir="auto">Select * from table where field1 in (select field1 from table2)</div><div \
dir="auto"><br></div><div dir="auto">Any thoughts?</div><div dir="auto">Thanks people \
y'all</div></div></div> </div></blockquote></div>
</div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic