Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
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" To: Cc: "Jean-Pierre Pelletier" <[EMAIL PROTECTED]>; "John Arbash Meinel" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
Michael Fuhr <[EMAIL PROTECTED]> 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
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
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" <[EMAIL PROTECTED]> To: "Jean-Pierre Pelletier" <[EMAIL PROTECTED]> Cc: "Josh Berkus" ; ; "John Arbash Meinel" <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 = 2 - Original Message - From: "Josh Berkus" To: "Jean-Pierre Pelletier" <[EMAIL PROTECTED]> 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 ** ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
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 = 2 -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
Michael Fuhr <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
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 = 2 - Original Message - From: "Josh Berkus" To: "Jean-Pierre Pelletier" <[EMAIL PROTECTED]> 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
Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
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" <[EMAIL PROTECTED]>
To: "Josh Berkus"
Cc: ; "Jean-Pierre Pelletier"
<[EMAIL PROTECTED]>; "John Arbash Meinel" <[EMAIL PROTECTED]>
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, 5);
INSERT INTO supplier (supplierid) SELECT * FROM generate_series(1, 1);
INSERT INTO content (contentid, supplierid, priceid) VALUES (1, 1, 5);
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 pON 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=5 width=4) (actual time=0.035..383.345 rows=5 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=5 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
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
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, 5);
INSERT INTO supplier (supplierid) SELECT * FROM generate_series(1, 1);
INSERT INTO content (contentid, supplierid, priceid) VALUES (1, 1, 5);
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 pON 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=5
width=4) (actual time=0.035..383.345 rows=5 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=5
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
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Fw: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
- Original Message - From: "Jean-Pierre Pelletier" <[EMAIL PROTECTED]> To: Sent: Thursday, September 22, 2005 6:37 PM Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0 How do I produce an "Index scan plan" ? - Original Message - From: "Josh Berkus" To: Cc: "Jean-Pierre Pelletier" <[EMAIL PROTECTED]>; "John Arbash Meinel" <[EMAIL PROTECTED]> 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 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
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: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 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=10005.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=10005.60..10005.60 rows=1 width=4) (actual
time=0.064
..0.067 rows=1 loops=1)
Sort Key: c.priceid
-> Nested Loop Left Join (cost=1.00..10005.59
rows=1
widt
h=4) (actual time=0.038..0.049 rows=1 loops=1)
-> Seq Scan on content c
(cost=1.00..10001.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" <[EMAIL PROTECTED]>
To: "Jean-Pierre Pelletier" <[EMAIL PROTECTED]>
Cc:
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 [EMAIL PROTECTED] 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
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=10005.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=10005.60..10005.60 rows=1 width=4) (actual
time=0.064
..0.067 rows=1 loops=1)
Sort Key: c.priceid
-> Nested Loop Left Join (cost=1.00..10005.59 rows=1
widt
h=4) (actual time=0.038..0.049 rows=1 loops=1)
-> Seq Scan on content c (cost=1.00..10001.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" <[EMAIL PROTECTED]>
To: "Jean-Pierre Pelletier" <[EMAIL PROTECTED]>
Cc:
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 [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
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
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 =:-> signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
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" <[EMAIL PROTECTED]>
To: "Jean-Pierre Pelletier" <[EMAIL PROTECTED]>
Cc:
Sent: Thursday, September 22, 2005 5:48 PM
Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
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 > signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
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. Roy To: Jean-Pierre Pelletier Cc: pgsql-performance@postgresql.org Sent: Thursday, September 22, 2005 5:32 PM Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0 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? Gavin M. Roy 800 Pound Gorilla [EMAIL PROTECTED]
Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
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? Gavin M. Roy800 Pound Gorilla[EMAIL PROTECTED]
[PERFORM] 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'),
ItemIdINTEGER NOT NULL,
SupplierIdINTEGER NOT NULL,
LocationIdSMALLINT NULL,
FromDate DATE NOT NULL DEFAULT CURRENT_DATE,
UnitValue DECIMAL NOT NULL,
InsertedByPersonIdINTEGER 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,
ApprovalDateDATENULL
);
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 select0 fromContent C LEFT OUTER JOIN Supplier SON
C.SupplierId = S.SupplierId LEFT OUTER JOIN Price PON 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 select0 fromContent C LEFT OUTER JOIN Supplier SON
C.SupplierId = S.SupplierId LEFT OUTER JOIN Price PON 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)
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
