Обсуждение: advice on query joining 10 tables
Hi all. Im triyng to see if i can improve the performance of a query (mainly a 10 table join) 1) Besides of triyng to use indexes, there is some rules of thumb to follow? 2) Should i try to join the bigger tables last in the query? 3) There is some place for understanding EXPLAIN better? Thanks! Gerardo
Gerardo Herzig <gherzig@fmed.uba.ar> writes:
> Hi all. Im triyng to see if i can improve the performance of a query
> (mainly a 10 table join)
One easy thing to try is increase from_collapse_limit and/or
join_collapse_limit to 10 or more. That will result in longer planning
time but might let the planner find a better plan for this query.
regards, tom lane
On Mon, May 10, 2010 at 12:40 PM, Gerardo Herzig <gherzig@fmed.uba.ar> wrote: > Hi all. Im triyng to see if i can improve the performance of a query > (mainly a 10 table join) > > 1) Besides of triyng to use indexes, there is some rules of thumb to follow? log long running queries for later analysis? > 2) Should i try to join the bigger tables last in the query? The query planner can do that for you automatically. > 3) There is some place for understanding EXPLAIN better? http://explain.depesz.com/ Here and the docs? I've found it pretty easy to post one here and ask for help and get it on why a query isn't running well. Note that explain analyze is much preferred to plain explain.
Scott Marlowe wrote: > On Mon, May 10, 2010 at 12:40 PM, Gerardo Herzig <gherzig@fmed.uba.ar> wrote: >> Hi all. Im triyng to see if i can improve the performance of a query >> (mainly a 10 table join) >> >> 1) Besides of triyng to use indexes, there is some rules of thumb to follow? > > log long running queries for later analysis? > >> 2) Should i try to join the bigger tables last in the query? > > The query planner can do that for you automatically. > >> 3) There is some place for understanding EXPLAIN better? > > http://explain.depesz.com/ > > Here and the docs? I've found it pretty easy to post one here and ask > for help and get it on why a query isn't running well. Note that > explain analyze is much preferred to plain explain. > Well, thanks Tom and Scott for the answers. I will take some more time reading the docs. Im looking more to know better, rather than just waiting to someone to point out my errors. By the way, that site is cute! Thanks again! Gerardo
On Mon, May 10, 2010 at 2:38 PM, Gerardo Herzig <gherzig@fmed.uba.ar> wrote: > Scott Marlowe wrote: >> On Mon, May 10, 2010 at 12:40 PM, Gerardo Herzig <gherzig@fmed.uba.ar> wrote: >>> Hi all. Im triyng to see if i can improve the performance of a query >>> (mainly a 10 table join) >>> >>> 1) Besides of triyng to use indexes, there is some rules of thumb to follow? >> >> log long running queries for later analysis? >> >>> 2) Should i try to join the bigger tables last in the query? >> >> The query planner can do that for you automatically. >> >>> 3) There is some place for understanding EXPLAIN better? >> >> http://explain.depesz.com/ >> >> Here and the docs? I've found it pretty easy to post one here and ask >> for help and get it on why a query isn't running well. Note that >> explain analyze is much preferred to plain explain. >> > Well, thanks Tom and Scott for the answers. I will take some more time > reading the docs. Im looking more to know better, rather than just > waiting to someone to point out my errors. Also, search the pgsql archives for "explain analyze" for lots of posts. You can learn quite a bit trawling the archives like that.