Re: Slow query (wrong index used maybe)

On Mon, Jan 27, 2014, at 7:06, Tom Lane wrote:
> Stelian Iancu <stelian(at)iancu(dot)ch> writes:
> > I have Postrgres 9.3 running on a Linux machine with 32GB RAM. I have a
> > fairly large database (some tables with approx. 1 mil. records) and I
> > have the following query:
> > [ 13-way join joined to a 3-way join ]
>
> Think you'll need to raise join_collapse_limit and from_collapse_limit
> to get the best plan here. The planning time might hurt, though.
>

I did raise both to 40 and it works flawless (for now). I got the
response time to less than a second. However I don't know what the
implications are for the future.

> TBH that schema looks designed for inefficiency; you'd be better off
> rethinking the design rather than hoping the planner is smart enough
> to save you from it.
>

Heh, I wish it was this easy. This whole thing is part of us moving away
from Oracle to Postgres. We already have this huge DB with this schema
in Oracle (which was successfully imported into Postgres, minus these
performance issues we're seeing now) and I don't know how feasible it is
to even start thinking about a redesign.

But I appreciate your input regarding this. Maybe one of these days I
will have success in convincing my boss to even start taking a look at
the design of the DB (you know the saying "it works, don't fix it").

> regards, tom lane
>
>
> --
> Sent via pgsql-performance mailing list
> (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance