From: Stelian Iancu <stelian@iancu.ch>
To: pgsql-performance@postgresql.org
Subject: Re: Slow query (wrong index used maybe)
Date: Mon, 27 Jan 2014 09:37:58 -0800
Message-ID: <1390844278.5638.75903621.2E4ABB56@webmail.messagingengine.com> (raw)
In-Reply-To: <2631.1390835184@sss.pgh.pa.us>
References: <1390833860.7369.75825537.0CAF7BA8@webmail.messagingengine.com>
<2631.1390835184@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On Mon, Jan 27, 2014, at 7:06, Tom Lane wrote:
> Stelian Iancu <stelian@iancu.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stelian@iancu.ch
Subject: Re: Slow query (wrong index used maybe)
In-Reply-To: <1390844278.5638.75903621.2E4ABB56@webmail.messagingengine.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox