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.