Friday, June 1, 2012

enable_material=false

I remember back in my oracle days, one of the first things I'd turn off in my OLTP environments was hash_join_enabled. Oracle would spend more time creating the hash table than it would actually processing the query.

I ran into that again this week, but this time with Postgres 9.1 and this time with Materialize.

Here's the query stats (from the wonderfull http://explain.depesz.com) pretty clearly showed me where the problem was.
node typecountsum of times% of query
Bitmap Heap Scan561251.792 ms0.1 %
Bitmap Index Scan6666.669 ms0.0 %
BitmapAnd10.225 ms0.0 %
Hash64.904 ms0.0 %
Hash Join637540.586 ms0.1 %
HashAggregate198.755 ms0.0 %
Index Scan53777.276 ms0.0 %
Materialize928084279.438 ms54.9 %
Merge Left Join1100.578 ms0.0 %
Nested Loop7607.531 ms0.0 %
Nested Loop Left Join1022904169.450 ms44.8 %
Seq Scan1572686.868 ms0.1 %
Sort3137.154 ms0.0 %


Here's the same query in PG9.0.

node typecountsum of times% of query
Bitmap Heap Scan3238.963 ms0.1 %
Bitmap Index Scan441.682 ms0.0 %
BitmapAnd10.458 ms0.0 %
Hash1529947.841 ms9.4 %
Hash Join773363.019 ms22.9 %
Hash Left Join841505.547 ms13.0 %
HashAggregate1164.592 ms0.1 %
Index Scan6187.713 ms0.1 %
Materialize15122.977 ms1.6 %
Nested Loop685.415 ms0.0 %
Nested Loop Left Join310780.593 ms3.4 %
Seq Scan16158396.545 ms49.5 %
Sort115.215 ms0.0 %


It seems like PG9.1 favors materializing a little more than 9.0 does, I even knocked the statistics up to 10k in the PG9.1 environment to get it to knock that off but no luck.

To get by the problem, while we tune the query and look at some other PG tuning options, I just disabled materializing for this specific application user.

No comments:

Post a Comment

Web Statistics