<[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.72
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/i
<[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 Pell
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 Sol
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 =
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 committ
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)--
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
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 sl
ED]>; "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..1131
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*
- 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
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
; 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:
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 unde
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 schem
ot;.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
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
>
> le
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
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..
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 i
21 matches
Mail list logo
