Re: Memory exhausted (leak?) - Mailing list pgsql-general

> 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@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL