Hi Tom,
> Either poke into the code yourself, or submit a self-contained test
> case (the query alone does not a test case make). I can't offhand
> think of a reason for 8.2.5 to be slower than 8.2.4 ...
preparing the test case was easier than I expected. It's attached. Fast
planning on 8.2.4, very slow on 8.2.5.
Thanks,
Kuba
begin;
set geqo to off;
set join_collapse_limit to 1000;
set from_collapse_limit to 1000;
Create table a1
(
a1_pk Serial NOT NULL,
primary key (a1_pk)
) Without Oids;
Create table a2
(
a2_pk Serial NOT NULL,
a3_id Integer NOT NULL,
primary key (a2_pk)
) Without Oids;
Create table a4
(
a4_pk Serial NOT NULL,
a4_verze_pk_prvni Integer,
a4_verze_pk_aktualni Integer,
primary key (a4_pk)
) Without Oids;
Create table a6
(
a6_pk Serial NOT NULL,
primary key (a6_pk)
) Without Oids;
Create table a7_verze
(
a7_verze_pk Serial NOT NULL,
a7_pk Integer NOT NULL,
a7_verze_pk_predchozi Integer,
a7_verze_pk_dalsi Integer,
a9_pk Integer NOT NULL,
a1_pk_ulozil Integer NOT NULL,
primary key (a7_verze_pk)
) Without Oids;
Create table a7
(
a7_pk Serial NOT NULL,
a2_pk Integer NOT NULL,
a4_pk Integer NOT NULL,
a7_verze_pk_prvni Integer,
a7_verze_pk_aktualni Integer,
primary key (a7_pk)
) Without Oids;
Create table a8
(
a8_pk Serial NOT NULL,
a8_id Integer NOT NULL UNIQUE,
primary key (a8_pk)
) Without Oids;
Create table a9
(
a9_pk Serial NOT NULL,
a9_a10_id Integer NOT NULL,
a3_id Integer,
a8_id Integer NOT NULL,
primary key (a9_pk)
) Without Oids;
Create table a4_verze
(
a4_verze_pk Serial NOT NULL,
a4_pk Integer NOT NULL,
a4_verze_pk_predchozi Integer,
a4_verze_pk_dalsi Integer,
a1_pk_ulozil Integer NOT NULL,
a6_pk Integer,
primary key (a4_verze_pk)
) Without Oids;
Create table a9_a10
(
a9_a10_pk Serial NOT NULL,
a9_a10_id Integer NOT NULL UNIQUE,
primary key (a9_a10_pk)
) Without Oids;
CREATE TABLE abc_a7 (
abc_a7_pk integer NOT NULL,
a7_verze_pk integer NOT NULL,
a6_pk_trvala integer NOT NULL,
a3 int
);
CREATE TABLE abc_prirazeni_a10u (
abc_prirazeni_a10u_pk integer NOT NULL,
abc_a7_pk integer NOT NULL,
abc_a10_id integer NOT NULL
);
CREATE TABLE a3 (
a3_pk integer NOT NULL,
a3_id integer NOT NULL
);
CREATE TABLE abc_a10 (
abc_a10_pk integer NOT NULL,
abc_a10_id integer NOT NULL
);
select now();
explain
SELECT
1
FROM a7_verze ov
JOIN a7 op ON op.a7_pk = ov.a7_pk
JOIN a9 sp ON sp.a9_pk = ov.a9_pk
JOIN a9_a10 sc ON sc.a9_a10_id = sp.a9_a10_id
JOIN a8 fr ON fr.a8_id = sp.a8_id
JOIN a4 ko ON ko.a4_pk = op.a4_pk
JOIN a4_verze kov ON kov.a4_pk = ko.a4_pk
JOIN a2 ka ON ka.a2_pk = op.a2_pk
JOIN a3 pr ON pr.a3_id = ka.a3_id
JOIN a1 uz_ko ON uz_ko.a1_pk = kov.a1_pk_ulozil
JOIN abc_a7 abc_pripad ON abc_pripad.a7_verze_pk =
ov.a7_verze_pk
LEFT OUTER JOIN a6 a6_trvala ON a6_trvala.a6_pk =
abc_pripad.a6_pk_trvala
LEFT OUTER JOIN abc_a10 abc_a10_abc_pripad_a3 ON
abc_a10_abc_pripad_a3.abc_a10_id = abc_pripad.a3
;
select now();
rollback;
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
