pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedamount of join's and sequential access to the tables involved
5+ messages / 2 participants
[nested] [flat]
* amount of join's and sequential access to the tables involved
@ 2012-01-11 15:36 Gerardo Herzig <gherzig@fmed.uba.ar>
0 siblings, 1 reply; 5+ messages in thread
From: Gerardo Herzig @ 2012-01-11 15:36 UTC (permalink / raw)
To: pgsql-sql
Hi all. Im working on a 'simple' query with 7, 8 left joins. After the
9nth join or so, explain analyze became to show the plan with many
tables being read in sequential fashion. Of course, this slows down the
query response in a factor on 10.
I can alter the order in wich the join's are appended, allways with the
same result.
So,it looks like a resources config situation.
Wich config variables are involved in the use of hash joins?
(I dont have access right now to the server running pg, so i cannot show
query plan nor pg configuration)
Thanks!
Gerardo
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: amount of join's and sequential access to the tables involved
@ 2012-01-11 15:40 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gerardo Herzig <gherzig@fmed.uba.ar>
0 siblings, 2 replies; 5+ messages in thread
From: Tom Lane @ 2012-01-11 15:40 UTC (permalink / raw)
To: Gerardo Herzig <gherzig@fmed.uba.ar>; +Cc: pgsql-sql
Gerardo Herzig <gherzig@fmed.uba.ar> writes:
> Hi all. Im working on a 'simple' query with 7, 8 left joins. After the
> 9nth join or so, explain analyze became to show the plan with many
> tables being read in sequential fashion. Of course, this slows down the
> query response in a factor on 10.
increase join_collapse_limit, perhaps?
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: amount of join's and sequential access to the tables involved
@ 2012-01-11 16:06 Gerardo Herzig <gherzig@fmed.uba.ar>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 5+ messages in thread
From: Gerardo Herzig @ 2012-01-11 16:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
El mié, 11-01-2012 a las 10:40 -0500, Tom Lane escribió:
> Gerardo Herzig <gherzig@fmed.uba.ar> writes:
> > Hi all. Im working on a 'simple' query with 7, 8 left joins. After the
> > 9nth join or so, explain analyze became to show the plan with many
> > tables being read in sequential fashion. Of course, this slows down the
> > query response in a factor on 10.
>
> increase join_collapse_limit, perhaps?
>
> regards, tom lane
>
Crap, yes. That was it.
Just curious about one thing:
That query is part of a function. If i raise set_join_collapse in a psql
bash session, and then excecute the function, there is no changes. But
if i excecute the query directly in the psql session, it flys.
So, what happens, when a plsql function is excecuted, it takes is own
enviroment variables, or something like that?
Thanks again, Tom!
Gerardo
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: amount of join's and sequential access to the tables involved
@ 2012-01-11 16:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gerardo Herzig <gherzig@fmed.uba.ar>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2012-01-11 16:14 UTC (permalink / raw)
To: Gerardo Herzig <gherzig@fmed.uba.ar>; +Cc: pgsql-sql
Gerardo Herzig <gherzig@fmed.uba.ar> writes:
> So, what happens, when a plsql function is excecuted, it takes is own
> enviroment variables, or something like that?
No, but it probably cached a plan from an execution before you changed
join_collapse_limit ...
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: amount of join's and sequential access to the tables involved
@ 2012-01-12 14:09 Gerardo Herzig <gherzig@fmed.uba.ar>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Gerardo Herzig @ 2012-01-12 14:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
El mié, 11-01-2012 a las 10:40 -0500, Tom Lane escribió:
> Gerardo Herzig <gherzig@fmed.uba.ar> writes:
> > Hi all. Im working on a 'simple' query with 7, 8 left joins. After the
> > 9nth join or so, explain analyze became to show the plan with many
> > tables being read in sequential fashion. Of course, this slows down the
> > query response in a factor on 10.
>
> increase join_collapse_limit, perhaps?
>
> regards, tom lane
>
Im sory Tom and all, again stuck with this. Now i have 9 joins, and
join_collapse_limit = 20, and the query slows down big time when the 9th
join appears.
Another configuration variable to check against?
Thanks again.
Gerardo
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2012-01-12 14:09 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-01-11 15:36 amount of join's and sequential access to the tables involved Gerardo Herzig <gherzig@fmed.uba.ar>
2012-01-11 15:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-01-11 16:06 ` Gerardo Herzig <gherzig@fmed.uba.ar>
2012-01-11 16:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-01-12 14:09 ` Gerardo Herzig <gherzig@fmed.uba.ar>
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