Hi,
When I run the following query Postmaster crashes. For your reference I have
attached information regarding the query (query plan, table , index, server
log).
My observation is that when Postmaster starts taking lot of memory because
of which system runs out of memory and OS kills the process. System specs
are as: SUSE 10.1, 2GB RAM , P- IV.
Thanks in advance,
Rupesh Bajaj
QUERY
select * from semtable as t1, semtable as t2 where exists (select
multilingual.english_hopi_lin.element from multilingual.english_hopi_lin,
multilingual.english_hopi_lout, multilingual.crosslink where
multilingual.english_hopi_lin.node = t1.scategory and
multilingual.english_hopi_lout.node = multilingual.crosslink.equivalent and
multilingual.english_hopi_lout.element =
multilingual.english_hopi_lin.element and multilingual.crosslink.word =
t2.scategory) or exists (select multilingual.hindi_hopi_lin.element from
multilingual.hindi_hopi_lin, multilingual.hindi_hopi_lout,
multilingual.crosslink where multilingual.hindi_hopi_lin.node = t1.scategoryand
multilingual.hindi_hopi_lout.node = multilingual.crosslink.equivalent and
multilingual.hindi_hopi_lout.element = multilingual.hindi_hopi_lin.elementand
multilingual.crosslink.word = t2.scategory);
TABLES
semtable (stitle varchar, sauthor varchar, scategory varchar) having 100,000
tuples.
English_hopi_lin (node varchar, element varchar) having 196533 tuples.
English_hopi_lout (node varchar, element varchar) having 154837 tuples.
Crosslink (word varchar, equivalent varchar, lang1 varchar, lang2
varchar) having
302269 tuples.
Hindi_hopi_lin (node varchar, element varchar) having 61497 tuples.
Hindi_hopi_lout (node varchar, element varchar) having 17414 tuples.
INDEX
e_lin_node on English_hopi_lin(node);
e_lout_node on English_hopi_lout(node);
c_w on crosslink(word);
c_q on crosslink(equivalent);
h_lin_node on Hindi_hopi_lin (node);
h_lout_node on Hindi_hopi_lout (node);
Query Plan:
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..1576180832775.25 rows=4800000000 width=144)
Join Filter: ((subplan) OR (subplan))
-> Seq Scan on semtable t1 (cost=0.00..1888.00 rows=80000 width=72)
-> Seq Scan on semtable t2 (cost= 0.00..1888.00 rows=80000 width=72)
SubPlan
-> Hash Join (cost=8.49..36.62 rows=1 width=21)
Hash Cond: ((hindi_hopi_lout.node)::text = (crosslink.equivalent
)::text)
-> Nested Loop (cost= 0.00..28.06 rows=6 width=44)
-> Index Scan using h_lin_node on hindi_hopi_lin (cost=
0.00..8.30 rows=2 width=21)
Index Cond: ((node)::text = ($0)::text)
-> Index Scan using h_lout_element on hindi_hopi_lout
(cost= 0.00..9.86 rows=2 width=44)
Index Cond: ((hindi_hopi_lout.element)::text =
(hindi_hopi_lin.element)::text)
-> Hash (cost=8.42..8.42 rows=6 width=24)
-> Index Scan using c_w on crosslink (cost=
0.00..8.42rows=6 width=24)
Index Cond: ((word)::text = ($1)::text)
-> Nested Loop (cost=0.00..209.62 rows=1 width=14)
Join Filter: ((english_hopi_lout.element)::text =
(english_hopi_lin.element)::text)
-> Nested Loop (cost=0.00..201.63 rows=14 width=15)
-> Index Scan using c_w on crosslink
(cost=0.00..8.42rows=6 width=24)
Index Cond: ((word)::text = ($1)::text)
-> Index Scan using e_lout_node on english_hopi_lout
(cost=0.00..31.86 rows=27 width=29)
Index Cond: ((english_hopi_lout.node)::text = (
crosslink.equivalent)::text)
-> Index Scan using e_lin_node on english_hopi_lin (cost=
0.00..0.48 rows=7 width=14)
Index Cond: ((node)::text = ($0)::text)
(24 rows)
SERVER LOG
LOG: background writer process (PID 5808) was terminated by signal 9
LOG: terminating any other active server processes
LOG: statistics collector process (PID 5809) was terminated by signal 9
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted at 2007-07-07 01:24:27 IST
LOG: checkpoint record is at 0/6DB4E9C
LOG: redo record is at 0/6DB4E9C; undo record is at 0/0; shutdown FALSE
LOG: next transaction ID: 0/891; next OID: 24576
LOG: next MultiXactId: 1; next MultiXactOffset: 0
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: record with zero length at 0/6DB4EE4
LOG: redo is not required
LOG: database system is ready
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
