Lists: | pgsql-performance |
---|
From: | Kevin Traster <ktraster(at)freshgrillfoods(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | index scan forward vs backward = speed difference of 357X slower! |
Date: | 2012-02-07 16:49:05 |
Message-ID: | CAC7CH7GB2Yi1VqQ9hy8Yex0avGvgLfvQkHabFGfmZa1WRU3X+A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-51), 64-bit
Dedicated DB server
4GB ram
Shared_Buffers = 1 GB
Effective_cache_size = 3GB
Work_mem = 32GB
Analyze done
Queries ran multiple times, same differences/results
Default Statistics = 1000
Query (5366ms) :
explain analyze select initcap (fullname), initcap(issuer),upper(rsymbol),
initcap(industry),
activity,to_char(shareschange,'FM9,999,999,999,999,999'),sharespchange ||+
E'\%' from changes where activity in (4,5) and mfiled >= (select
max(mfiled) from changes) order by shareschange asc limit 15
Slow Ascending explain Analyze:
http://explain.depesz.com/s/zFz
Query (15ms) :
explain analyze select initcap (fullname), initcap(issuer),upper(rsymbol),
initcap(industry),
activity,to_char(shareschange,'FM9,999,999,999,999,999'),sharespchange ||+
E'\%' from changes where activity in (4,5) and mfiled >= (select
max(mfiled) from changes) order by shareschange desc limit 15
Fast descending explain analyze:
http://explain.depesz.com/s/OP7
The index: changes_shareschange is a btree index created with default
ascending order
The query plan and estimates are exactly the same, except desc has index
scan backwards instead of index scan for changes_shareschange.
Yet, actual runtime performance is different by 357x slower for the
ascending version instead of descending.
Why and how do I fix it?
From: | Rural Hunter <ruralhunter(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: index scan forward vs backward = speed difference of 357X slower! |
Date: | 2012-02-08 08:01:44 |
Message-ID: | 4F322BE8.6070701@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
what's the size of the index? is it too big to fit in
shared_buffers? maybe the firt 15 rows by asc order are in buffer
but the ones of desc order are not, while your disk IO is very slow?<br>
btw, your mem configuration of work_men is very strange. <br>
<br>
于 2012/2/8 0:49, Kevin Traster 写道:
<blockquote
cite="mid:CAC7CH7GB2Yi1VqQ9hy8Yex0avGvgLfvQkHabFGfmZa1WRU3X+A(at)mail(dot)gmail(dot)com"
type="cite"><span style="border-collapse:collapse">
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif">PostgreSQL 9.1.2 on
x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-51), 64-bit</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif">Dedicated DB server</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif">4GB ram</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif">Shared_Buffers = 1 GB</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif">Effective_cache_size = 3GB</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif">Work_mem = 32GB</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif">Analyze done</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif">Queries ran multiple times, same
differences/results</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif">Default Statistics = 1000</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif"><br>
</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif">Query (5366ms) :</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><span
style="color:rgb(11,55,77);font-family:inherit;background-color:rgb(255,255,255);font-size:12px;text-align:left">explain
analyze select initcap (fullname),
initcap(issuer),upper(rsymbol), initcap(industry),
activity,to_char(shareschange,'FM9,999,999,999,999,999'),sharespchange
||+ E'\%' from changes where activity in (4,5) and mfiled
>= (select max(mfiled) from changes) order by
shareschange asc limit 15 </span></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><span
style="color:rgb(11,55,77);font-family:inherit;background-color:rgb(255,255,255);font-size:12px;text-align:left"><br>
</span></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px">Slow
Ascending explain Analyze:</p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><a
moz-do-not-send="true"
href="http://explain.depesz.com/s/zFz">http://explain.depesz.com/s/zFz</a></p>
<br class="Apple-interchange-newline">
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><span
style="color:rgb(11,55,77);font-family:inherit;background-color:rgb(255,255,255);font-size:12px;text-align:left"><br>
</span></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif">Query (15ms) :</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><span
style="color:rgb(11,55,77);font-family:inherit;background-color:rgb(255,255,255);font-size:12px;text-align:left">explain
analyze select initcap (fullname),
initcap(issuer),upper(rsymbol), initcap(industry),
activity,to_char(shareschange,'FM9,999,999,999,999,999'),sharespchange
||+ E'\%' from changes where activity in (4,5) and mfiled
>= (select max(mfiled) from changes) order by
shareschange desc limit 15 </span></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><br>
</p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif">Fast descending explain analyze:</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><a
moz-do-not-send="true"
href="http://explain.depesz.com/s/OP7">http://explain.depesz.com/s/OP7</a></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><br>
</p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><span
style="font-family:arial,sans-serif;font-size:13.2px"><br>
</span></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><span
style="font-family:arial,sans-serif;font-size:13.2px">The
index: </span><span
style="font-family:arial,sans-serif;font-size:13px">changes_shareschange
</span><span
style="background-color:rgb(255,255,255);color:rgb(11,55,77);font-family:inherit;font-size:12px;text-align:left">is
a </span><span
style="font-family:arial,sans-serif;font-size:13.2px">btree
index created with default ascending order</span></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><span
style="font-family:arial,sans-serif;font-size:13.2px"><br>
</span></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><span
style="font-family:arial,sans-serif;font-size:13.2px">The
query plan and estimates are exactly the same, except desc
has index scan backwards instead of index scan for
changes_shareschange.</span></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><span
style="font-family:arial,sans-serif;font-size:13.2px"><br>
</span></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif">Yet, actual runtime performance is
different by 357x slower for the ascending version instead
of descending.</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif"><br>
</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif">Why and how do I fix it?</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif"><br>
</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif"><br>
</font></p>
<p
style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font
face="arial, sans-serif"><br>
</font></p>
</span>
</blockquote>
<br>
</body>
</html>
Attachment | Content-Type | Size |
---|---|---|
unknown_filename | text/html | 7.8 KB |
From: | Rural Hunter <ruralhunter(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: index scan forward vs backward = speed difference of 357X slower! |
Date: | 2012-02-08 14:36:30 |
Message-ID: | 4F32886E.1030907@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
what's the size of the index? is it too big to fit in shared_buffers?
maybe the firt 15 rows by asc order are in buffer but the ones of desc
order are not, while your disk IO is very slow?
btw, your mem configuration of work_men is very strange.
于 2012/2/8 0:49, Kevin Traster 写道:
>
> PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
> 4.1.2 20080704 (Red Hat 4.1.2-51), 64-bit
>
> Dedicated DB server
>
> 4GB ram
>
> Shared_Buffers = 1 GB
>
> Effective_cache_size = 3GB
>
> Work_mem = 32GB
>
> Analyze done
>
> Queries ran multiple times, same differences/results
>
> Default Statistics = 1000
>
>
> Query (5366ms) :
>
> explain analyze select initcap (fullname),
> initcap(issuer),upper(rsymbol), initcap(industry),
> activity,to_char(shareschange,'FM9,999,999,999,999,999'),sharespchange
> ||+ E'\%' from changes where activity in (4,5) and mfiled >= (select
> max(mfiled) from changes) order by shareschange asc limit 15
>
>
> Slow Ascending explain Analyze:
>
> http://explain.depesz.com/s/zFz
>
>
>
> Query (15ms) :
>
> explain analyze select initcap (fullname),
> initcap(issuer),upper(rsymbol), initcap(industry),
> activity,to_char(shareschange,'FM9,999,999,999,999,999'),sharespchange
> ||+ E'\%' from changes where activity in (4,5) and mfiled >= (select
> max(mfiled) from changes) order by shareschange desc limit 15
>
>
> Fast descending explain analyze:
>
> http://explain.depesz.com/s/OP7
>
>
>
> The index: changes_shareschange is a btree index created with default
> ascending order
>
>
> The query plan and estimates are exactly the same, except desc has
> index scan backwards instead of index scan for changes_shareschange.
>
>
> Yet, actual runtime performance is different by 357x slower for the
> ascending version instead of descending.
>
>
> Why and how do I fix it?
>
>
>
>
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Kevin Traster <ktraster(at)freshgrillfoods(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: index scan forward vs backward = speed difference of 357X slower! |
Date: | 2012-02-08 19:31:08 |
Message-ID: | 29203.1328729468@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
Kevin Traster <ktraster(at)freshgrillfoods(dot)com> writes:
> The query plan and estimates are exactly the same, except desc has index
> scan backwards instead of index scan for changes_shareschange.
> Yet, actual runtime performance is different by 357x slower for the
> ascending version instead of descending.
Apparently, there are some rows passing the filter condition that are
close to the end of the index, but none that are close to the start.
So it takes a lot longer to find the first 15 matches in one case than
the other. You haven't shown us the index definition, but I gather from
the fact that the scan condition is just a Filter (not an Index Cond)
that the index itself doesn't offer any clue as to whether a given row
meets those conditions. So this plan is going to be doing a lot of
random-access heap probes until it finds a match.
> Why and how do I fix it?
Probably, you need an index better suited to the query condition.
If you have one and the problem is that the planner's not choosing it,
then this is going to take more information to resolve.
regards, tom lane
From: | Kevin Traster <ktraster(at)freshgrillfoods(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: index scan forward vs backward = speed difference of 357X slower! |
Date: | 2012-02-08 19:58:57 |
Message-ID: | CAC7CH7FDr+LibQuJYchhbOWJM_U2vez8NC5SazoX+rNGrL8Whw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
Typo: Work_mem = 32 MB
The definition for both column and index:
shareschange | numeric |
"changes_shareschange" btree (shareschange)
Index created using: CREATE INDEX changes_shareschange ON changes(shareschange);
The entire table is created nightly (and analyzed afterwords), and
used only for reporting - there no updates/deletes, so there shouldn't
be any dead rows in the table.
Likewise, there is no nulls in the column.
Please elaborate on:
>You haven't shown us the index definition, but I gather from
> the fact that the scan condition is just a Filter (not an Index Cond)
> that the index itself doesn't offer any clue as to whether a given row
> meets those conditions
Are you saying it is the retrieval of the physically random located 15
rows to meet the ascending condition that causes the 5 sec difference?
The table is not-clustered, so it is "random" for descending also.
The condition is shareschange ascending, I have an index for that
condition and the planner is using it.
What else can I look at?
On Wed, Feb 8, 2012 at 11:31 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Kevin Traster <ktraster(at)freshgrillfoods(dot)com> writes:
>> The query plan and estimates are exactly the same, except desc has index
>> scan backwards instead of index scan for changes_shareschange.
>> Yet, actual runtime performance is different by 357x slower for the
>> ascending version instead of descending.
>
> Apparently, there are some rows passing the filter condition that are
> close to the end of the index, but none that are close to the start.
> So it takes a lot longer to find the first 15 matches in one case than
> the other. You haven't shown us the index definition, but I gather from
> the fact that the scan condition is just a Filter (not an Index Cond)
> that the index itself doesn't offer any clue as to whether a given row
> meets those conditions. So this plan is going to be doing a lot of
> random-access heap probes until it finds a match.
>
>> Why and how do I fix it?
>
> Probably, you need an index better suited to the query condition.
> If you have one and the problem is that the planner's not choosing it,
> then this is going to take more information to resolve.
>
> regards, tom lane
From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Kevin Traster <ktraster(at)freshgrillfoods(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: index scan forward vs backward = speed difference of 357X slower! |
Date: | 2012-02-08 23:27:00 |
Message-ID: | CAHyXU0z6BQ+2bOuV+0wxayawdo-+FVvSQE3AnbJH+D_+dyOB8w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
On Wed, Feb 8, 2012 at 1:58 PM, Kevin Traster
<ktraster(at)freshgrillfoods(dot)com> wrote:
> Typo: Work_mem = 32 MB
>
> The definition for both column and index:
> shareschange | numeric |
> "changes_shareschange" btree (shareschange)
>
> Index created using: CREATE INDEX changes_shareschange ON changes(shareschange);
>
> The entire table is created nightly (and analyzed afterwords), and
> used only for reporting - there no updates/deletes, so there shouldn't
> be any dead rows in the table.
> Likewise, there is no nulls in the column.
>
> Please elaborate on:
>
>>You haven't shown us the index definition, but I gather from
>> the fact that the scan condition is just a Filter (not an Index Cond)
>> that the index itself doesn't offer any clue as to whether a given row
>> meets those conditions
>
> Are you saying it is the retrieval of the physically random located 15
> rows to meet the ascending condition that causes the 5 sec difference?
> The table is not-clustered, so it is "random" for descending also.
>
> The condition is shareschange ascending, I have an index for that
> condition and the planner is using it.
This is not a problem with dead rows, but the index is not really
satisfying your query and the database has to look through an
indeterminate amount of rows until the 'limit 15' is satisfied. Yeah,
backwards scans are slower, especially for disk bound scans but you
also have to consider how many filter misses your have. The smoking
gun is here:
"Index Scan Backward using changes_shareschange on changes
(cost=0.00..925150.26 rows=181997 width=98) (actual time=3.161..15.843
rows=15 loops=1)
Filter: ((activity = ANY ('{4,5}'::integer[])) AND (mfiled >= $1))"
When you see Filter: xyz, xyz is what each record has to be compared
against after the index pointed you to an area(s) in the heap. It's
pure luck going forwards or backwards that determines how many records
you have to look through to get 15 good ones as defined by satisfying
the filter. To prove that one way or the other you can convert your
where to a boolean returning (and bump the limit appropriately)
expression to see how many records get filtered out.
merlin
From: | Kevin Traster <ktraster(at)freshgrillfoods(dot)com> |
---|---|
To: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: index scan forward vs backward = speed difference of 357X slower! |
Date: | 2012-02-08 23:31:50 |
Message-ID: | CAC7CH7HWdx1Fkcu04cQJH7_Dgon-+D73=tYDpA4zPoUaJ5fEww@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
> This is not a problem with dead rows, but the index is not really
> satisfying your query and the database has to look through an
> indeterminate amount of rows until the 'limit 15' is satisfied. Yeah,
> backwards scans are slower, especially for disk bound scans but you
> also have to consider how many filter misses your have. The smoking
> gun is here:
>
> "Index Scan Backward using changes_shareschange on changes
> (cost=0.00..925150.26 rows=181997 width=98) (actual time=3.161..15.843
> rows=15 loops=1)
> Filter: ((activity = ANY ('{4,5}'::integer[])) AND (mfiled >= $1))"
>
> When you see Filter: xyz, xyz is what each record has to be compared
> against after the index pointed you to an area(s) in the heap. It's
> pure luck going forwards or backwards that determines how many records
> you have to look through to get 15 good ones as defined by satisfying
> the filter. To prove that one way or the other you can convert your
> where to a boolean returning (and bump the limit appropriately)
> expression to see how many records get filtered out.
>
> merlin
I have indexes also on activity and mfiled (both btree) - wouldn't the
database use them? - Kevin
From: | "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> |
---|---|
To: | "Kevin Traster" <ktraster(at)freshgrillfoods(dot)com>, "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
Cc: | <pgsql-performance(at)postgresql(dot)org>,"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Re: index scan forward vs backward = speed difference of 357X slower! |
Date: | 2012-02-08 23:41:06 |
Message-ID: | 4F32B3B20200002500045085@gw.wicourts.gov |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
Kevin Traster <ktraster(at)freshgrillfoods(dot)com> wrote:
> I have indexes also on activity and mfiled (both btree) - wouldn't
> the database use them? - Kevin
It will use them if they are part of the plan which had the lowest
cost when it compared the costs of all possible plans.
You haven't really shown us the schema, so there's more guesswork
involved in trying to help you than there could be. This page might
be worth reviewing:
http://wiki.postgresql.org/wiki/SlowQueryQuestions
In particular, if there are indexes that aren't being used which you
think should be, there is a good chance that either there is a type
mismatch or your costing factors may need adjustment.
-Kevin