Thread: Explain Analyze understanding

Hi,

I need to improve performance for a particular SQL command but facing difficulties to understand the explain results.

Is there somewhere a tool could help on this?

I've stored the SQL code and corresponding explain analyze at

SQL: http://www.opendb.com.br/v1/sql.txt
Explain: http://www.opendb.com.br/v1/explain.txt

in case some one could identify some improvement that could be done to the SQL or table structures in order to get better command performance.

Thank you!

--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@opendb.com.br

On 08/24/11 9:18 AM, Carlos Henrique Reimer wrote:
> I need to improve performance for a particular SQL command but facing
> difficulties to understand the explain results.

you're joining like 50 tables in nested selects, getting 200 rows, and
its only taking 3.5 seconds?     where's the problem?


you can paste EXPLAIN output into http://explain.depesz.com and get
output like http://explain.depesz.com/s/hfWp which helps format it for
better understanding.

in this case, most of the time (3.35s worth) appears to be spent on
sorts and an nidex scan inside an inner merge join thats processing
77000 rows.


--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast