From: Dirk Lutzebaeck <lutzeb@aeccom.com>
To: pgsql-performance@postgresql.org
Subject: query produces 1 GB temp file
Message-Id: <20061027111142.DE9163ECE@cayambe.core.aeccom.com>
Date: Sat,  5 Feb 2005 18:25:52 +0100 (CET)


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)