I've got a query which causes PostgreSQL to create hundreds of thousands
of temporary files, many of them empty. The process also needs a lot of
memory. I suspect this is due to bookkeeping for those files.
The query looks like this:
SELECT ef.arch::text, en.name, file_id, f.name
FROM symboldb.package_set_member psm
JOIN symboldb.file f USING (package_id)
JOIN symboldb.elf_file ef USING (contents_id)
JOIN symboldb.elf_needed en USING (contents_id)
WHERE psm.set_id = 36;
The query plan looks like this:
Hash Join (cost=3153618.94..7866324.29 rows=86653612 width=84)
Hash Cond: (f.contents_id = ef.contents_id)
-> Hash Join (cost=2349021.27..5624009.06 rows=5647704 width=68)
Hash Cond: (psm.package_id = f.package_id)
-> Bitmap Heap Scan on package_set_member psm
(cost=495.42..5385.79 rows=33870 width=4)
Recheck Cond: (set_id = 36)
-> Bitmap Index Scan on package_set_member_set_id_idx
(cost=0.00..486.95 rows=33870 width=0)
Index Cond: (set_id = 36)
-> Hash (cost=1252310.60..1252310.60 rows=45263060 width=72)
-> Seq Scan on file f (cost=0.00..1252310.60
rows=45263060 width=72)
-> Hash (cost=592698.57..592698.57 rows=10958808 width=28)
-> Hash Join (cost=51493.85..592698.57 rows=10958808 width=28)
Hash Cond: (en.contents_id = ef.contents_id)
-> Seq Scan on elf_needed en (cost=0.00..202333.08
rows=10958808 width=20)
-> Hash (cost=30622.82..30622.82 rows=1272082 width=8)
-> Seq Scan on elf_file ef (cost=0.00..30622.82
rows=1272082 width=8)
This happens with postgresql-server-9.3.2-2.fc20.x86_64.
I track this down to a lower-than-usual setting of work_mem, to 1MB,
after the upgrade to 9.3.
With work_mem set to 8MB, the query completes in a reasonable time
frame, with just a few thousand temporary files. EXPLAIN ANALYZE gives
these numbers:
Hash Join (cost=3153618.94..7866324.29 rows=86653612 width=84)
(actual time=56041.178..87956.190 rows=511599 loops=1)
Output: (ef.arch)::text, en.name, f.file_id, f.name
Hash Cond: (f.contents_id = ef.contents_id)
-> Hash Join (cost=2349021.27..5624009.06 rows=5647704 width=68)
(actual time=46754.959..75014.287 rows=3820442 loops=1)
Output: f.file_id, f.name, f.contents_id
Hash Cond: (psm.package_id = f.package_id)
-> Bitmap Heap Scan on symboldb.package_set_member psm
(cost=495.42..5385.79 rows=33870 width=4) (actual time=2.124..8.425
rows=32872 loops=1)
Output: psm.package_id
Recheck Cond: (psm.set_id = 36)
-> Bitmap Index Scan on package_set_member_set_id_idx
(cost=0.00..486.95 rows=33870 width=0) (actual time=2.089..2.089
rows=33112 loops=1)
Index Cond: (psm.set_id = 36)
-> Hash (cost=1252310.60..1252310.60 rows=45263060 width=72)
(actual time=46699.578..46699.578 rows=45327619 loops=1)
Output: f.file_id, f.name, f.package_id, f.contents_id
Buckets: 8192 Batches: 2048 (originally 1024) Memory
Usage: 8193kB
-> Seq Scan on symboldb.file f (cost=0.00..1252310.60
rows=45263060 width=72) (actual time=0.003..34658.946 rows=45327619 loops=1)
Output: f.file_id, f.name, f.package_id, f.contents_id
-> Hash (cost=592698.57..592698.57 rows=10958808 width=28) (actual
time=9261.212..9261.212 rows=10974399 loops=1)
Output: ef.arch, ef.contents_id, en.name, en.contents_id
Buckets: 16384 Batches: 128 Memory Usage: 5462kB
-> Hash Join (cost=51493.85..592698.57 rows=10958808
width=28) (actual time=449.725..7130.583 rows=10974399 loops=1)
Output: ef.arch, ef.contents_id, en.name, en.contents_id
Hash Cond: (en.contents_id = ef.contents_id)
-> Seq Scan on symboldb.elf_needed en
(cost=0.00..202333.08 rows=10958808 width=20) (actual
time=0.008..1633.980 rows=10974399 loops=1)
Output: en.name, en.contents_id
-> Hash (cost=30622.82..30622.82 rows=1272082 width=8)
(actual time=449.424..449.424 rows=1275149 loops=1)
Output: ef.arch, ef.contents_id
Buckets: 32768 Batches: 8 Memory Usage: 6253kB
-> Seq Scan on symboldb.elf_file ef
(cost=0.00..30622.82 rows=1272082 width=8) (actual time=0.032..223.748
rows=1275149 loops=1)
Output: ef.arch, ef.contents_id
Total runtime: 87983.826 ms
Is this a bug?
--
Florian Weimer / Red Hat Product Security Team
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
