Good morning,
We run postgres 9.4. We have a query that is running for more than 6 hours. I have downloaded the query, the explain analyze with buffers, the sizes of the principle tables (june29. tocommunity.op) the table descriptions of the principle tables, the postgresql.conf file, and the plpgsql function where the bottleneck is occurring (number_of_sends.plpgsql)
One observation I would like specific comments for: the buffers: shared hit on the explain shows 532 million hit buffers - could that be a clue to the 6 hour query? Why so many? The largest table (queuenodes) is only
6,500,000 pages. the next largest - emailrcpts is only 3,700,000 pages If this is a valid observation, is there a memory setting I could use to alleviate that? Or am I interpreting wrong. Another insight: when I run this query again, with cache benefit, the query runs in less than a minute and the buffers:shared hit rises to only 232,000.
Any observation, and / or explanation of the 'buffers' parameter of the explain would be appreciated.
--