Re: Poor performance problem with Materialize, 8.4 -> 9.1 (enable_material)

From: Claudio Freire <klaussfreire(at)gmail(dot)com>
To: Eoghan Murray <eoghan(at)qatano(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Poor performance problem with Materialize, 8.4 -> 9.1 (enable_material)
Date: 2012-07-13 15:53:24
Message-ID: CAGTBQpaQ066-vUZ_eN3CoND2W4QfL_tDoxYtGv87Lu27DdjobA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Fri, Jul 13, 2012 at 11:11 AM, Eoghan Murray <eoghan(at)qatano(dot)com> wrote:
> 8.4: 314ms: http://explain.depesz.com/s/GkX
> 9.1: 10,059ms :http://explain.depesz.com/s/txn
> 9.1 with setting `enable_material = off`: 1,635ms
> http://explain.depesz.com/s/gIu

I think the problem is it's using a merge join, with a sort inside
that's producing 600x more rows than expected, while 8.4 does a hash
join with no intermediate big tables instead.

What's your configuration like in both servers? (that could explain
planning differences)

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2012-07-13 16:15:07 Re: query overhead
Previous Message Eoghan Murray 2012-07-13 14:11:23 Poor performance problem with Materialize, 8.4 -> 9.1 (enable_material)