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
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
: 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
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
-
From: John Arbash Meinel [EMAIL PROTECTED]
To: Jean-Pierre Pelletier [EMAIL PROTECTED]
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
---(end of broadcast
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.
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
PROTECTED]
To: Jean-Pierre Pelletier [EMAIL PROTECTED]
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
: 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
- Original Message -
From: Jean-Pierre Pelletier [EMAIL PROTECTED]
To: josh@agliodbs.com
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
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
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
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
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)---
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
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
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
; pgsql-performance@postgresql.org;
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
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
***
[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
20 matches
Mail list logo
