From: bobJobS <russelljanusz@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Re: Slow query (wrong index used maybe)
Date: Mon, 27 Jan 2014 11:10:49 -0800 (PST)
Message-ID: <1390849849986-5789045.post@n5.nabble.com> (raw)
In-Reply-To: <1390844278.5638.75903621.2E4ABB56@webmail.messagingengine.com>
References: <1390833860.7369.75825537.0CAF7BA8@webmail.messagingengine.com>
<2631.1390835184@sss.pgh.pa.us>
<1390844278.5638.75903621.2E4ABB56@webmail.messagingengine.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
My developers have had the same issue.
Postgres 9.2.3 on Linux 5.6.
The query planner estimates (for 27 table join SQL) that using the nestloop
is faster, when in fact it is not. A hashjoin returns results faster. We've
set enable_nestloop = false and have gotten good results. The problem is,
nestoop would be faster for other types of queries. Maybe ones with fewer
joins.
Recently we made a change that forced our multi join queires to slow down.
We now build temp views for each user session. To speed these queries up, we
up'd geqo_effort = 10. This has also given us good results; but again, we
don't know if there will be another impact down the road.
Same issue here with redesign. There is some simple denormalization we could
do that would minimize our joins. Instead if link tables, we would utilize
hstore, json or array columns types.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Slow-query-wrong-index-used-maybe-tp5788979p5789045.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
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: russelljanusz@yahoo.com
Subject: Re: Slow query (wrong index used maybe)
In-Reply-To: <1390849849986-5789045.post@n5.nabble.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