pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [PERFORMANCE] work_mem vs temp files issue
16+ messages / 3 participants
[nested] [flat]
* Re: [PERFORMANCE] work_mem vs temp files issue
@ 2010-01-11 18:15 Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:07 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Jaime Casanova @ 2010-01-11 18:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: decibel <decibel@decibel.org>; pgsql-performance
On Sun, Sep 13, 2009 at 5:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> It might be useful to turn on trace_sort to see if the small files
> are coming from sorts. If they're from hashes I'm afraid there's
> no handy instrumentation ...
>
yes they are, this is the log (i deleted the STATEMENT lines because
they were redundant), seems like all the temp files are used to
execute the same sentence...
BTW, this is my laptop no the server.
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [PERFORMANCE] work_mem vs temp files issue
2010-01-11 18:15 Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
@ 2010-01-11 19:07 ` Robert Haas <robertmhaas@gmail.com>
2010-01-11 19:14 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
0 siblings, 1 reply; 16+ messages in thread
From: Robert Haas @ 2010-01-11 19:07 UTC (permalink / raw)
To: Jaime Casanova <jcasanov@systemguards.com.ec>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; decibel <decibel@decibel.org>; pgsql-performance
On Mon, Jan 11, 2010 at 1:15 PM, Jaime Casanova
<jcasanov@systemguards.com.ec> wrote:
> On Sun, Sep 13, 2009 at 5:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>> It might be useful to turn on trace_sort to see if the small files
>> are coming from sorts. If they're from hashes I'm afraid there's
>> no handy instrumentation ...
>>
>
> yes they are, this is the log (i deleted the STATEMENT lines because
> they were redundant), seems like all the temp files are used to
> execute the same sentence...
>
> BTW, this is my laptop no the server.
I think maybe there was supposed to be an attachment here?
...Robert
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [PERFORMANCE] work_mem vs temp files issue
2010-01-11 18:15 Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:07 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
@ 2010-01-11 19:14 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:16 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
0 siblings, 1 reply; 16+ messages in thread
From: Jaime Casanova @ 2010-01-11 19:14 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; decibel <decibel@decibel.org>; pgsql-performance
On Mon, Jan 11, 2010 at 2:07 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Mon, Jan 11, 2010 at 1:15 PM, Jaime Casanova
> <jcasanov@systemguards.com.ec> wrote:
>> On Sun, Sep 13, 2009 at 5:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>
>>> It might be useful to turn on trace_sort to see if the small files
>>> are coming from sorts. If they're from hashes I'm afraid there's
>>> no handy instrumentation ...
>>>
>>
>> yes they are, this is the log (i deleted the STATEMENT lines because
>> they were redundant), seems like all the temp files are used to
>> execute the same sentence...
>>
>> BTW, this is my laptop no the server.
>
> I think maybe there was supposed to be an attachment here?
>
i knew i was forgotting something ;)
ah! and this is in 8.5dev but it's the same in 8.3
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [PERFORMANCE] work_mem vs temp files issue
2010-01-11 18:15 Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:07 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
2010-01-11 19:14 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
@ 2010-01-11 19:16 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 20:18 ` Re: [PERFORMANCE] work_mem vs temp files issue Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 16+ messages in thread
From: Jaime Casanova @ 2010-01-11 19:16 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; decibel <decibel@decibel.org>; pgsql-performance
On Mon, Jan 11, 2010 at 2:14 PM, Jaime Casanova
<jcasanov@systemguards.com.ec> wrote:
> On Mon, Jan 11, 2010 at 2:07 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Mon, Jan 11, 2010 at 1:15 PM, Jaime Casanova
>> <jcasanov@systemguards.com.ec> wrote:
>>> On Sun, Sep 13, 2009 at 5:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>>
>>>> It might be useful to turn on trace_sort to see if the small files
>>>> are coming from sorts. If they're from hashes I'm afraid there's
>>>> no handy instrumentation ...
>>>>
>>>
>>> yes they are, this is the log (i deleted the STATEMENT lines because
>>> they were redundant), seems like all the temp files are used to
>>> execute the same sentence...
>>>
>>> BTW, this is my laptop no the server.
>>
>> I think maybe there was supposed to be an attachment here?
>>
>
> i knew i was forgotting something ;)
> ah! and this is in 8.5dev but it's the same in 8.3
>
oh! boy this can't be happen!
attaching again
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
Attachments:
log (application/octet-stream, 1.9K) download
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [PERFORMANCE] work_mem vs temp files issue
2010-01-11 18:15 Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:07 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
2010-01-11 19:14 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:16 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
@ 2010-01-11 20:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-01-11 21:11 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-13 06:31 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
0 siblings, 2 replies; 16+ messages in thread
From: Tom Lane @ 2010-01-11 20:18 UTC (permalink / raw)
To: Jaime Casanova <jcasanov@systemguards.com.ec>; +Cc: Robert Haas <robertmhaas@gmail.com>; decibel <decibel@decibel.org>; pgsql-performance
Jaime Casanova <jcasanov@systemguards.com.ec> writes:
> LOG: begin tuple sort: nkeys = 1, workMem = 1024, randomAccess = f
> LOG: switching to bounded heapsort at 641 tuples: CPU 0.08s/0.13u sec elapsed 0.25 sec
> LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp8507.5", size 471010
> LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp8507.10", size 81096
> LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp8507.0", size 467373
> LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp8507.9", size 110200
> LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp8507.3", size 470011
> LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp8507.8", size 157192
> LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp8507.4", size 468681
> LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp8507.12", size 101624
> LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp8507.1", size 472285
> LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp8507.11", size 100744
> LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp8507.6", size 467173
> LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp8507.7", size 141888
> LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp8507.2", size 476227
> LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp8507.13", size 89072
> LOG: performsort starting: CPU 0.10s/0.19u sec elapsed 0.33 sec
> LOG: performsort done: CPU 0.10s/0.19u sec elapsed 0.33 sec
> LOG: internal sort ended, 118 KB used: CPU 0.10s/0.19u sec elapsed 0.33 sec
Hmm. Not clear where the temp files are coming from, but it's *not* the
sort --- the "internal sort ended" line shows that that sort never went
to disk. What kind of plan is feeding the sort node?
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [PERFORMANCE] work_mem vs temp files issue
2010-01-11 18:15 Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:07 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
2010-01-11 19:14 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:16 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 20:18 ` Re: [PERFORMANCE] work_mem vs temp files issue Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-01-11 21:11 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 21:36 ` Re: [PERFORMANCE] work_mem vs temp files issue Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 16+ messages in thread
From: Jaime Casanova @ 2010-01-11 21:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Robert Haas <robertmhaas@gmail.com>; decibel <decibel@decibel.org>; pgsql-performance
On Mon, Jan 11, 2010 at 3:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jaime Casanova <jcasanov@systemguards.com.ec> writes:
>> LOG: begin tuple sort: nkeys = 1, workMem = 1024, randomAccess = f
>> LOG: switching to bounded heapsort at 641 tuples: CPU 0.08s/0.13u sec elapsed 0.25 sec
>> LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp8507.5", size 471010
[... some more temp files logged ...]
>> LOG: internal sort ended, 118 KB used: CPU 0.10s/0.19u sec elapsed 0.33 sec
>
> Hmm. Not clear where the temp files are coming from, but it's *not* the
> sort --- the "internal sort ended" line shows that that sort never went
> to disk. What kind of plan is feeding the sort node?
>
i'm sure i have seen on disk sorts even when the files are small, but
still i see a problem here...
the temp files shoul be coming from hash operations but AFAICS the
files are small and every hash operation should be using until
work_mem memory, right?
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
Attachments:
explain_sort.txt (text/plain, 2.3K) download
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
-----------------
Limit (cost=9090.60..9090.65 rows=20 width=166) (actual time=441.619..441.674 rows=20 loops=1)
-> Sort (cost=9089.85..9106.26 rows=6567 width=166) (actual time=441.062..441.362 rows=320 loops=1)
Sort Key: tcf.fech_hora
Sort Method: top-N heapsort Memory: 118kB
-> Hash Join (cost=6660.18..8783.76 rows=6567 width=166) (actual time=310.561..427.393 rows=6511 loops=1)
Hash Cond: (tcsc.id_soli_compra = tcf.id_soli_compra)
-> Hash Join (cost=5600.47..7551.97 rows=6548 width=158) (actual time=281.279..378.434 rows=6544 loops=1)
Hash Cond: (tcsc.esta_solicitud = tgdc.seq_det_cat)
-> Hash Join (cost=5577.86..7439.33 rows=6548 width=147) (actual time=279.590..361.704 rows=6545 loops=1)
Hash Cond: ((tcsc.persona_id)::numeric = tgp.persona_id)
-> Seq Scan on tcom_solicitud_compra tcsc (cost=0.00..799.77 rows=6548 width=125) (actual time=0.013..18.433 ro
ws=6545 loops=1)
Filter: ((esta_solicitud <> 425) AND (esta_solicitud <> 424) AND (esta_solicitud <> 416))
-> Hash (cost=3878.94..3878.94 rows=83594 width=34) (actual time=278.837..278.837 rows=83594 loops=1)
-> Seq Scan on tgen_persona tgp (cost=0.00..3878.94 rows=83594 width=34) (actual time=0.006..136.582 rows
=83594 loops=1)
-> Hash (cost=14.49..14.49 rows=649 width=15) (actual time=1.657..1.657 rows=649 loops=1)
-> Seq Scan on tgen_det_cat tgdc (cost=0.00..14.49 rows=649 width=15) (actual time=0.008..0.803 rows=649 loops=
1)
-> Hash (cost=977.62..977.62 rows=6567 width=16) (actual time=29.264..29.264 rows=6556 loops=1)
-> Seq Scan on tcom_fecha tcf (cost=0.00..977.62 rows=6567 width=16) (actual time=0.016..20.532 rows=6556 loops=1)
Filter: (seq_det_cat = 388)
Total runtime: 443.434 ms
(20 rows)
* Re: [PERFORMANCE] work_mem vs temp files issue
2010-01-11 18:15 Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:07 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
2010-01-11 19:14 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:16 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 20:18 ` Re: [PERFORMANCE] work_mem vs temp files issue Tom Lane <tgl@sss.pgh.pa.us>
2010-01-11 21:11 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
@ 2010-01-11 21:36 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 16+ messages in thread
From: Tom Lane @ 2010-01-11 21:36 UTC (permalink / raw)
To: Jaime Casanova <jcasanov@systemguards.com.ec>; +Cc: Robert Haas <robertmhaas@gmail.com>; decibel <decibel@decibel.org>; pgsql-performance
Jaime Casanova <jcasanov@systemguards.com.ec> writes:
> the temp files shoul be coming from hash operations but AFAICS the
> files are small and every hash operation should be using until
> work_mem memory, right?
No, when a hash spills to disk the code has to guess the partition sizes
(number of buckets per partition) in advance. So it wouldn't be at all
surprising if the actual sizes come out substantially different from
work_mem.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [PERFORMANCE] work_mem vs temp files issue
2010-01-11 18:15 Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:07 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
2010-01-11 19:14 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:16 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 20:18 ` Re: [PERFORMANCE] work_mem vs temp files issue Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-01-13 06:31 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-13 14:45 ` Re: [PERFORMANCE] work_mem vs temp files issue Tom Lane <tgl@sss.pgh.pa.us>
2010-01-13 15:23 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
1 sibling, 2 replies; 16+ messages in thread
From: Jaime Casanova @ 2010-01-13 06:31 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Robert Haas <robertmhaas@gmail.com>; decibel <decibel@decibel.org>; pgsql-performance
On Mon, Jan 11, 2010 at 3:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Hmm. Not clear where the temp files are coming from, but it's *not* the
> sort --- the "internal sort ended" line shows that that sort never went
> to disk. What kind of plan is feeding the sort node?
>
some time ago, you said:
"""
It might be useful to turn on trace_sort to see if the small files
are coming from sorts. If they're from hashes I'm afraid there's
no handy instrumentation ...
"""
and is clearly what was bother me... because most of all temp files
are coming from hash...
why we don't show some of that info in explain? for example: we can
show memory used, no? or if the hash goes to disk... if i remove
#ifdef HJDEBUG seems like we even know how many batchs the hash
used...
the reason i say "most of the temp files" is that when i removed
#ifdef HJDEBUG it says that in total i was using 10 batchs but there
were 14 temp files created (i guess we use 1 file per batch, no?)
"""
nbatch = 1, nbuckets = 1024
nbatch = 1, nbuckets = 1024
nbatch = 8, nbuckets = 2048
"""
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [PERFORMANCE] work_mem vs temp files issue
2010-01-11 18:15 Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:07 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
2010-01-11 19:14 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:16 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 20:18 ` Re: [PERFORMANCE] work_mem vs temp files issue Tom Lane <tgl@sss.pgh.pa.us>
2010-01-13 06:31 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
@ 2010-01-13 14:45 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 16+ messages in thread
From: Tom Lane @ 2010-01-13 14:45 UTC (permalink / raw)
To: Jaime Casanova <jcasanov@systemguards.com.ec>; +Cc: Robert Haas <robertmhaas@gmail.com>; decibel <decibel@decibel.org>; pgsql-performance
Jaime Casanova <jcasanov@systemguards.com.ec> writes:
> why we don't show some of that info in explain?
Lack of round tuits; plus concern about breaking programs that read
EXPLAIN output, which I guess will be alleviated in 8.5.
> the reason i say "most of the temp files" is that when i removed
> #ifdef HJDEBUG it says that in total i was using 10 batchs but there
> were 14 temp files created (i guess we use 1 file per batch, no?)
Two files per batch, in general --- I suppose some of the buckets
were empty.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [PERFORMANCE] work_mem vs temp files issue
2010-01-11 18:15 Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:07 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
2010-01-11 19:14 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:16 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 20:18 ` Re: [PERFORMANCE] work_mem vs temp files issue Tom Lane <tgl@sss.pgh.pa.us>
2010-01-13 06:31 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
@ 2010-01-13 15:23 ` Robert Haas <robertmhaas@gmail.com>
2010-01-13 15:42 ` Re: [PERFORMANCE] work_mem vs temp files issue Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 16+ messages in thread
From: Robert Haas @ 2010-01-13 15:23 UTC (permalink / raw)
To: Jaime Casanova <jcasanov@systemguards.com.ec>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; decibel <decibel@decibel.org>; pgsql-performance
On Wed, Jan 13, 2010 at 1:31 AM, Jaime Casanova
<jcasanov@systemguards.com.ec> wrote:
> On Mon, Jan 11, 2010 at 3:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>> Hmm. Not clear where the temp files are coming from, but it's *not* the
>> sort --- the "internal sort ended" line shows that that sort never went
>> to disk. What kind of plan is feeding the sort node?
>>
>
> some time ago, you said:
> """
> It might be useful to turn on trace_sort to see if the small files
> are coming from sorts. If they're from hashes I'm afraid there's
> no handy instrumentation ...
> """
>
> and is clearly what was bother me... because most of all temp files
> are coming from hash...
>
> why we don't show some of that info in explain? for example: we can
> show memory used, no? or if the hash goes to disk... if i remove
> #ifdef HJDEBUG seems like we even know how many batchs the hash
> used...
I had an idea at one point of making explain show the planned and
actual # of batches for each hash join. I believe that "actual # of
batches > 1" is isomorphic to "hash join went to disk". The code is
actually pretty easy; the hard part is figuring out what to do about
the UI. The choices seem to be:
1. Create a new EXPLAIN option just for this - what would we call it?
2. Think of some more, similar things and come up with a new EXPLAIN
option covering all of them - what else would go along with?
3. Sandwhich it into an existing EXPLAIN option, most likely VERBOSE.
4. Display it by default.
...Robert
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [PERFORMANCE] work_mem vs temp files issue
2010-01-11 18:15 Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:07 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
2010-01-11 19:14 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:16 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 20:18 ` Re: [PERFORMANCE] work_mem vs temp files issue Tom Lane <tgl@sss.pgh.pa.us>
2010-01-13 06:31 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-13 15:23 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
@ 2010-01-13 15:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-01-13 16:11 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Tom Lane @ 2010-01-13 15:42 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Jaime Casanova <jcasanov@systemguards.com.ec>; decibel <decibel@decibel.org>; pgsql-performance
Robert Haas <robertmhaas@gmail.com> writes:
> I had an idea at one point of making explain show the planned and
> actual # of batches for each hash join. I believe that "actual # of
> batches > 1" is isomorphic to "hash join went to disk". The code is
> actually pretty easy; the hard part is figuring out what to do about
> the UI. The choices seem to be:
> 1. Create a new EXPLAIN option just for this - what would we call it?
> 2. Think of some more, similar things and come up with a new EXPLAIN
> option covering all of them - what else would go along with?
> 3. Sandwhich it into an existing EXPLAIN option, most likely VERBOSE.
> 4. Display it by default.
Treat it the same as the Sort-node actual usage information. We did not
add a special option when we added that.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [PERFORMANCE] work_mem vs temp files issue
2010-01-11 18:15 Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:07 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
2010-01-11 19:14 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:16 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 20:18 ` Re: [PERFORMANCE] work_mem vs temp files issue Tom Lane <tgl@sss.pgh.pa.us>
2010-01-13 06:31 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-13 15:23 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
2010-01-13 15:42 ` Re: [PERFORMANCE] work_mem vs temp files issue Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-01-13 16:11 ` Robert Haas <robertmhaas@gmail.com>
2010-01-13 16:14 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
0 siblings, 1 reply; 16+ messages in thread
From: Robert Haas @ 2010-01-13 16:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jaime Casanova <jcasanov@systemguards.com.ec>; decibel <decibel@decibel.org>; pgsql-performance
On Wed, Jan 13, 2010 at 10:42 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> I had an idea at one point of making explain show the planned and
>> actual # of batches for each hash join. I believe that "actual # of
>> batches > 1" is isomorphic to "hash join went to disk". The code is
>> actually pretty easy; the hard part is figuring out what to do about
>> the UI. The choices seem to be:
>
>> 1. Create a new EXPLAIN option just for this - what would we call it?
>> 2. Think of some more, similar things and come up with a new EXPLAIN
>> option covering all of them - what else would go along with?
>> 3. Sandwhich it into an existing EXPLAIN option, most likely VERBOSE.
>> 4. Display it by default.
>
> Treat it the same as the Sort-node actual usage information. We did not
> add a special option when we added that.
Well, what about when we're just doing EXPLAIN, not EXPLAIN ANALYZE?
It'll add another line to the output for the expected number of
batches.
...Robert
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [PERFORMANCE] work_mem vs temp files issue
2010-01-11 18:15 Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:07 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
2010-01-11 19:14 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:16 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 20:18 ` Re: [PERFORMANCE] work_mem vs temp files issue Tom Lane <tgl@sss.pgh.pa.us>
2010-01-13 06:31 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-13 15:23 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
2010-01-13 15:42 ` Re: [PERFORMANCE] work_mem vs temp files issue Tom Lane <tgl@sss.pgh.pa.us>
2010-01-13 16:11 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
@ 2010-01-13 16:14 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-13 16:24 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Jaime Casanova @ 2010-01-13 16:14 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; decibel <decibel@decibel.org>; pgsql-performance
On Wed, Jan 13, 2010 at 11:11 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>
> Well, what about when we're just doing EXPLAIN, not EXPLAIN ANALYZE?
> It'll add another line to the output for the expected number of
> batches.
>
and when we are in EXPLAIN ANALYZE the real number as well?
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [PERFORMANCE] work_mem vs temp files issue
2010-01-11 18:15 Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:07 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
2010-01-11 19:14 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:16 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 20:18 ` Re: [PERFORMANCE] work_mem vs temp files issue Tom Lane <tgl@sss.pgh.pa.us>
2010-01-13 06:31 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-13 15:23 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
2010-01-13 15:42 ` Re: [PERFORMANCE] work_mem vs temp files issue Tom Lane <tgl@sss.pgh.pa.us>
2010-01-13 16:11 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
2010-01-13 16:14 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
@ 2010-01-13 16:24 ` Robert Haas <robertmhaas@gmail.com>
2010-01-13 16:53 ` Re: [PERFORMANCE] work_mem vs temp files issue Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 16+ messages in thread
From: Robert Haas @ 2010-01-13 16:24 UTC (permalink / raw)
To: Jaime Casanova <jcasanov@systemguards.com.ec>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; decibel <decibel@decibel.org>; pgsql-performance
On Wed, Jan 13, 2010 at 11:14 AM, Jaime Casanova
<jcasanov@systemguards.com.ec> wrote:
> On Wed, Jan 13, 2010 at 11:11 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>> Well, what about when we're just doing EXPLAIN, not EXPLAIN ANALYZE?
>> It'll add another line to the output for the expected number of
>> batches.
>
> and when we are in EXPLAIN ANALYZE the real number as well?
Yeah. My question is whether it's acceptable to add an extra line to
the EXPLAIN output for every hash join, even w/o ANALYZE.
...Robert
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [PERFORMANCE] work_mem vs temp files issue
2010-01-11 18:15 Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:07 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
2010-01-11 19:14 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:16 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 20:18 ` Re: [PERFORMANCE] work_mem vs temp files issue Tom Lane <tgl@sss.pgh.pa.us>
2010-01-13 06:31 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-13 15:23 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
2010-01-13 15:42 ` Re: [PERFORMANCE] work_mem vs temp files issue Tom Lane <tgl@sss.pgh.pa.us>
2010-01-13 16:11 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
2010-01-13 16:14 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-13 16:24 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
@ 2010-01-13 16:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-01-13 17:02 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Tom Lane @ 2010-01-13 16:53 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Jaime Casanova <jcasanov@systemguards.com.ec>; decibel <decibel@decibel.org>; pgsql-performance
Robert Haas <robertmhaas@gmail.com> writes:
> Yeah. My question is whether it's acceptable to add an extra line to
> the EXPLAIN output for every hash join, even w/o ANALYZE.
We could add it if either VERBOSE or ANALYZE appears. Not sure if
that's just too much concern for backwards compatibility, though.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [PERFORMANCE] work_mem vs temp files issue
2010-01-11 18:15 Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:07 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
2010-01-11 19:14 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:16 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 20:18 ` Re: [PERFORMANCE] work_mem vs temp files issue Tom Lane <tgl@sss.pgh.pa.us>
2010-01-13 06:31 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-13 15:23 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
2010-01-13 15:42 ` Re: [PERFORMANCE] work_mem vs temp files issue Tom Lane <tgl@sss.pgh.pa.us>
2010-01-13 16:11 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
2010-01-13 16:14 ` Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-13 16:24 ` Re: [PERFORMANCE] work_mem vs temp files issue Robert Haas <robertmhaas@gmail.com>
2010-01-13 16:53 ` Re: [PERFORMANCE] work_mem vs temp files issue Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-01-13 17:02 ` Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 16+ messages in thread
From: Robert Haas @ 2010-01-13 17:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jaime Casanova <jcasanov@systemguards.com.ec>; decibel <decibel@decibel.org>; pgsql-performance
On Wed, Jan 13, 2010 at 11:53 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> Yeah. My question is whether it's acceptable to add an extra line to
>> the EXPLAIN output for every hash join, even w/o ANALYZE.
>
> We could add it if either VERBOSE or ANALYZE appears. Not sure if
> that's just too much concern for backwards compatibility, though.
I think having it controlled by either of two options is to weird.
I'm not worried so much about backward compatibility as I am about
cluttering the output. Maybe making it controlled by VERBOSE is the
right thing to do, although I'm sort of tempted to figure out if there
is more useful instrumentation that could be done and put it all under
a new option called, say, HASH_DETAILS. Not sure what else we could
show though.
...Robert
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2010-01-13 17:02 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-01-11 18:15 Re: [PERFORMANCE] work_mem vs temp files issue Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:07 ` Robert Haas <robertmhaas@gmail.com>
2010-01-11 19:14 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 19:16 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 20:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-01-11 21:11 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-11 21:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-01-13 06:31 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-13 14:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-01-13 15:23 ` Robert Haas <robertmhaas@gmail.com>
2010-01-13 15:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-01-13 16:11 ` Robert Haas <robertmhaas@gmail.com>
2010-01-13 16:14 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2010-01-13 16:24 ` Robert Haas <robertmhaas@gmail.com>
2010-01-13 16:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-01-13 17:02 ` Robert Haas <robertmhaas@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox