Message-ID: <BAYC1-PASMTP01EE67AD3A0034394D85D895970@CEZ.ICE>
Message-ID: <004201c5bfbb$6745bba0$6501a8c0@JP>
From: "Jean-Pierre Pelletier" <pelletier_32@sympatico.ca>
To: <pgsql-performance@postgresql.org>
Subject: Queries 15 times slower on 8.1 beta 2 than on 8.0
Date: Thu, 22 Sep 2005 17:20:04 -0400
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit

Hi,

I've got many queries running much slower on 8.1 beta2 than on 8.0.1
Here is a simplified one that takes 484 ms on 8.1 and 32 ms on 8.0.1.

select
   0
from
   Content C

   left outer join Supplier S
   on  C.SupplierId = S.SupplierId

   left outer join Price P
   on C.PriceId = P.PriceId;

Any ideas why it's slower?

Thanks
Jean-Pierre Pelletier
e-djuster

======================================================

create table Price (
   PriceId               INTEGER      NOT NULL DEFAULT NEXTVAL('PriceId'),
   ItemId                INTEGER      NOT NULL,
   SupplierId            INTEGER      NOT NULL,
   LocationId            SMALLINT         NULL,
   FromDate              DATE         NOT NULL DEFAULT CURRENT_DATE,
   UnitValue             DECIMAL      NOT NULL,
   InsertedByPersonId    INTEGER      NOT NULL,
   LastUpdatedByPersonId INTEGER          NULL,
   InsertTimestamp       TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
   LastUpdateTimeStamp   TIMESTAMP(0)     NULL
);

alter table price add primary key (priceid);

create table Supplier (
  SupplierId          INTEGER     NOT NULL DEFAULT NEXTVAL('SupplierId'),
  SupplierDescription VARCHAR(50) NOT NULL,
  InsertTimestamp     TIMESTAMP(0)    NULL DEFAULT CURRENT_TIMESTAMP,
  ApprovalDate        DATE            NULL
);

alter table supplier add primary key (supplierid);

-- I've only put one row in table Content because it was sufficient to
produce
-- the slowdown

create table content (contentid integer not null, supplierid integer,
priceid integer);
insert into content VALUES (148325, 12699, 388026);

vacuum analyze content; -- 1 row
vacuum analyze price; -- 581475 rows
vacuum analyze supplier; -- 10139 rows

======================================================
Here are the query plans:

On "PostgreSQL 8.1beta2 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
3.4.2 (mingw-special)"

explain select    0 from    Content C      LEFT OUTER JOIN Supplier S    ON
C.SupplierId = S.SupplierId      LEFT OUTER JOIN Price P    ON C.PriceId =
P.PriceId;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.00..18591.77 rows=1 width=0)
   Join Filter: ("outer".priceid = "inner".priceid)
   ->  Nested Loop Left Join  (cost=0.00..5.59 rows=1 width=4)
         ->  Seq Scan on content c  (cost=0.00..1.01 rows=1 width=8)
         ->  Index Scan using "Supplier Id" on supplier s  (cost=0.00..4.56
rows=1 width=4)
               Index Cond: ("outer".supplierid = s.supplierid)
   ->  Seq Scan on price p  (cost=0.00..11317.75 rows=581475 width=4)


"PostgreSQL 8.0.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special)"

explain select    0 from    Content C      LEFT OUTER JOIN Supplier S    ON
C.SupplierId = S.SupplierId      LEFT OUTER JOIN Price P    ON C.PriceId =
P.PriceId;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.00..11.08 rows=1 width=0)
   ->  Nested Loop Left Join  (cost=0.00..5.53 rows=1 width=4)
         ->  Seq Scan on content c  (cost=0.00..1.01 rows=1 width=8)
         ->  Index Scan using "Supplier Id" on supplier s  (cost=0.00..4.51
rows=1 width=4)
               Index Cond: ("outer".supplierid = s.supplierid)
   ->  Index Scan using price_pkey on price p  (cost=0.00..5.53 rows=1
width=4)
         Index Cond: ("outer".priceid = p.priceid)