Re: Memory exhausted (leak?)

> I tried executing this query disabling hash or merge joins, and not
> doing 'unique' and the same error ocurred.. (note: there was a
> matrrialize step there)
>
> postgresql version is 7.3.6

1. Are the row estimates accurate? If not increase your statistics.
2. Is the explain analyze different?
3. Do these tables receive a lot of update/deletes? Could you possibly
have index bloat?

Sincerely,

Joshua D. Drake

>
> -Aaron
>
> EXPLAIN output below
> --------------------------
> Unique (cost=278362.15..278362.26 rows=12 width=64)
> -> Sort (cost=278362.15..278362.20 rows=117 width=64)
> Sort Key: obj1.docid
> -> Hash Join (cost=278039.06..278361.34 rows=117 width=64)
> Hash Cond: ("outer".docid = "inner".refers)
> -> Seq Scan on obj1 (cost=0.00..299.20 rows=22440 width=4)
> -> Hash (cost=278039.00..278039.00 rows=117 width=60)
> -> Hash Join (cost=277702.73..278039.00
> rows=117 width=60)
> Hash Cond: ("outer".referrer = "inner".refers)
> -> Seq Scan on r2 r_1_2
> (cost=0.00..308.81 rows=27161 width=8)
> -> Hash (cost=277702.53..277702.53
> rows=395 width=52)
> -> Nested Loop
> (cost=343.71..277702.53 rows=395 width=52)
> Join Filter: ("outer".refers =
> "inner".referrer)
> -> Nested Loop
> (cost=343.71..258374.97 rows=1 width=44)
> -> Hash Join
> (cost=343.71..258371.80 rows=1 width=40)
> Hash Cond:
> ("outer".refers = "inner".refers)
> -> Nested Loop
> (cost=0.00..258011.22 rows=16859 width=28)
> -> Nested
> Loop (cost=0.00..238683.66 rows=1 width=20)
> ->
> Nested Loop (cost=0.00..219356.09 rows=1 width=12)
>
> -> Index Scan using obj4_word_map_wid_idx on obj4_word_map
> (cost=0.00..213099.71 rows=1600 width=4)
>
> Index Cond: (wid = 19458::oid)
>
> Filter: ("type" = 4)
>
> -> Index Scan using obj4_doc_idx on obj4 (cost=0.00..3.90 rows=1
> width=8)
>
> Index Cond: ("outer".doc = obj4.descriptor)
>
> Filter: (text_field ~* 'ABC'::text)
> ->
> Index Scan using r1_referrer_idx on r1 (cost=0.00..19234.83
> rows=16860 width=8)
>
> Index Cond: (r1.referrer = "outer".objobj3t)
> -> Index
> Scan using r1_referrer_idx on r1 r1_2_3 (cost=0.00..19234.83
> rows=16860 width=8)
> Index
> Cond: ("outer".refers = r1_2_3.referrer)
> -> Hash
> (cost=343.70..343.70 rows=23 width=12)
> -> Nested
> Loop (cost=0.00..343.70 rows=23 width=12)
> ->
> Index Scan using obj3_field1_idx on obj3 (cost=0.00..3.33 rows=1
> width=4)
>
> Index Cond: (field1 = 'XYZ'::text)
> ->
> Index Scan using r3_referrer_idx on r3 r_2_3 (cost=0.00..339.86
> rows=94 width=8)
>
> Index Cond: ("outer".docid = r_2_3.referrer)
> -> Index Scan using
> obj2_docid_idx on obj2 (cost=0.00..3.16 rows=1 width=4)
> Index Cond:
> (obj2.docid = "outer".referrer)
> -> Index Scan using
> r1_referrer_idx on r1 r1_1_2 (cost=0.00..19234.83 rows=16860 width=8)
> Index Cond:
> ("outer".referrer = r1_1_2.referrer)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd(at)commandprompt(dot)com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL