Thread: Queries 15 times slower on 8.1 beta 2 than on 8.0
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)
What stood out to me the most was:
On Sep 22, 2005, at 2:20 PM, Jean-Pierre Pelletier wrote:
-> Seq Scan on price p (cost=0.00..11317.75 rows=581475 width=4)
a) is the index there, b) have you analyzed, c) perhaps the planners have different default values for when to use an index vrs a seqscan... if you turn off seqscan, are the timings similar?
All indexes are there, and I've analyzed the three tables.
I turned off seq scan, the query plans became identical but the performance
was not better.
----- Original Message -----
From: Gavin M. RoySent: Thursday, September 22, 2005 5:32 PMSubject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0What stood out to me the most was:On Sep 22, 2005, at 2:20 PM, Jean-Pierre Pelletier wrote:-> Seq Scan on price p (cost=0.00..11317.75 rows=581475 width=4)
a) is the index there, b) have you analyzed, c) perhaps the planners have different default values for when to use an index vrs a seqscan... if you turn off seqscan, are the timings similar?
Jean-Pierre Pelletier wrote: > 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? You really have to post the results of "EXPLAIN ANALYZE" not just explain. So that we can tell what the planner is expecting, versus what really happened. John =:-> > > Thanks > Jean-Pierre Pelletier > e-djuster >
Attachment
Here are the explain analyze:
On 8.1 beta2:
"Nested Loop Left Join (cost=0.00..18591.77 rows=1 width=0) (actual
time=1320.302..2439.066 rows=1 loops=1)"
" Join Filter: ("outer".priceid = "inner".priceid)"
" -> Nested Loop Left Join (cost=0.00..5.59 rows=1 width=4) (actual
time=0.044..0.058 rows=1 loops=1)"
" -> Seq Scan on content c (cost=0.00..1.01 rows=1 width=8) (actual
time=0.009..0.011 rows=1 loops=1)"
" -> Index Scan using "Supplier Id" on supplier s (cost=0.00..4.56
rows=1 width=4) (actual time=0.016..0.022 rows=1 loops=1)"
" Index Cond: ("outer".supplierid = s.supplierid)"
" -> Seq Scan on price p (cost=0.00..11317.75 rows=581475 width=4)
(actual time=0.004..1143.720 rows=581475 loops=1)"
"Total runtime: 2439.211 ms"
On 8.0.1:
"Nested Loop Left Join (cost=0.00..11.02 rows=1 width=0) (actual
time=0.000..0.000 rows=1 loops=1)"
" -> Nested Loop Left Join (cost=0.00..5.48 rows=1 width=4) (actual
time=0.000..0.000 rows=1 loops=1)"
" -> Seq Scan on content c (cost=0.00..1.01 rows=1 width=8) (actual
time=0.000..0.000 rows=1 loops=1)"
" -> Index Scan using "Supplier Id" on supplier s (cost=0.00..4.46
rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)"
" Index Cond: ("outer".supplierid = s.supplierid)"
" -> Index Scan using "Price Id" on price p (cost=0.00..5.53 rows=1
width=4) (actual time=0.000..0.000 rows=1 loops=1)"
" Index Cond: ("outer".priceid = p.priceid)"
"Total runtime: 0.000 ms"
----- Original Message -----
From: "John Arbash Meinel" <john@arbash-meinel.com>
To: "Jean-Pierre Pelletier" <pelletier_32@sympatico.ca>
Cc: <pgsql-performance@postgresql.org>
Sent: Thursday, September 22, 2005 5:48 PM
Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
Jean-Pierre Pelletier wrote: > Here are the explain analyze: What is the explain analyze if you use "set enable_seqscan to off"? Also, can you post the output of: \d supplier \d price \d content Mostly I just want to see what the indexes are, in the case that you don't want to show us your schema. John =:->
Attachment
Jean-Pierre, First off, you're on Windows? > " -> Seq Scan on price p (cost=0.00..11317.75 rows=581475 width=4) > (actual time=0.004..1143.720 rows=581475 loops=1)" Well, this is your pain point. Can we see the index scan plan on 8.1? Given that it's *expecting* only one row, I can't understand why it's using a seq scan ... > "Nested Loop Left Join (cost=0.00..11.02 rows=1 width=0) (actual > time=0.000..0.000 rows=1 loops=1)" > " -> Nested Loop Left Join (cost=0.00..5.48 rows=1 width=4) (actual > time=0.000..0.000 rows=1 loops=1)" > "Total runtime: 0.000 ms" Feh, this looks like the "windows does not report times" bug, which makes it hard to compare ... -- --Josh Josh Berkus Aglio Database Solutions San Francisco
With enable-seq-scan = off, it runs in 350 ms so better than 484 ms
but still much slower than 32 ms in 8.0.1.
==============================================
Table "public.content"
Column | Type | Modifiers
------------+---------+-----------
contentid | integer | not null
supplierid | integer |
priceid | integer |
Table "public.price"
Column | Type | Modifiers
-----------------------+--------------------------------+-----------
priceid | integer | not null
itemid | integer |
supplierid | integer |
locationid | smallint |
fromdate | date |
unitvalue | numeric |
insertedbypersonid | integer |
lastupdatedbypersonid | integer |
inserttimestamp | timestamp(0) without time zone |
lastupdatetimestamp | timestamp(0) without time zone |
Indexes:
"price_pkey" PRIMARY KEY, btree (priceid)
Table "public.supplier"
Column | Type |
Modifie
rs
---------------------+--------------------------------+-------------------------
---------------------
supplierid | integer | not null default
nextval
('SupplierId'::text)
supplierdescription | character varying(50) | not null
inserttimestamp | timestamp(0) without time zone | default now()
approvaldate | date |
Indexes:
"Supplier Id" PRIMARY KEY, btree (supplierid)
"Supplier Description" UNIQUE, btree (upper(supplierdescription::text))
"Supplier.InsertTimestamp" btree (inserttimestamp)
Check constraints:
"Supplier Name cannot be empty" CHECK (btrim(supplierdescription::text)
<> ''::tex
================================================================================
Explan analyze with enable-seq-scan = off on 8.1 beta2
QUERY PLAN
--------------------------------------------------------------------------------
------------------------------------------------------------
Merge Left Join (cost=100000005.60..101607964.74 rows=1 width=0) (actual
time=
729.067..729.078 rows=1 loops=1)
Merge Cond: ("outer".priceid = "inner".priceid)
-> Sort (cost=100000005.60..100000005.60 rows=1 width=4) (actual
time=0.064
..0.067 rows=1 loops=1)
Sort Key: c.priceid
-> Nested Loop Left Join (cost=100000000.00..100000005.59 rows=1
widt
h=4) (actual time=0.038..0.049 rows=1 loops=1)
-> Seq Scan on content c (cost=100000000.00..100000001.01
ro
ws=1 width=8) (actual time=0.008..0.011 rows=1 loops=1)
-> Index Scan using "Supplier Id" on supplier s
(cost=0.00..4.5
6 rows=1 width=4) (actual time=0.016..0.019 rows=1 loops=1)
Index Cond: ("outer".supplierid = s.supplierid)
-> Index Scan using "Price Id" on price p (cost=0.00..1606505.44
rows=58147
5 width=4) (actual time=0.008..370.854 rows=164842 loops=1)
Total runtime: 729.192 ms
----- Original Message -----
From: "John Arbash Meinel" <john@arbash-meinel.com>
To: "Jean-Pierre Pelletier" <pelletier_32@sympatico.ca>
Cc: <pgsql-performance@postgresql.org>
Sent: Thursday, September 22, 2005 6:03 PM
Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
Have tried adjusting the effective_cache_size so that you don't the
planner may produce a better explain plan for you and not needing to set
seqscan to off.
--
Husam
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Jean-Pierre
Pelletier
Sent: Thursday, September 22, 2005 3:28 PM
To: John Arbash Meinel
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
With enable-seq-scan = off, it runs in 350 ms so better than 484 ms
but still much slower than 32 ms in 8.0.1.
==============================================
Table "public.content"
Column | Type | Modifiers
------------+---------+-----------
contentid | integer | not null
supplierid | integer |
priceid | integer |
Table "public.price"
Column | Type | Modifiers
-----------------------+--------------------------------+-----------
priceid | integer | not null
itemid | integer |
supplierid | integer |
locationid | smallint |
fromdate | date |
unitvalue | numeric |
insertedbypersonid | integer |
lastupdatedbypersonid | integer |
inserttimestamp | timestamp(0) without time zone |
lastupdatetimestamp | timestamp(0) without time zone |
Indexes:
"price_pkey" PRIMARY KEY, btree (priceid)
Table "public.supplier"
Column | Type |
Modifie
rs
---------------------+--------------------------------+-----------------
--------
---------------------
supplierid | integer | not null default
nextval
('SupplierId'::text)
supplierdescription | character varying(50) | not null
inserttimestamp | timestamp(0) without time zone | default now()
approvaldate | date |
Indexes:
"Supplier Id" PRIMARY KEY, btree (supplierid)
"Supplier Description" UNIQUE, btree
(upper(supplierdescription::text))
"Supplier.InsertTimestamp" btree (inserttimestamp)
Check constraints:
"Supplier Name cannot be empty" CHECK
(btrim(supplierdescription::text)
<> ''::tex
========================================================================
========
Explan analyze with enable-seq-scan = off on 8.1 beta2
QUERY
PLAN
------------------------------------------------------------------------
--------
------------------------------------------------------------
Merge Left Join (cost=100000005.60..101607964.74 rows=1 width=0)
(actual
time=
729.067..729.078 rows=1 loops=1)
Merge Cond: ("outer".priceid = "inner".priceid)
-> Sort (cost=100000005.60..100000005.60 rows=1 width=4) (actual
time=0.064
..0.067 rows=1 loops=1)
Sort Key: c.priceid
-> Nested Loop Left Join (cost=100000000.00..100000005.59
rows=1
widt
h=4) (actual time=0.038..0.049 rows=1 loops=1)
-> Seq Scan on content c
(cost=100000000.00..100000001.01
ro
ws=1 width=8) (actual time=0.008..0.011 rows=1 loops=1)
-> Index Scan using "Supplier Id" on supplier s
(cost=0.00..4.5
6 rows=1 width=4) (actual time=0.016..0.019 rows=1 loops=1)
Index Cond: ("outer".supplierid = s.supplierid)
-> Index Scan using "Price Id" on price p (cost=0.00..1606505.44
rows=58147
5 width=4) (actual time=0.008..370.854 rows=164842 loops=1)
Total runtime: 729.192 ms
----- Original Message -----
From: "John Arbash Meinel" <john@arbash-meinel.com>
To: "Jean-Pierre Pelletier" <pelletier_32@sympatico.ca>
Cc: <pgsql-performance@postgresql.org>
Sent: Thursday, September 22, 2005 6:03 PM
Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
**********************************************************************
This message contains confidential information intended only for the
use of the addressee(s) named above and may contain information that
is legally privileged. If you are not the addressee, or the person
responsible for delivering it to the addressee, you are hereby
notified that reading, disseminating, distributing or copying this
message is strictly prohibited. If you have received this message by
mistake, please immediately notify us by replying to the message and
delete the original message immediately thereafter.
Thank you. FADLD Tag
**********************************************************************
On Thu, Sep 22, 2005 at 03:19:05PM -0700, Josh Berkus wrote:
> > " -> Seq Scan on price p (cost=0.00..11317.75 rows=581475 width=4)
> > (actual time=0.004..1143.720 rows=581475 loops=1)"
>
> Well, this is your pain point. Can we see the index scan plan on 8.1?
> Given that it's *expecting* only one row, I can't understand why it's
> using a seq scan ...
I've created a simplified, self-contained test case for this:
CREATE TABLE price (
priceid integer PRIMARY KEY
);
CREATE TABLE supplier (
supplierid integer PRIMARY KEY
);
CREATE TABLE content (
contentid integer PRIMARY KEY,
supplierid integer NOT NULL REFERENCES supplier,
priceid integer NOT NULL REFERENCES price
);
INSERT INTO price (priceid) SELECT * FROM generate_series(1, 50000);
INSERT INTO supplier (supplierid) SELECT * FROM generate_series(1, 10000);
INSERT INTO content (contentid, supplierid, priceid) VALUES (1, 1, 50000);
ANALYZE price;
ANALYZE supplier;
ANALYZE content;
EXPLAIN ANALYZE
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;
Here's the EXPLAIN ANALYZE from 8.0.3:
Nested Loop Left Join (cost=0.00..7.06 rows=1 width=0) (actual time=0.180..0.232 rows=1 loops=1)
-> Nested Loop Left Join (cost=0.00..4.04 rows=1 width=4) (actual time=0.105..0.133 rows=1 loops=1)
-> Seq Scan on content c (cost=0.00..1.01 rows=1 width=8) (actual time=0.021..0.029 rows=1 loops=1)
-> Index Scan using supplier_pkey on supplier s (cost=0.00..3.01 rows=1 width=4) (actual time=0.052..0.059
rows=1loops=1)
Index Cond: ("outer".supplierid = s.supplierid)
-> Index Scan using price_pkey on price p (cost=0.00..3.01 rows=1 width=4) (actual time=0.046..0.055 rows=1
loops=1)
Index Cond: ("outer".priceid = p.priceid)
Total runtime: 0.582 ms
Here it is from 8.1beta2:
Merge Right Join (cost=4.05..1054.06 rows=1 width=0) (actual time=676.863..676.895 rows=1 loops=1)
Merge Cond: ("outer".priceid = "inner".priceid)
-> Index Scan using price_pkey on price p (cost=0.00..925.00 rows=50000 width=4) (actual time=0.035..383.345
rows=50000loops=1)
-> Sort (cost=4.05..4.05 rows=1 width=4) (actual time=0.152..0.159 rows=1 loops=1)
Sort Key: c.priceid
-> Nested Loop Left Join (cost=0.00..4.04 rows=1 width=4) (actual time=0.082..0.111 rows=1 loops=1)
-> Seq Scan on content c (cost=0.00..1.01 rows=1 width=8) (actual time=0.016..0.024 rows=1 loops=1)
-> Index Scan using supplier_pkey on supplier s (cost=0.00..3.01 rows=1 width=4) (actual
time=0.039..0.047rows=1 loops=1)
Index Cond: ("outer".supplierid = s.supplierid)
Total runtime: 677.563 ms
If we change content's priceid then we get the same plan but faster results:
UPDATE content SET priceid = 1;
Merge Right Join (cost=4.05..1054.06 rows=1 width=0) (actual time=0.268..0.303 rows=1 loops=1)
Merge Cond: ("outer".priceid = "inner".priceid)
-> Index Scan using price_pkey on price p (cost=0.00..925.00 rows=50000 width=4) (actual time=0.049..0.061 rows=2
loops=1)
-> Sort (cost=4.05..4.05 rows=1 width=4) (actual time=0.187..0.192 rows=1 loops=1)
Sort Key: c.priceid
-> Nested Loop Left Join (cost=0.00..4.04 rows=1 width=4) (actual time=0.099..0.128 rows=1 loops=1)
-> Seq Scan on content c (cost=0.00..1.01 rows=1 width=8) (actual time=0.025..0.033 rows=1 loops=1)
-> Index Scan using supplier_pkey on supplier s (cost=0.00..3.01 rows=1 width=4) (actual
time=0.046..0.053rows=1 loops=1)
Index Cond: ("outer".supplierid = s.supplierid)
Total runtime: 0.703 ms
--
Michael Fuhr
I don't know if it makes a difference but in my tables,
content.supplierid and content.priceid were nullable.
----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "Josh Berkus" <josh@agliodbs.com>
Cc: <pgsql-performance@postgresql.org>; "Jean-Pierre Pelletier"
<pelletier_32@sympatico.ca>; "John Arbash Meinel" <john@arbash-meinel.com>
Sent: Thursday, September 22, 2005 6:54 PM
Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
> On Thu, Sep 22, 2005 at 03:19:05PM -0700, Josh Berkus wrote:
>> > " -> Seq Scan on price p (cost=0.00..11317.75 rows=581475 width=4)
>> > (actual time=0.004..1143.720 rows=581475 loops=1)"
>>
>> Well, this is your pain point. Can we see the index scan plan on 8.1?
>> Given that it's *expecting* only one row, I can't understand why it's
>> using a seq scan ...
>
> I've created a simplified, self-contained test case for this:
>
> CREATE TABLE price (
> priceid integer PRIMARY KEY
> );
>
> CREATE TABLE supplier (
> supplierid integer PRIMARY KEY
> );
>
> CREATE TABLE content (
> contentid integer PRIMARY KEY,
> supplierid integer NOT NULL REFERENCES supplier,
> priceid integer NOT NULL REFERENCES price
> );
>
> INSERT INTO price (priceid) SELECT * FROM generate_series(1, 50000);
> INSERT INTO supplier (supplierid) SELECT * FROM generate_series(1, 10000);
> INSERT INTO content (contentid, supplierid, priceid) VALUES (1, 1, 50000);
>
> ANALYZE price;
> ANALYZE supplier;
> ANALYZE content;
>
> EXPLAIN ANALYZE
> 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;
>
> Here's the EXPLAIN ANALYZE from 8.0.3:
>
> Nested Loop Left Join (cost=0.00..7.06 rows=1 width=0) (actual
> time=0.180..0.232 rows=1 loops=1)
> -> Nested Loop Left Join (cost=0.00..4.04 rows=1 width=4) (actual
> time=0.105..0.133 rows=1 loops=1)
> -> Seq Scan on content c (cost=0.00..1.01 rows=1 width=8)
> (actual time=0.021..0.029 rows=1 loops=1)
> -> Index Scan using supplier_pkey on supplier s (cost=0.00..3.01
> rows=1 width=4) (actual time=0.052..0.059 rows=1 loops=1)
> Index Cond: ("outer".supplierid = s.supplierid)
> -> Index Scan using price_pkey on price p (cost=0.00..3.01 rows=1
> width=4) (actual time=0.046..0.055 rows=1 loops=1)
> Index Cond: ("outer".priceid = p.priceid)
> Total runtime: 0.582 ms
>
> Here it is from 8.1beta2:
>
> Merge Right Join (cost=4.05..1054.06 rows=1 width=0) (actual
> time=676.863..676.895 rows=1 loops=1)
> Merge Cond: ("outer".priceid = "inner".priceid)
> -> Index Scan using price_pkey on price p (cost=0.00..925.00
> rows=50000 width=4) (actual time=0.035..383.345 rows=50000 loops=1)
> -> Sort (cost=4.05..4.05 rows=1 width=4) (actual time=0.152..0.159
> rows=1 loops=1)
> Sort Key: c.priceid
> -> Nested Loop Left Join (cost=0.00..4.04 rows=1 width=4)
> (actual time=0.082..0.111 rows=1 loops=1)
> -> Seq Scan on content c (cost=0.00..1.01 rows=1 width=8)
> (actual time=0.016..0.024 rows=1 loops=1)
> -> Index Scan using supplier_pkey on supplier s
> (cost=0.00..3.01 rows=1 width=4) (actual time=0.039..0.047 rows=1 loops=1)
> Index Cond: ("outer".supplierid = s.supplierid)
> Total runtime: 677.563 ms
>
> If we change content's priceid then we get the same plan but faster
> results:
>
> UPDATE content SET priceid = 1;
>
> Merge Right Join (cost=4.05..1054.06 rows=1 width=0) (actual
> time=0.268..0.303 rows=1 loops=1)
> Merge Cond: ("outer".priceid = "inner".priceid)
> -> Index Scan using price_pkey on price p (cost=0.00..925.00
> rows=50000 width=4) (actual time=0.049..0.061 rows=2 loops=1)
> -> Sort (cost=4.05..4.05 rows=1 width=4) (actual time=0.187..0.192
> rows=1 loops=1)
> Sort Key: c.priceid
> -> Nested Loop Left Join (cost=0.00..4.04 rows=1 width=4)
> (actual time=0.099..0.128 rows=1 loops=1)
> -> Seq Scan on content c (cost=0.00..1.01 rows=1 width=8)
> (actual time=0.025..0.033 rows=1 loops=1)
> -> Index Scan using supplier_pkey on supplier s
> (cost=0.00..3.01 rows=1 width=4) (actual time=0.046..0.053 rows=1 loops=1)
> Index Cond: ("outer".supplierid = s.supplierid)
> Total runtime: 0.703 ms
>
> --
> Michael Fuhr
my settings are: effective_cache_size = 1000 random_page_cost = 4 work_mem = 20000 ----- Original Message ----- From: "Josh Berkus" <josh@agliodbs.com> To: "Jean-Pierre Pelletier" <pelletier_32@sympatico.ca> Sent: Thursday, September 22, 2005 6:58 PM Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0 > Jean-Pierre, > >> How do I produce an "Index scan plan" ? > > You just did. What's your effective_cache_size set to? > random_page_cost? work_mem? > > -- > --Josh > > Josh Berkus > Aglio Database Solutions > San Francisco
Michael Fuhr <mike@fuhr.org> writes:
> I've created a simplified, self-contained test case for this:
I see the problem --- I broke best_inner_indexscan() for some cases
where the potential indexscan clause is an outer-join ON clause.
regards, tom lane
Jean-Pierre, > effective_cache_size = 1000 Try setting this to 16,384 as a test. > random_page_cost = 4 Try setting this to 2.5 as a test. > work_mem = 20000 -- --Josh Josh Berkus Aglio Database Solutions San Francisco
On Thu, Sep 22, 2005 at 07:07:41PM -0400, Jean-Pierre Pelletier wrote: > I don't know if it makes a difference but in my tables, > content.supplierid and content.priceid were nullable. That makes no difference in the tests I've done. Tom Lane says he's found the problem; I expect he'll be committing a fix shortly. -- Michael Fuhr
The recommendation for effective_cache_size is about 2/3 of your
server's physical RAM (if the server is dedicated only for postgres).
This should have a significant impact on whether Postgres planner
chooses indexes over sequential scans.
--
Husam
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Jean-Pierre
Pelletier
Sent: Thursday, September 22, 2005 4:10 PM
To: josh@agliodbs.com
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
my settings are:
effective_cache_size = 1000
random_page_cost = 4
work_mem = 20000
----- Original Message -----
From: "Josh Berkus" <josh@agliodbs.com>
To: "Jean-Pierre Pelletier" <pelletier_32@sympatico.ca>
Sent: Thursday, September 22, 2005 6:58 PM
Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
> Jean-Pierre,
>
>> How do I produce an "Index scan plan" ?
>
> You just did. What's your effective_cache_size set to?
> random_page_cost? work_mem?
>
> --
> --Josh
>
> Josh Berkus
> Aglio Database Solutions
> San Francisco
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
**********************************************************************
This message contains confidential information intended only for the
use of the addressee(s) named above and may contain information that
is legally privileged. If you are not the addressee, or the person
responsible for delivering it to the addressee, you are hereby
notified that reading, disseminating, distributing or copying this
message is strictly prohibited. If you have received this message by
mistake, please immediately notify us by replying to the message and
delete the original message immediately thereafter.
Thank you. FADLD Tag
**********************************************************************
Thanks everybody for your help, I'll be awaiting the fix. I've also noticed that pg_stat_activity is always empty even if stats_start_collector = on ----- Original Message ----- From: "Michael Fuhr" <mike@fuhr.org> To: "Jean-Pierre Pelletier" <pelletier_32@sympatico.ca> Cc: "Josh Berkus" <josh@agliodbs.com>; <pgsql-performance@postgresql.org>; "John Arbash Meinel" <john@arbash-meinel.com> Sent: Thursday, September 22, 2005 7:17 PM Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0 > On Thu, Sep 22, 2005 at 07:07:41PM -0400, Jean-Pierre Pelletier wrote: >> I don't know if it makes a difference but in my tables, >> content.supplierid and content.priceid were nullable. > > That makes no difference in the tests I've done. > > Tom Lane says he's found the problem; I expect he'll be committing > a fix shortly. > > -- > Michael Fuhr > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
Jean-Pierre, > Thanks everybody for your help, I'll be awaiting the fix. > > I've also noticed that pg_stat_activity is always empty even if > stats_start_collector = on Yes, I believe that this is a know Windows issue. Not sure if it's fixed in 8.1. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
Michael Fuhr <mike@fuhr.org> writes:
> Tom Lane says he's found the problem; I expect he'll be committing
> a fix shortly.
The attached patch allows it to generate the expected plan, at least
in the test case I tried.
regards, tom lane
*** src/backend/optimizer/path/indxpath.c.orig Sun Aug 28 18:47:20 2005
--- src/backend/optimizer/path/indxpath.c Thu Sep 22 19:17:41 2005
***************
*** 955,969 ****
/*
* Examine each joinclause in the joininfo list to see if it matches any
* key of any index. If so, add the clause's other rels to the result.
- * (Note: we consider only actual participants, not extraneous rels
- * possibly mentioned in required_relids.)
*/
foreach(l, rel->joininfo)
{
RestrictInfo *joininfo = (RestrictInfo *) lfirst(l);
Relids other_rels;
! other_rels = bms_difference(joininfo->clause_relids, rel->relids);
if (matches_any_index(joininfo, rel, other_rels))
outer_relids = bms_join(outer_relids, other_rels);
else
--- 955,967 ----
/*
* Examine each joinclause in the joininfo list to see if it matches any
* key of any index. If so, add the clause's other rels to the result.
*/
foreach(l, rel->joininfo)
{
RestrictInfo *joininfo = (RestrictInfo *) lfirst(l);
Relids other_rels;
! other_rels = bms_difference(joininfo->required_relids, rel->relids);
if (matches_any_index(joininfo, rel, other_rels))
outer_relids = bms_join(outer_relids, other_rels);
else
Explain analyze on my 8.0.1 installation does report the time for slower queries but for this small query it reports 0.000 ms ----- Original Message ----- From: "Josh Berkus" <josh@agliodbs.com> To: <pgsql-performance@postgresql.org> Cc: "Jean-Pierre Pelletier" <pelletier_32@sympatico.ca>; "John Arbash Meinel" <john@arbash-meinel.com> Sent: Thursday, September 22, 2005 6:19 PM Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0 > Jean-Pierre, > > First off, you're on Windows? > >> " -> Seq Scan on price p (cost=0.00..11317.75 rows=581475 width=4) >> (actual time=0.004..1143.720 rows=581475 loops=1)" > > Well, this is your pain point. Can we see the index scan plan on 8.1? > Given that it's *expecting* only one row, I can't understand why it's > using a seq scan ... > >> "Nested Loop Left Join (cost=0.00..11.02 rows=1 width=0) (actual >> time=0.000..0.000 rows=1 loops=1)" >> " -> Nested Loop Left Join (cost=0.00..5.48 rows=1 width=4) (actual >> time=0.000..0.000 rows=1 loops=1)" >> "Total runtime: 0.000 ms" > > Feh, this looks like the "windows does not report times" bug, which makes > it hard to compare ... > > -- > --Josh > > Josh Berkus > Aglio Database Solutions > San Francisco > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq