I've never received any reply to this post; as I said, I think I have a dump
that recreates the problem.
--- Ven 14/8/09, Scara Maccai m_li...@yahoo.it ha scritto:
Da: Scara Maccai m_li...@yahoo.it
Oggetto: Re: [GENERAL] totally different plan when using partitions
A: Tom Lane t
Hmmm ... why is the inner Nested Loop estimated to produce
30120 rows,
when the cartesian product of its inputs would only be 285
* 14 = 3990
rows?
Oh my... didn't notice it!!!
What PG version is this
That was 8.4 beta1; now tried on
select version()
PostgreSQL 8.4.0 on
Scara Maccai wrote:
Hmmm ... why is the inner Nested Loop estimated to produce
30120 rows,
when the cartesian product of its inputs would only be 285
* 14 = 3990
rows?
Oh my... didn't notice it!!!
It was doing the same thing here too:
Query:
set enable_mergejoin=off;set enable_hashjoin=off;
explain analyze
select nome1,
thv3tralacc,
dltbfpgpdch
FROM cell_bsc_60_0610 as cell_bsc
left outer join teststscell73_test as data on
data.ne_id=cell_bsc.nome1
left outer
Anyone??? This looks like a bug to me... or is there an explanation?
--- Mer 12/8/09, Scara Maccai m_li...@yahoo.it ha scritto:
Da: Scara Maccai m_li...@yahoo.it
Oggetto: [GENERAL] totally different plan when using partitions
A: pgsql-general pgsql-general@postgresql.org
Data: Mercoledì 12
Scara Maccai wrote:
same query, but using postgresql's partition pruning (2):
explain analyze
select nome1,
thv3tralacc,
dltbfpgpdch
FROM cell_bsc_60_0610 as cell_bsc
left outer join teststscell73 as data on
data.ne_id=cell_bsc.nome1
left outer join
Thank you for your reply.
This makes partitions unusable for me... hope someone explains why this
happens... this still looks like a bug to me...
BTW the problem arises when adding the second left outer join: when using
only 1 partitioned table (that is, only 1 left outer join) the 2 plans are
Scara Maccai wrote:
Thank you for your reply. This makes partitions unusable for me...
hope someone explains why this happens... this still looks like a bug
to me... BTW the problem arises when adding the second left outer
join: when using only 1 partitioned table (that is, only 1 left
outer
I'm still looking into it, but it seems the difference in the 2 plans is due to
the fact that when using partitions, the planner adds the time it would take to
index-scan the empty root table.
But that table will never contain any data...
Is there any chance to have the partitioning mechanism
Scara Maccai wrote:
explain analyze
select nome1,
thv3tralacc,
dltbfpgpdch
FROM cell_bsc_60_0610 as cell_bsc
left outer join teststscell73_0610_1 as data on
data.ne_id=cell_bsc.nome1
left outer join teststscell13_0610_1 as data1 on
Huh, clearly not the same query (you're using the partition
directly in
the first query) ... Doing two changes at once is not
helping your
case.
Sorry, I don't understand... of course I used the partition directly in the
first query... it's the difference between the two... what I don't
Scara Maccai wrote:
I'm still looking into it, but it seems the difference in the 2 plans is due to the fact
that when using partitions, the planner adds the time it would take to index-scan the
empty root table.
But that table will never contain any data...
Is there any chance to have the
- Index Scan using
teststscell13_pkey on teststscell13 data1 (cost=0.0..3.9
rows=1 width=16) (actual time=0.006..0.006 rows=0
loops=285)
doesn't make any sense: that table will never have any
data.
I'd like to have a way to tell that to Postgresql...
It's one index probe and
Scara Maccai wrote:
Huh, clearly not the same query (you're using the partition directly
in the first query) ... Doing two changes at once is not helping
your case.
Sorry, I don't understand... of course I used the partition directly
in the first query... it's the difference between the
What version are you using? Also,
please post the table
definitions (preferably in pg_dump -s format)
Table definition at the end of the msg.
Postgresql 8.4beta1
I'm not sure I agree with your assessment of the problem.
This is why I think that's the problem:
This is an explain of the
Scara Maccai wrote:
set enable_mergejoin=off;
set enable_hashjoin=off
http://explain-analyze.info/query_plans/3817-query-plan-2525
Ah, good - that's useful.
As you can see, the 2 root partition roots (teststscell73 and teststscell13)
take
teststscell73: 3.90 * 30120 loops = 117468 cost
Richard Huxton d...@archonet.com writes:
Scara Maccai wrote:
http://explain-analyze.info/query_plans/3817-query-plan-2525
Ah, good - that's useful.
Hmmm ... why is the inner Nested Loop estimated to produce 30120 rows,
when the cartesian product of its inputs would only be 285 * 14 = 3990
query using partitions explicitly (1):
explain analyze
select nome1,
thv3tralacc,
dltbfpgpdch
FROM cell_bsc_60_0610 as cell_bsc
left outer join teststscell73_0610_1 as data on
data.ne_id=cell_bsc.nome1
left outer join
18 matches
Mail list logo
