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

From: Eoghan Murray <eoghan(at)qatano(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Poor performance problem with Materialize, 8.4 -> 9.1 (enable_material)
Date: 2012-07-13 14:11:23
Message-ID: CABVdsenbysuwirMxXR1Pt+0PCLiR_JLvN3ZTWghHAxLuXzVn3Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I'm upgrading from 8.4 to 9.1 and experiencing a performance degradation on
a key query with 2 views and 2 tables.

Old server "PostgreSQL 8.4.10 on i686-redhat-linux-gnu, compiled by GCC gcc
(GCC) 4.1.2 20080704 (Red Hat 4.1.2-51), 32-bit"
New server "PostgreSQL 9.1.4 on x86_64-unknown-linux-gnu, compiled by gcc
(GCC) 4.4.6 20110731 (Red Hat 4.4.6-3), 64-bit"

The query is as follows:
SELECT *
FROM edge_geom
WHERE (edge_geom.start_id, edge_geom.end_id) IN (('congo', 'donal'),
('golow', 'tundo'), ('golow', 'arthur'), ('golow', 'porto'), ('tundo',
'donal'), ('golow', 'newbo'), ('porto', 'donal'), ('decal', 'donal'),
('arthur', 'donal'), ('leandro', 'donal'), ('golow', 'decal'), ('golow',
'salad'), ('newbo', 'donal'), ('golow', 'congo'), ('salad', 'donal'),
('golow', 'leandro'));

Schema definitions:
http://pastebin.com/0YNG8jSC
I've tried to simplify the table and view definitions wherever possible.

And the query plans:
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

So it looks like the Materialize in the query plan is causing the 30x
slowdown.
With the materialize strategy switched off , it's still 5x slower on 9.1
vs. 8.4.

Any help appreciated, I acknowledge that the tables and views aren't the
simplest.

Thanks!

Eoghan

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Claudio Freire 2012-07-13 15:53:24 Re: Poor performance problem with Materialize, 8.4 -> 9.1 (enable_material)
Previous Message Stanislaw Pankevich 2012-07-13 07:50:53 Re: PostgreSQL db, 30 tables with number of rows < 100 (not huge) - the fastest way to clean each non-empty table and reset unique identifier column of empty ones.