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 ha scritto:
> Da: Scara Maccai
> Oggetto: Re: [GENERAL] totally different plan when using partitions
> A: "Tom Lane"
> Cc: "
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
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:
http://explain-analyze.info/query_plans/3807-query-pla
> 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 sparc-s
Richard Huxton 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
rows? What PG ve
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
te
> 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 q
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 bet
> > -> 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 inde
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 p
> 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'
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 data
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:
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 j
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 ar
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 teststscel
Anyone??? This looks like a bug to me... or is there an explanation?
--- Mer 12/8/09, Scara Maccai ha scritto:
> Da: Scara Maccai
> Oggetto: [GENERAL] totally different plan when using partitions
> A: "pgsql-general"
> Data: Mercoledì 12 agosto 2009, 13:05
>
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 teststsce
18 matches
Mail list logo
