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:
I've tried to simplify the table and view definitions wherever possible.
And the query plans:
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
