Re: totally different plan when using partitions

[Scara Maccai <m_lists@xxxxxxxx>]

> 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 query using

set enable_mergejoin=off;
set enable_hashjoin=off

http://explain-analyze.info/query_plans/3817-query-plan-2525

As you can see, the 2 root partition roots (teststscell73 and teststscell13) take

teststscell73: 3.90 * 30120 loops = 117468 cost
teststscell13: 3.89 * 15964 loops = 62099 cost

total: 179567 cost out of 377398 total cost of the query...

basically the 2 empty tables index access take 1/2 of the query planned time... while they should take 0, since they're empty!!!
Since I can't tell postgresql they're empty, it assumes they have to be accessed... 
As I said, when using partitioning, I would like the option of flagging some tables (what I call "the root tables") as "Always empty", so that the planner wouldn't care of them...










CREATE TABLE cell_bsc_60_0610 (
    id integer NOT NULL,
    nome1 integer,
    nome2 integer,
    starttime timestamp without time zone,
    endtime timestamp without time zone
);



CREATE TABLE teststscell13 (
    "time" timestamp without time zone NOT NULL,
    ne_id integer NOT NULL,
    void0 integer,
    void1 integer,
    void2 integer,
    id1 integer,
    [....]
    mutil33 integer,
    mutil12 integer
);


CREATE TABLE teststscell13_0610_1 (CONSTRAINT teststscell13_0610_1_time_check CHECK ((("time" >= '2006-10-01 00:00:00'::timestamp without time zone) AND ("time" < '2006-10-09 00:00:00'::timestamp without time zone)))
)
INHERITS (teststscell13);

CREATE TABLE teststscell13_0610_2 (CONSTRAINT teststscell13_0610_2_time_check CHECK ((("time" >= '2006-10-09 00:00:00'::timestamp without time zone) AND ("time" < '2006-10-16 00:00:00'::timestamp without time zone)))
)
INHERITS (teststscell13);

CREATE TABLE teststscell13_0610_3 (CONSTRAINT teststscell13_0610_3_time_check CHECK ((("time" >= '2006-10-16 00:00:00'::timestamp without time zone) AND ("time" < '2006-10-24 00:00:00'::timestamp without time zone)))
)
INHERITS (teststscell13);

CREATE TABLE teststscell13_0610_4 (CONSTRAINT teststscell13_0610_4_time_check CHECK ((("time" >= '2006-10-24 00:00:00'::timestamp without time zone) AND ("time" < '2006-11-01 00:00:00'::timestamp without time zone)))
)
INHERITS (teststscell13);

CREATE TABLE teststscell73 (
    "time" timestamp without time zone NOT NULL,
    ne_id integer NOT NULL,
    mutil22 integer,
    traffdlgprsscan integer,
    dlbpdch integer,
    dlgpdch integer,
    dlepdch integer,
    dltbfpbpdch integer,
		[...]
    void504 integer,
    void505 integer,
    void506 integer,
    void507 integer,
    void508 integer,
    void509 integer,
    void510 integer,
    void511 integer
);


CREATE TABLE teststscell73_0610_1 (CONSTRAINT teststscell73_0610_1_time_check CHECK ((("time" >= '2006-10-01 00:00:00'::timestamp without time zone) AND ("time" < '2006-10-09 00:00:00'::timestamp without time zone)))
)
INHERITS (teststscell73);

CREATE TABLE teststscell73_0610_2 (CONSTRAINT teststscell73_0610_2_time_check CHECK ((("time" >= '2006-10-09 00:00:00'::timestamp without time zone) AND ("time" < '2006-10-16 00:00:00'::timestamp without time zone)))
)
INHERITS (teststscell73);

CREATE TABLE teststscell73_0610_3 (CONSTRAINT teststscell73_0610_3_time_check CHECK ((("time" >= '2006-10-16 00:00:00'::timestamp without time zone) AND ("time" < '2006-10-24 00:00:00'::timestamp without time zone)))
)
INHERITS (teststscell73);

CREATE TABLE teststscell73_0610_4 (CONSTRAINT teststscell73_0610_4_time_check CHECK ((("time" >= '2006-10-24 00:00:00'::timestamp without time zone) AND ("time" < '2006-11-01 00:00:00'::timestamp without time zone)))
)
INHERITS (teststscell73);

ALTER TABLE ONLY teststscell13_0610_1
    ADD CONSTRAINT teststscell13_0610_1_pkey PRIMARY KEY (ne_id, "time");
ALTER TABLE ONLY teststscell13_0610_2
    ADD CONSTRAINT teststscell13_0610_2_pkey PRIMARY KEY (ne_id, "time");
ALTER TABLE ONLY teststscell13_0610_3
    ADD CONSTRAINT teststscell13_0610_3_pkey PRIMARY KEY (ne_id, "time");
ALTER TABLE ONLY teststscell13_0610_4
    ADD CONSTRAINT teststscell13_0610_4_pkey PRIMARY KEY (ne_id, "time");
ALTER TABLE ONLY teststscell13
    ADD CONSTRAINT teststscell13_pkey PRIMARY KEY (ne_id, "time");
ALTER TABLE ONLY teststscell73_0610_1
    ADD CONSTRAINT teststscell73_0610_1_pkey PRIMARY KEY (ne_id, "time");
ALTER TABLE ONLY teststscell73_0610_2
    ADD CONSTRAINT teststscell73_0610_2_pkey PRIMARY KEY (ne_id, "time");
ALTER TABLE ONLY teststscell73_0610_3
    ADD CONSTRAINT teststscell73_0610_3_pkey PRIMARY KEY (ne_id, "time");
ALTER TABLE ONLY teststscell73_0610_4
    ADD CONSTRAINT teststscell73_0610_4_pkey PRIMARY KEY (ne_id, "time");
ALTER TABLE ONLY teststscell73
    ADD CONSTRAINT teststscell73_pkey PRIMARY KEY (ne_id, "time");

CREATE INDEX cell_bsc_60_idx ON cell_bsc_60_0610 USING btree (nome2, nome1);

 




-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general