| From: | Reid Thompson <Reid(dot)Thompson(at)ateb(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | work_mem = 900MB but Sort Method: external merge Disk: 304008kB |
| Date: | 2011-02-22 16:35:22 |
| Message-ID: | 4D63E5CA.7000201@ateb.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
What am I missing that causes this to resort to sorting on disk?
obc=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 8.3.7 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42)
(1 row)
Time: 43.920 ms
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=1063641.92..1063643.47 rows=20 width=13) (actual time=422710.147..422711.328 rows=20 loops=1)
> -> Unique (cost=1063641.92..1064133.33 rows=6320 width=13) (actual time=422710.022..422711.127 rows=20 loops=1)
> -> Sort (cost=1063641.92..1063887.62 rows=98282 width=13) (actual time=422710.014..422710.696 rows=172 loops=1)
> Sort Key: cpn.value
> Sort Method: external merge Disk: 304008kB
> -> Nested Loop (cost=647.20..1061026.67 rows=98282 width=13) (actual time=61.029..71867.921 rows=9627373 loops=1)
> -> HashAggregate (cost=647.20..648.15 rows=95 width=4) (actual time=60.950..64.350 rows=596 loops=1)
> -> Hash Join (cost=4.59..646.96 rows=95 width=4) (actual time=0.352..57.210 rows=596 loops=1)
> Hash Cond: (cb.client_id = c.id)
> -> Seq Scan on contact_block cb (cost=0.00..596.31 rows=12031 width=8) (actual time=0.015..26.757 rows=10323 loops=1)
> -> Hash (cost=4.58..4.58 rows=1 width=4) (actual time=0.064..0.064 rows=1 loops=1)
> -> Seq Scan on clients c (cost=0.00..4.58 rows=1 width=4) (actual time=0.021..0.055 rows=1 loops=1)
> Filter: ((name)::text = 'Kmart Pharmacies, Inc.'::text)
> -> Index Scan using extra_import_param_blk_item_tag on extra_import_param cpn (cost=0.00..11039.67 rows=9777 width=17) (actual time=0.057..61.769 rows=16153 loops=596)
> Index Cond: ((cpn.block_id = cb.id) AND ((cpn.tag)::text = 'PATNAME'::text))
> Total runtime: 422920.026 ms
> (16 rows)
>
> Time: 422924.289 ms
> obc=# show sort_mem;
> work_mem
> ----------
> 900MB
> (1 row)
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Howard Cole | 2011-02-22 16:40:36 | Reordering a table |
| Previous Message | Tom Lane | 2011-02-22 15:32:05 | Re: Notify rule |