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

List:       postgresql-general
Subject:    Re: Feature request (or at least discussion): enable autovaccum on temp tables
From:       Michael Lewis <mlewis () entrata ! com>
Date:       2019-05-31 17:00:57
Message-ID: CAHOFxGpTna8yf3bHivKNuWYYd2M2YqibWUHbX4NLnhm2u1HzmQ () mail ! gmail ! com
[Download RAW message or body]

>
> So, a related question, since we have dozens of temp tables and a lot of
> code, is there a way to look up what temp tables are being created by the
> current session, so I can do a VACUUM or ANALYZE on all of them in bulk? I
> know I can inspect pg_temp_* schema, but how to figure out which one is
> from the current session?
>

Any table in pg_temp schema would belong to the current session.
Technically the schema has some number appended on the end, but to the
creating connection of those temp tables, it is just pg_temp. Probably you
should be analyzing the temp tables immediately after insert/update/delete,
OR just before first use. As others have said, you have no stats of most
common values or historgrams or any other info unless you analyze. If your
temp tables have many columns which are only selected and not used for
joins or ordering, then you may not need stats on those columns and could
save some cycles by specifying the columns, eg. *analyze
pg_temp.temp_table_name( join_field1, join_field2, order_by_field );*

[Attachment #3 (text/html)]

<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 class="gmail_quote"><div>So, a \
related question, since we have dozens of temp tables and a lot of code, is there a \
way to look up what temp tables are being created by the current session, so I can do \
a VACUUM or ANALYZE on all of them in bulk? I know I can inspect pg_temp_* schema, \
but how to figure out which one is from the current \
session?</div></div></div></blockquote><div><br></div><div>Any table in pg_temp \
schema would belong to the current session. Technically the schema has some number \
appended on the end, but to the creating connection of those temp tables, it is just \
pg_temp. Probably you should be analyzing the temp tables immediately after \
insert/update/delete, OR just before first use. As others have said, you have no \
stats of most common values or historgrams or any other info unless you analyze. If \
your temp tables have many columns which are only selected and not used for joins or \
ordering, then you may not need stats on those columns and could save some cycles by \
specifying the columns, eg. <i>analyze pg_temp.temp_table_name( join_field1, \
join_field2, order_by_field );</i></div></div></div>



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