Re: Analyzing foreign tables & memory problems

From: "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at>
To: "Tom Lane *EXTERN*" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Analyzing foreign tables & memory problems
Date: 2012-04-30 14:59:46
Message-ID: D960CB61B694CF459DCFB4B0128514C207D4F7AB@exadv11.host.magwien.gv.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Tom Lane wrote:
>> During ANALYZE, in analyze.c, functions compute_minimal_stats
>> and compute_scalar_stats, values whose length exceed
>> WIDTH_THRESHOLD (= 1024) are not used for calculating statistics
>> other than that they are counted as "too wide rows" and assumed
>> to be all different.
>
>> This works fine with regular tables; values exceeding that threshold
>> don't get detoasted and won't consume excessive memory.
>
>> With foreign tables the situation is different. Even though
>> values exceeding WIDTH_THRESHOLD won't get used, the complete
>> rows will be fetched from the foreign table. This can easily
>> exhaust maintenance_work_mem.

> I'm fairly skeptical that this is a real problem, and would prefer not
> to complicate wrappers until we see some evidence from the field that
> it's worth worrying about. The WIDTH_THRESHOLD logic was designed a
> dozen years ago when common settings for work_mem were a lot smaller
> than today. Moreover, to my mind it's always been about avoiding
> detoasting operations as much as saving memory, and we don't have
> anything equivalent to that consideration in foreign data wrappers.

If I have a table with 100000 rows and default_statistics_target
at 100, then a sample of 30000 rows will be taken.

If each row contains binary data of 1MB (an Image), then the
data structure returned will use about 30 GB of memory, which
will probably exceed maintenance_work_mem.

Or is there a flaw in my reasoning?

Yours,
Laurenz Albe

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Greg Stark 2012-04-30 15:04:13 Re: Patch: add timing of buffer I/O requests
Previous Message Robert Haas 2012-04-30 14:56:38 Re: default_transaction_isolation = serializable causes crash under Hot Standby