pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Jaime Casanova <jcasanov@systemguards.com.ec>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: decibel <decibel@decibel.org>
Cc: psql performance list <pgsql-performance@postgresql.org>
Subject: Re: [PERFORMANCE] work_mem vs temp files issue
Date: Mon, 11 Jan 2010 16:11:50 -0500
Message-ID: <3073cc9b1001111311k7af04b71gd4104e07913bc349@mail.gmail.com> (raw )
In-Reply-To: <18803.1263241120@sss.pgh.pa.us >
References: <3073cc9b0908191745o3dc63d95q6b9308ef5bd199f2@mail.gmail.com >
<B43373EF-4CFC-4643-AB6E-5BC0D4A7EB58@decibel.org >
<25315.1252881420@sss.pgh.pa.us >
<3073cc9b1001111015t3888cbbex7171d61b952218da@mail.gmail.com >
<603c8f071001111107v75276375g4d626dfc0efd8dc4@mail.gmail.com >
<3073cc9b1001111114j7ddacd56p8966f9d61997a8b@mail.gmail.com >
<3073cc9b1001111116x53427c9bm1e448128a3b4f6c6@mail.gmail.com >
<18803.1263241120@sss.pgh.pa.us >
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)
view thread (16+ messages) latest in thread
Message-ID: <3073cc9b1001111311k7af04b71gd4104e07913bc349@mail.gmail.com>
Permalink: ../3073cc9b1001111311k7af04b71gd4104e07913bc349@mail.gmail.com/
Also on: postgresql.org/message-id/3073cc9b1001111311k7af04b71gd4104e07913bc349@mail.gmail.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jcasanov@systemguards.com.ec, tgl@sss.pgh.pa.us, robertmhaas@gmail.com, decibel@decibel.org
Subject: Re: [PERFORMANCE] work_mem vs temp files issue
In-Reply-To: <3073cc9b1001111311k7af04b71gd4104e07913bc349@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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