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
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)
