Skip site navigation (1) Skip section navigation (2)

Re: Possible planner bug/regression introduced in 8.2.5

From: Jakub Ouhrabka <kuba(at)comgate(dot)cz>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Possible planner bug/regression introduced in 8.2.5
Date: 2007-10-24 16:51:22
Message-ID: 471F780A.2020408@comgate.cz (view raw or flat)
Thread:
Lists: 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;

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2007-10-24 20:57:13
Subject: Re: Possible planner bug/regression introduced in 8.2.5
Previous:From: Dirk MoebiusDate: 2007-10-24 16:23:37
Subject: BUG #3693: PSQLException when using ResultSet.getLong(String) in turkish locale

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group