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
Message-ID: <BAYC1-PASMTP01EE67AD3A0034394D85D895970@CEZ.ICE> (raw)
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pelletier_32@sympatico.ca
Subject: Re: Queries 15 times slower on 8.1 beta 2 than on 8.0
In-Reply-To: <BAYC1-PASMTP01EE67AD3A0034394D85D895970@CEZ.ICE>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox