From: Scott Marlowe <smarlowe@g2switchworks.com>
To: martial.bizel@free.fr
Cc: pgsql-performance@postgresql.org
Subject: Re: out of memory
Date: Wed, 15 Feb 2006 11:38:17 -0600
Message-ID: <1140025097.22740.229.camel@state.g2switchworks.com> (raw)
In-Reply-To: <1140023901.43f3625d51661@imp2-g19.free.fr>
References: <1140018930.43f34ef260de0@imp2-g19.free.fr>
<1140022257.22740.225.camel@state.g2switchworks.com>
<1140023901.43f3625d51661@imp2-g19.free.fr>
On Wed, 2006-02-15 at 11:18, martial.bizel@free.fr wrote:
> Here the result with hashAgg to false :
> Nested Loop (cost=2487858.08..2490896.58 rows=1001 width=34) (actual
> time=1028044.781..1030251.260 rows=1000 loops=1)
> -> Subquery Scan "day" (cost=2487858.08..2487870.58 rows=1000 width=16)
> (actual time=1027996.748..1028000.969 rows=1000 loops=1)
> -> Limit (cost=2487858.08..2487860.58 rows=1000 width=12) (actual
> time=1027996.737..1027999.199 rows=1000 loops=1)
> -> Sort (cost=2487858.08..2487866.47 rows=3357 width=12)
> (actual time=1027996.731..1027998.066 rows=1000 loops=1)
> Sort Key: sum(occurence)
> -> GroupAggregate (cost=2484802.05..2487661.48 rows=3357
> width=12) (actual time=810623.035..914550.262 rows=19422774 loops=1)
> -> Sort (cost=2484802.05..2485752.39 rows=380138
> width=12) (actual time=810612.248..845427.013 rows=36724340 loops=1)
> Sort Key: query
> -> Index Scan using test_date on
> queries_detail_statistics (cost=0.00..2449570.55 rows=380138 width=12) (actual
> time=25.393..182029.205 rows=36724340 loops=1)
> Index Cond: ((date >= '2006-01-01'::date)
> AND (date <= '2006-01-30'::date))
> Filter: (((portal)::text = '1'::text) OR
> ((portal)::text = '2'::text))
> -> Index Scan using query_string_pkey on query_string (cost=0.00..3.01
> rows=1 width=34) (actual time=2.244..2.246 rows=1 loops=1000)
> Index Cond: ("outer".query = query_string.id)
> Total runtime: 1034357.390 ms
OK, in the index scan using test_date, you get 36724340 when the planner
expects 380138. That's off by a factor of about 10, so I'm guessing
that your statistics aren't reflecting what's really in your db. You
said before you'd run analyze, so I'd try increasing the stats target on
that column and rerun analyze to see if things get any better.
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: smarlowe@g2switchworks.com, martial.bizel@free.fr
Subject: Re: out of memory
In-Reply-To: <1140025097.22740.229.camel@state.g2switchworks.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