Re: BUG #18573: Analyze command consumes several GB of memory - more than analyzed table size - Mailing list pgsql-bugs

what is the size of table?

2024년 8월 8일 (목) 오후 1:31, Aleš Zelený <zeleny.ales@gmail.com>님이 작성:

Hello,

the default statistics target is:

powa=# show default_statistics_target ;
 default_statistics_target
---------------------------
 2500
(1 row)

So I've changed it to the default:

powa=# ALTER TABLE testcase_t3 ALTER COLUMN records SET STATISTICS 100;
ALTER TABLE

The memory consumption drops down to ~ 1.1GB

So you were right, thanks!

I thought it was a bug, but possibly it is only a space for an improvement for adding a potential limit for similar cases.

Kind regards Ales Zeleny

st 7. 8. 2024 v 16:46 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

PG Bug reporting form <noreply@postgresql.org> writes:
> Is there more I can do to identify why analyze uses that much memory?

What have you got default_statistics_target set to?

You might need to decrease the stats target for that composite-array
column.  compute_array_stats is fairly aggressive about how much
data it will try to collect, and I can believe that that'd add up
when the array elements are of a wide composite type.

                        regards, tom lane

--

Muhammad Waqas

Senior Technical Support Engineer - Tech Support Center (Karachi)

Mobile: +92-322-2844150

Email:  waqas.m@bitnine.net 

1st floor, suit no F-04, COLABS Karachi, 8-C Khayaban-e-Tanzeem, 

DHA Phase 5 Tauheed Commercial Area,

Defense V Defense Housing Authority, Karachi, Karachi City, Sindh, 

75500 Pakistan.