different plan when using partitions: stats for inherited tables in joins

From: Scara Maccai <m_lists(at)yahoo(dot)it>
To: pgsql-hackers(at)postgresql(dot)org
Subject: different plan when using partitions: stats for inherited tables in joins
Date: 2009-08-17 12:33:42
Message-ID: 162452.61332.qm@web24609.mail.ird.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

following:

http://www.mail-archive.com/pgsql-general(at)postgresql(dot)org/msg135076.html

I recreated the problem using a join between 2 tables:

explain select nome1, dltbfpgpdch
FROM cell_bsc_60_0610 as cell_bsc
left outer join teststscell73_test_0610_1 as data on
data.ne_id=cell_bsc.nome1 where
data.time between '2006-10-01 00:00:00' and '2006-10-02 01:00:00'
and cell_bsc.nome2=2;

result:
http://explain-analyze.info/query_plans/3824-query-plan-2531

This should give similar results, but it doesn't (same query as above, but instead of using "teststscell73_test_0610_1" explicitly, I use "teststscell73_test", which is the table teststscell73_test_0610_1 inherits from):

explain select nome1, dltbfpgpdch
FROM cell_bsc_60_0610 as cell_bsc
left outer join teststscell73_test as data on
data.ne_id=cell_bsc.nome1 where
data.time between '2006-10-01 00:00:00' and '2006-10-02 01:00:00'
and cell_bsc.nome2=2;

result:
http://explain-analyze.info/query_plans/3823-query-plan-2530

The number of rows in the second explain are off.

Debugging the code (HEAD) I think the problem is in these lines (selfuncs.c:4059):

else if (rte->inh)
{
/*
* XXX This means the Var represents a column of an append
* relation. Later add code to look at the member relations and
* try to derive some kind of combined statistics?
*/

I guess that's the problem because running the 2 queries at line costsize.c:2794 (set_joinrel_size_estimates):

nrows = outer_rel->rows * inner_rel->rows * jselec;

I get:

query1 (the "ok" one):
"inner_rel->rows" = 58507.0
"outer_rel->rows" = 285.0
"jselec" = 4.3593112595119964E-4
"nrows" = 7268.931380017649

query2 (the "not ok" one):
"inner_rel->rows" = 58516.0
"outer_rel->rows" = 285.0
"jselec" = 0.0035087719298245615
"nrows" = 58516.0

The jselec is very different in the 2 cases.

query1 doesn't pass through that "else if (rte->inh)" in selfuncs; which means stats are found; while query2 passes through that code, and can't find real stats to be used by the join estimates...

This leads to the bad estimates of query2.

Of course, I know almost nothing about Postgres, so my analysis is likely to be totally wrong...

As I said before, I have a 500KB dump that recreates the problem.

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2009-08-17 12:43:40 Re: 8.5 development schedule
Previous Message Robert Haas 2009-08-17 12:04:01 Re: 8.5 development schedule