Re: Possible planner bug/regression introduced in 8.2.5 - Mailing list pgsql-bugs

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;