pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedadvice on query joining 10 tables
5+ messages / 3 participants
[nested] [flat]
* advice on query joining 10 tables
@ 2010-05-10 18:40 Gerardo Herzig <gherzig@fmed.uba.ar>
2010-05-10 20:11 ` Re: advice on query joining 10 tables Tom Lane <tgl@sss.pgh.pa.us>
2010-05-10 20:37 ` Re: advice on query joining 10 tables Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Gerardo Herzig @ 2010-05-10 18:40 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: advice on query joining 10 tables
2010-05-10 18:40 advice on query joining 10 tables Gerardo Herzig <gherzig@fmed.uba.ar>
@ 2010-05-10 20:11 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2010-05-10 20:11 UTC (permalink / raw)
To: Gerardo Herzig <gherzig@fmed.uba.ar>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: advice on query joining 10 tables
2010-05-10 18:40 advice on query joining 10 tables Gerardo Herzig <gherzig@fmed.uba.ar>
@ 2010-05-10 20:37 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-05-10 20:38 ` Re: advice on query joining 10 tables Gerardo Herzig <gherzig@fmed.uba.ar>
1 sibling, 1 reply; 5+ messages in thread
From: Scott Marlowe @ 2010-05-10 20:37 UTC (permalink / raw)
To: Gerardo Herzig <gherzig@fmed.uba.ar>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: advice on query joining 10 tables
2010-05-10 18:40 advice on query joining 10 tables Gerardo Herzig <gherzig@fmed.uba.ar>
2010-05-10 20:37 ` Re: advice on query joining 10 tables Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-05-10 20:38 ` Gerardo Herzig <gherzig@fmed.uba.ar>
2010-05-10 21:35 ` Re: advice on query joining 10 tables Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Gerardo Herzig @ 2010-05-10 20:38 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: advice on query joining 10 tables
2010-05-10 18:40 advice on query joining 10 tables Gerardo Herzig <gherzig@fmed.uba.ar>
2010-05-10 20:37 ` Re: advice on query joining 10 tables Scott Marlowe <scott.marlowe@gmail.com>
2010-05-10 20:38 ` Re: advice on query joining 10 tables Gerardo Herzig <gherzig@fmed.uba.ar>
@ 2010-05-10 21:35 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Scott Marlowe @ 2010-05-10 21:35 UTC (permalink / raw)
To: Gerardo Herzig <gherzig@fmed.uba.ar>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-05-10 21:35 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-05-10 18:40 advice on query joining 10 tables Gerardo Herzig <gherzig@fmed.uba.ar>
2010-05-10 20:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-05-10 20:37 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-05-10 20:38 ` Gerardo Herzig <gherzig@fmed.uba.ar>
2010-05-10 21:35 ` Scott Marlowe <scott.marlowe@gmail.com>
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