Hi,
I'm sorry but it look like my computer has resent older posts from me,
sigh...
Dirk
Alexander Staubo wrote:
While I can't explain why PostgreSQL would use that
memory, I recommend looking into tweaking the work_mem parameter. This
setting specifies how much memory PostgreSQL on certain temporary data
structures (hash tables, sort vectors) until it starts using temporary
files. Quoting the docs:
work_mem (integer)
Specifies the amount of memory to be used by internal sort operations
and hash tables before switching to temporary disk files. The value is
specified in kilobytes, and defaults to 1024 kilobytes (1 MB). Note
that for a complex query, several sort or hash operations might be
running in parallel; each one will be allowed to use as much memory as
this value specifies before it starts to put data into temporary files.
Also, several running sessions could be doing such operations
concurrently. So the total memory used could be many times the value of
work_mem; it is necessary to keep this fact in mind when choosing the
value. Sort operations are used for ORDER BY, DISTINCT, and merge
joins. Hash tables are used in hash joins, hash-based aggregation, and
hash-based processing of IN subqueries.
Alexander.
On Feb 5, 2005, at 18:25 , Dirk Lutzebaeck wrote:
Hi,
here is a query which produces over 1G temp file in pgsql_tmp. This
is on pgsql 7.4.2, RHEL 3.0, XEON MP machine with 32GB RAM, 300MB
sort_mem and 320MB shared_mem.
Below is the query and results for EXPLAIN and EXPLAIN ANALYZE. All
tables have been analyzed before.
Can some please explain why the temp file is so huge? I understand
there are a lot of rows. All relevant indices seem to be used.
Thanks in advance,
Dirk
EXPLAIN
SELECT DISTINCT ON (ft.val_9, ft.created, ft.flatid) ft.docstart,
ft.flatobj, bi.oid, bi.en
FROM bi, en, df AS ft, es
WHERE bi.rc=130170467
AND bi.en=ft.en
AND bi.co=117305223
AND bi.hide=FALSE
AND ft.en=en.oid
AND es.en=bi.en
AND es.co=bi.co
AND es.spec=122293729
AND (ft.val_2='DG' OR ft.val_2='SK')
AND ft.docstart=1
ORDER BY ft.val_9 ASC, ft.created DESC
LIMIT 1000 OFFSET 0;
Limit (cost=8346.75..8346.78 rows=3 width=1361)
-> Unique (cost=8346.75..8346.78 rows=3 width=1361)
-> Sort (cost=8346.75..8346.76 rows=3 width=1361)
Sort Key: ft.val_9, ft.created, ft.flatid
-> Nested Loop (cost=0.00..8346.73 rows=3
width=1361)
-> Nested Loop (cost=0.00..5757.17 rows=17
width=51)
-> Nested Loop (cost=0.00..5606.39
rows=30 width=42)
-> Index Scan using es_sc_index on
es (cost=0.00..847.71 rows=301 width=8)
Index Cond: ((spec = 122293729)
AND (co = 117305223::oid))
-> Index Scan using bi_env_index
on bi (cost=0.00..15.80 rows=1 width=42)
Index Cond: ("outer".en = bi.en)
Filter: ((rc = 130170467::oid)
AND (co = 117305223::oid) AND (hide = false))
-> Index Scan using en_oid_index on en
(cost=0.00..5.01 rows=1 width=9)
Index Cond: ("outer".en = en.oid)
-> Index Scan using df_en on df ft
(cost=0.00..151.71 rows=49 width=1322)
Index Cond: ("outer".en = ft.en)
Filter: (((val_2 = 'DG'::text) OR (val_2 =
'SK'::text)) AND (docstart = 1))
(17 rows)
--------------
EXPLAIN ANALYZE gives:
Limit (cost=8346.75..8346.78 rows=3 width=1361) (actual
time=75357.465..75679.964 rows=1000 loops=1)
-> Unique (cost=8346.75..8346.78 rows=3 width=1361) (actual
time=75357.459..75675.371 rows=1000 loops=1)
-> Sort (cost=8346.75..8346.76 rows=3 width=1361) (actual
time=75357.448..75499.263 rows=22439 loops=1)
Sort Key: ft.val_9, ft.created, ft.flatid
-> Nested Loop (cost=0.00..8346.73 rows=3
width=1361) (actual time=34.104..18016.005 rows=703677 loops=1)
-> Nested Loop (cost=0.00..5757.17 rows=17
width=51) (actual time=0.467..3216.342 rows=48563 loops=1)
-> Nested Loop (cost=0.00..5606.39
rows=30 width=42) (actual time=0.381..1677.014 rows=48563 loops=1)
-> Index Scan using es_sc_index on
es (cost=0.00..847.71 rows=301 width=8) (actual time=0.184..46.519
rows=5863 loops=1)
Index Cond: ((spec = 122293729)
AND (co = 117305223::oid))
-> Index Scan using bi_env_index
on bi (cost=0.00..15.80 rows=1 width=42) (actual time=0.052..0.218
rows=8 loops=5863)
Index Cond: ("outer".en = bi.en)
Filter: ((rc = 130170467::oid)
AND (co = 117305223::oid) AND (hide = false))
-> Index Scan using en_oid_index on en
(cost=0.00..5.01 rows=1 width=9) (actual time=0.015..0.019 rows=1
loops=48563)
Index Cond: ("outer".en = en.oid)
-> Index Scan using df_en on df ft
(cost=0.00..151.71 rows=49 width=1322) (actual time=0.038..0.148
rows=14 loops=48563)
Index Cond: ("outer".en = ft.en)
Filter: (((val_2 = 'DG'::text) OR (val_2 =
'SK'::text)) AND (docstart = 1))
Total runtime: 81782.052 ms
(18 rows)
---------------------------(end of
broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
|
