Re: [PERFORM] Tuning/performance issue....
David Griffiths wrote: And finally, Here's the contents of the postgresql.conf file (I've been playing with these setting the last couple of days, and using the guide @ http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html to make sure I didn't have it mis-tuned): tcpip_socket = true max_connections = 500# We will need quite a few connections; currently only one connection to database, however port = 5432 shared_buffers = 5000 # I've tried 5000 to 80,000 with no apparent difference wal_buffers = 16 sort_mem = 256# decreased this due to the large # of connectiosn effective_cache_size = 5 # read that this can improve performance; hasn't done anything. Reading this whole thread, I think most of the improvement you would get would be from rethinking your schema from PG point of view and examine each query. After you changed your last query as Tom suggested for explicit join, how much improvement did it make? I noticed that you put 'commercial_entity.commercial_entity_id=225528' as a second codition. Does it make any difference to put it ahead in where clause list? HTH Shridhar ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] inferior SCSI performance
On Wed, Sep 17, 2003 at 04:46:00PM -0400, Michael Adler wrote: > So the quesiton is whether it is ever sensible to use write-caching and > expect comparable persistence. Yes. If and only if you have a battery-backed cache. I know of no IDE drives that have that, but there's nothing about the spec which makes it impossible. A -- Andrew Sullivan 204-4141 Yonge Street Afilias CanadaToronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 3: 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
[PERFORM] What is the fastest null WHERE
Hello, While writing web application I found that it would be very nice for me to have "null" WHERE clause. Like WHERE 1=1. Then it is easy to concat additional conditions just using $query . " AND col=false" syntax. But which of the possible "null" clauses is the fastest one? Thanks, Mindaugas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Tuning/performance issue...
On Tue, 30 Sep 2003, David Griffiths wrote: > > This is all part of a "migrate away from Oracle" project. We are looking at > 3 databases - > MySQL (InnoDB), Postgres and Matisse (object oriented). We have alot of > queries like this > or worse, and I'm worried that many of them would need to be re-written. The > developers > know SQL, but nothing about tuning, etc. > There's a movement at my company to ditch several commercial db's in favor of a free one. I'm currently the big pg fan around here and I've actually written a rather lengthy presentation about pg features, why, tuning, etc. but another part was some comparisons to other db's.. I decided so I wouldn't be blinding flaming mysql to give it a whirl and loaded it up with the same dataset as pg. First thing I hit was lack of stored procedures. But I decided to code around that, giving mysql the benefit of the doubt. What I found was interesting. For 1-2 concurrent 'beaters' it screamed. ultra-fast. But.. If you increase the concurrent beaters up to say, 20 Mysql comes to a grinding halt.. Mysql and the machine itself become fairly unresponsive. And if you do cache unfriendly queries it becomes even worse. On PG - no problems at all. Scaled fine and dandy up. And with 40 concurrent beaters the machine was still responsive. (The numbers for 20 client was 220 seconds (pg) and 650 seconds (mysql)) So that is another test to try out - Given your configuration I expect you have lots of concurrent activity. -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] What is the fastest null WHERE
Mindaugas Riauba wrote: Hello, While writing web application I found that it would be very nice for me to have "null" WHERE clause. Like WHERE 1=1. Then it is easy to concat additional conditions just using $query . " AND col=false" syntax. But which of the possible "null" clauses is the fastest one? Rather than this approach, keep a flag which tells you whether or not it is first where condition. If it is not first where condition, add a 'and'. That would be simple, isn't it? Shridhar ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] What is the fastest null WHERE
On Wednesday 01 October 2003 13:11, Mindaugas Riauba wrote:
> Hello,
>
> While writing web application I found that it would
> be very nice for me to have "null" WHERE clause. Like
> WHERE 1=1. Then it is easy to concat additional
> conditions just using $query . " AND col=false" syntax.
>
> But which of the possible "null" clauses is the fastest
> one?
I suspect WHERE true, but is it really necessary.
Most languages will have a join() operator that lets you do something like:
$where_cond = join(' AND ', @list_of_tests)
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] What is the fastest null WHERE
On Wed, 2003-10-01 at 08:11, Mindaugas Riauba wrote: > While writing web application I found that it would > be very nice for me to have "null" WHERE clause. Like > WHERE 1=1. Then it is easy to concat additional > conditions just using $query . " AND col=false" syntax. > > But which of the possible "null" clauses is the fastest > one? WHERE true AND signature.asc Description: This is a digitally signed message part
Re: [PERFORM] What is the fastest null WHERE
> > While writing web application I found that it would
> > be very nice for me to have "null" WHERE clause. Like
> > WHERE 1=1. Then it is easy to concat additional
> > conditions just using $query . " AND col=false" syntax.
> >
> > But which of the possible "null" clauses is the fastest
> > one?
>
> I suspect WHERE true, but is it really necessary.
Thanks. I'll use "WHERE true" for now. And of course it is
not necessary it just simplifies code a bit.
> Most languages will have a join() operator that lets you do something
like:
>
> $where_cond = join(' AND ', @list_of_tests)
That's not the case. Test may or may not be performed based on
web form values.
Mindaugas
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] inferior SCSI performance
On Wed, 1 Oct 2003, Andrew Sullivan wrote: > On Wed, Sep 17, 2003 at 04:46:00PM -0400, Michael Adler wrote: > > So the quesiton is whether it is ever sensible to use write-caching and > > expect comparable persistence. > > Yes. If and only if you have a battery-backed cache. I know of no > IDE drives that have that, but there's nothing about the spec which > makes it impossible. FYI, on a Dual PIV2800 with 2 gig ram and a single UDMA 80 gig hard drive, I from 420 tps to 22 tps when I disable write caching. WOW. A factor of about 20 times slower. (pgbench -c 4 -t 100) ---(end of broadcast)--- TIP 3: 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] inferior SCSI performance
On Wed, Oct 01, 2003 at 07:14:32AM -0600, scott.marlowe wrote: > FYI, on a Dual PIV2800 with 2 gig ram and a single UDMA 80 gig hard drive, > I from 420 tps to 22 tps when I disable write caching. WOW. A factor of > about 20 times slower. (pgbench -c 4 -t 100) That's completely consistent with tests Chris Browne has done here on cache-enabled and cache-disabled boxes that we have. It's a _really_ big difference. The combination of battery-backed write cache on your controller plus a real good UPS is quite possibly the number one thing you can do to improve performance. For what it's worth, I can't see how this is something special about Postgres: even raw-filesystem type systems have to make sure the disk actually has the data, and a write cache is bound to be a big help for that. A -- Andrew Sullivan 204-4141 Yonge Street Afilias CanadaToronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] TPC-R benchmarks
On Tue, 30 Sep 2003, Oleg Lebedev wrote: > I continue struggling with the TPC-R benchmarks and wonder if anyone > could help me optimize the query below. ANALYZE statistics indicate that > the query should run relatively fast, but it takes hours to complete. I > attached the query plan to this posting. > Thanks. What are the differences between estimated and real rows and such of an explain analyze on that query? Are there any estimates that are just way off? ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Tuning/performance issue...
David Griffiths <[EMAIL PROTECTED]> writes: >> The reason PG's planner doesn't discover this join order for itself >> is that it's written to not attempt to re-order outer joins from the >> syntactically defined ordering. In general, such reordering would >> change the results. It is possible to analyze the query and prove that >> certain reorderings are valid (don't change the results), but we don't >> currently have code to do that. > Not sure I follow. Are you saying that, depending on when the outer-join is > applied to the rows found at the time, you may end up with a different set > of rows? Here's an example showing that it's not always safe to rearrange join order in the presence of outer joins: jtest=# create table a (f1 int); CREATE TABLE jtest=# create table b (f1 int, f2 int); CREATE TABLE jtest=# create table c(f1 int, f2 int); CREATE TABLE jtest=# insert into a values (1); INSERT 431307 1 jtest=# insert into b values (10,10); INSERT 431308 1 jtest=# insert into b values (11,11); INSERT 431309 1 jtest=# insert into c values (1,10); INSERT 431310 1 jtest=# insert into c values (2,11); INSERT 431311 1 jtest=# SELECT * FROM a, b LEFT JOIN c ON b.f2 = c.f2 WHERE a.f1 = c.f1; f1 | f1 | f2 | f1 | f2 ++++ 1 | 10 | 10 | 1 | 10 (1 row) Per spec the JOIN operator binds more tightly than comma, so this is equivalent to: jtest=# SELECT * FROM a JOIN (b LEFT JOIN c ON b.f2 = c.f2) ON a.f1 = c.f1; f1 | f1 | f2 | f1 | f2 ++++ 1 | 10 | 10 | 1 | 10 (1 row) Now suppose we try to join A and C before joining to B: jtest=# SELECT * FROM b LEFT JOIN (a join c ON a.f1 = c.f1) ON b.f2 = c.f2; f1 | f2 | f1 | f1 | f2 ++++ 10 | 10 | 1 | 1 | 10 11 | 11 ||| (2 rows) We get a different answer, because some C rows are eliminated before reaching the left join, causing null-extended B rows to be added. (I don't have a MySQL installation here to try, but if they still work the way they used to, they get the wrong answer on the first query.) The point of this example is just that there are cases where it'd be incorrect for the planner to change the ordering of joins from what is implied by the query syntax. It is always safe to change the join order when only inner joins are involved. There are cases where outer join order is safe to change too, but you need analysis code that checks the query conditions to prove that a particular rearrangement is safe. Right now, we don't have such code, and so we just follow the simple rule "never rearrange any outer joins". > I would have expected the optimizer to do the outer-joins last, as the > extra data received by the outer-joins is not mandatory, and won't > affect the rows that were retreived by joining user_account, > address_list, and commercial_entity. I think your example falls into the category of provably-safe rearrangements ... but as I said, the planner doesn't know that. > An outer join would *never* be the most restrictive > join in a query. Sure it can, if the restriction conditions are mainly on the outer join's tables. But that's not really the issue here. As best I can tell without seeing your data statistics, the most restrictive conditions in your query are the ones on commercial_entity.commercial_entity_id and user_account.user_role_id. The trick is to apply those before joining any other tables. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Joins on inherited tables
Hi,
In some situations, it looks like the optimizer does not chose
efficient paths for joining inherited tables. For I created a
rather trivial formulation to serve as an example. I created the table
'numbers' comprising of the columns id (int) and value (text). I also
created the table 'evens' and 'odds' that inherit numbers, with no
additional columns. Into 'evens' I placed 5 entries, each one with
an even (unique) id and random 'value'. Likewise, for 'odds', I created
5 odd (and unique) id fields id fields and random 'value', and
created index on all ID fields of every table that has any rows (and
analyzed).
so.. my tables look like this:
Table "public.numbers"
Column | Type | Modifiers
+-+---
id | integer |
value | text|
Table "public.evens"
Column | Type | Modifiers
+-+---
id | integer |
value | text|
Indexes:
"ei" btree (id)
Inherits: numbers
Table "public.odds"
Column | Type | Modifiers
+-+---
id | integer |
value | text|
Indexes:
"oi" btree (id)
Inherits: numbers
As per the above construction, 'evens' and 'odds' both have 5
rows. 'numbers' contains none.
Now, I created a trivial query that would use 'numbers' as an inheritor
table in a join (a very stupid one, but a join nevertheless) as
follows, which produces a terrible, but correct, plan:
select value from (SELECT 1::integer as id) as ids JOIN numbers on
(numbers.id = ids.id);
QUERY PLAN
---
Hash Join (cost=0.02..2195.79 rows=501 width=19)
Hash Cond: ("outer".id = "inner".id)
-> Append (cost=0.00..1690.50 rows=100051 width=23)
-> Seq Scan on numbers (cost=0.00..0.00 rows=1 width=23)
-> Seq Scan on evens numbers (cost=0.00..845.25 rows=50025 width=23)
-> Seq Scan on odds numbers (cost=0.00..845.25 rows=50025 width=23)
-> Hash (cost=0.02..0.02 rows=1 width=4)
-> Subquery Scan ids (cost=0.00..0.02 rows=1 width=4)
-> Result (cost=0.00..0.01 rows=1 width=0)
Now, I subsitute 'evens' for 'numbers', so I am now joining with a normal,
non-inherited table. The plan is much better:
select value from (SELECT 1::integer as id) as ids JOIN evens on
(evens.id = ids.id);
QUERY PLAN
---
Nested Loop (cost=0.00..3.05 rows=2 width=19)
-> Subquery Scan ids (cost=0.00..0.02 rows=1 width=4)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Index Scan using ei on evens (cost=0.00..3.01 rows=1 width=23)
Index Cond: (evens.id = "outer".id)
I would think that the ideal plan for the first query should be a nested
loop like the second that considers indexes where possible, and it would
look as follows:
HYPOTHETICAL PLAN
Nested Loop
-> Subquery Scan ids
-> Result
-> Append
-> Seq Scan on numbers
-> Index Scan using ei on evens
Index Cond: (evens.id = "outer.id")
-> Index Scan using oi on odds
Index Cond: (odds.id = "outer.id")
So.. Why wouldn't postgres use such a plan? I could think of three
reasons:
- The planner wasn't considering this plan due to some
fault of its own
- That plan makes no sense and would not be able to be run in the
executor, and therefore it would be wasteful to consider it.
- It truly is more expensive than a hash join
I've pretty much ruled out the third and I suspect the second is also
untrue (though have not proven that to myself), leaving the first. If it
is indeed the second, that the plan makes no sense, someone please let me
know!
OK, so I took a look into the optimizer and over time got a better
understanding of what's going on, though I still don't understand it
completely. Here's my theory as to what's happening:
For this query, most of the path consideration takes place in
match_unsorted_outer() in path/joinpath.c. For the 'good' query against
the non-inherited 'evens' table, the good plan is generated in the line:
bestinnerjoin = best_inner_indexscan(...);
Since an inherited table doesn't have one single index over all its
inherited tables, this call produces a null bestinnerjoin.
Later on in match_unsorted_inner(), various access paths are considered
for a nested loop. One is bestinnerjoin (when it exists), and that is
how the 'good' query gets its nested loop with an index scan.
Other paths considered for inclusion in the nested loop are
'inner_cheapest_total' and 'inner_cheapest_startup'; These plans,
presumably, contain
Re: [PERFORM] Tuning/performance issue...
Jeff, I would really appreciate if you could send me that lengthy presentation that you've written on pg/other dbs comparison. Thanks. Oleg -Original Message- From: Jeff [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 6:23 AM To: David Griffiths Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] Tuning/performance issue... Importance: Low On Tue, 30 Sep 2003, David Griffiths wrote: > > This is all part of a "migrate away from Oracle" project. We are > looking at 3 databases - MySQL (InnoDB), Postgres and Matisse (object > oriented). We have alot of queries like this > or worse, and I'm worried that many of them would need to be re-written. The > developers > know SQL, but nothing about tuning, etc. > There's a movement at my company to ditch several commercial db's in favor of a free one. I'm currently the big pg fan around here and I've actually written a rather lengthy presentation about pg features, why, tuning, etc. but another part was some comparisons to other db's.. I decided so I wouldn't be blinding flaming mysql to give it a whirl and loaded it up with the same dataset as pg. First thing I hit was lack of stored procedures. But I decided to code around that, giving mysql the benefit of the doubt. What I found was interesting. For 1-2 concurrent 'beaters' it screamed. ultra-fast. But.. If you increase the concurrent beaters up to say, 20 Mysql comes to a grinding halt.. Mysql and the machine itself become fairly unresponsive. And if you do cache unfriendly queries it becomes even worse. On PG - no problems at all. Scaled fine and dandy up. And with 40 concurrent beaters the machine was still responsive. (The numbers for 20 client was 220 seconds (pg) and 650 seconds (mysql)) So that is another test to try out - Given your configuration I expect you have lots of concurrent activity. -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match * This e-mail may contain privileged or confidential material intended for the named recipient only. If you are not the named recipient, delete this message and all attachments. Unauthorized reviewing, copying, printing, disclosing, or otherwise using information in this e-mail is prohibited. We reserve the right to monitor e-mail sent through our network. * ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] TPC-R benchmarks
Tom Lane wrote: > When benchmarking with data sets considerably larger than available > buffer cache, I rather doubt that small random_page_cost would be a > good idea. Still, you might as well experiment to see. >From experience, I know the difference in response time can be huge when postgres >incorrectly chooses a sequential scan over an index scan. In practice, do people experience as great a difference when postgres incorrectly chooses an index scan over a sequential scan? My intuition is that the speed difference is a lot less for incorrectly choosing an index scan. If this is the case, it would be safer to chose a small value for random_page_cost. George Essig ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] TPC-R benchmarks
Oleg, > I continue struggling with the TPC-R benchmarks and wonder if anyone > could help me optimize the query below. ANALYZE statistics indicate that > the query should run relatively fast, but it takes hours to complete. I > attached the query plan to this posting. Even though it takes hours to complete, I think we need you to run EXPLAIN ANALYZE instead of just EXPLAIN. Without the real-time statistics, we simply can't see what's slowing the query down. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Joins on inherited tables
[EMAIL PROTECTED] writes:
> So.. does anybody have any advice?
Look at set_inherited_rel_pathlist() in allpaths.c --- it forms the best
plan for fully scanning the inheritance-tree table. Currently that's
the *only* plan considered, and it does not make any use of join
clauses. It's possible that something could be done with providing a
similar routine for best inner indexscans taken across the whole
inheritance tree. You'd have to figure out how to locate the applicable
joinclauses though. I think they'd be attached to the inheritance-tree
parent relation and not to the individual inheritance tree member
relations. Also, if you are wondering why best_inner_indexscan() is so
tense about caching its results, that's because it gets called *a lot*
in large join problems. If you don't achieve a similar level of
efficiency then you'll be seeing some nasty performance problems in
larger queries.
I think you'd find there is executor work to do as well; I'm not sure
how the outer-relation values would get propagated down into the
indexscans when there's an Append node between. Maybe the existing code
would Just Work, but I bet not.
Not sure if this will help you, but:
Once upon a time the planner did the APPEND for an inheritance tree at
the top of the plan not the bottom. (It still does when the tree is the
target of an update/delete query.) In 7.0 for example I get a plan like
this:
create table pt (f1 int primary key);
create table ct1 (f2 int) inherits (pt);
create table ct2 (f2 int) inherits (pt);
create index ct1i on ct1(f1);
create table bar(f1 int);
explain select * from pt*, bar where pt.f1 = bar.f1;
NOTICE: QUERY PLAN:
Append (cost=69.83..474.33 rows=3 width=8)
-> Merge Join (cost=69.83..154.83 rows=1 width=8)
-> Index Scan using pt_pkey on pt (cost=0.00..60.00 rows=1000 width=4)
-> Sort (cost=69.83..69.83 rows=1000 width=4)
-> Seq Scan on bar (cost=0.00..20.00 rows=1000 width=4)
-> Merge Join (cost=69.83..154.83 rows=1 width=8)
-> Index Scan using ct1i on ct1 pt (cost=0.00..60.00 rows=1000 width=4)
-> Sort (cost=69.83..69.83 rows=1000 width=4)
-> Seq Scan on bar (cost=0.00..20.00 rows=1000 width=4)
-> Merge Join (cost=139.66..164.66 rows=1 width=8)
-> Sort (cost=69.83..69.83 rows=1000 width=4)
-> Seq Scan on bar (cost=0.00..20.00 rows=1000 width=4)
-> Sort (cost=69.83..69.83 rows=1000 width=4)
-> Seq Scan on ct2 pt (cost=0.00..20.00 rows=1000 width=4)
whereas the same test in CVS tip produces
QUERY PLAN
Merge Join (cost=303.09..353.09 rows=3000 width=8)
Merge Cond: ("outer".f1 = "inner".f1)
-> Sort (cost=69.83..72.33 rows=1000 width=4)
Sort Key: bar.f1
-> Seq Scan on bar (cost=0.00..20.00 rows=1000 width=4)
-> Sort (cost=233.26..240.76 rows=3000 width=4)
Sort Key: public.pt.f1
-> Append (cost=0.00..60.00 rows=3000 width=4)
-> Seq Scan on pt (cost=0.00..20.00 rows=1000 width=4)
-> Seq Scan on ct1 pt (cost=0.00..20.00 rows=1000 width=4)
-> Seq Scan on ct2 pt (cost=0.00..20.00 rows=1000 width=4)
The fact that 7.0 could actually adapt to different index sets for
different child tables was kinda cool, but the append-at-the-top
strategy failed completely for outer joins, so we had to abandon it.
In practice I think the generated plan was usually worse anyway (note
that bar gets scanned three times in 7.0's plan), but for the specific
case where the inheritance tree is on the inside of a nestloop that
could be indexed, the new approach is not as good. If you can come up
with a fix that doesn't break things in other respects, it'd be great.
[digs in CVS logs] The patch that altered the APPEND-at-the-top
behavior was this one:
2000-11-11 19:36 tgl
* src/: backend/commands/command.c, backend/commands/copy.c,
backend/commands/explain.c, backend/executor/execMain.c,
backend/executor/execQual.c, backend/executor/execTuples.c,
backend/executor/execUtils.c, backend/executor/functions.c,
backend/executor/nodeAppend.c, backend/executor/nodeSeqscan.c,
backend/nodes/copyfuncs.c, backend/nodes/equalfuncs.c,
backend/nodes/outfuncs.c, backend/nodes/readfuncs.c,
backend/optimizer/path/allpaths.c,
backend/optimizer/path/pathkeys.c,
backend/optimizer/plan/createplan.c,
backend/optimizer/plan/planmain.c,
backend/optimizer/plan/planner.c,
backend/optimizer/prep/prepunion.c,
backend/optimizer/util/pathnode.c,
backend/optimizer/util/relnode.c, backend/parser/parse_clause.c,
backend/tcop/pquery.c, include/catalog/catversion.h,
include/executor/executor.h, include/executor/tuptable.h,
Re: [PERFORM] inferior SCSI performance
[EMAIL PROTECTED] (Andrew Sullivan) writes:
> On Wed, Oct 01, 2003 at 07:14:32AM -0600, scott.marlowe wrote:
>> FYI, on a Dual PIV2800 with 2 gig ram and a single UDMA 80 gig hard drive,
>> I from 420 tps to 22 tps when I disable write caching. WOW. A factor of
>> about 20 times slower. (pgbench -c 4 -t 100)
>
> That's completely consistent with tests Chris Browne has done here on
> cache-enabled and cache-disabled boxes that we have.
>
> It's a _really_ big difference. The combination of battery-backed
> write cache on your controller plus a real good UPS is quite possibly
> the number one thing you can do to improve performance. For what
> it's worth, I can't see how this is something special about Postgres:
> even raw-filesystem type systems have to make sure the disk actually
> has the data, and a write cache is bound to be a big help for that.
Indeed.
When I ran the tests, I found that JFS was preferable to XFS and ext3
on Linux on the machine with the big battery backed cache. (And the
side-effect that it was getting yes, probably about 20x the
performance of systems without the cache.)
The FS-related result appeared surprising, as the "stories" I had
heard suggested that JFS hadn't been particularly heavily tuned on
Linux, whereas XFS was supposed to be the "speed demon."
It is entirely possible that the result I saw was one that would
reverse partially or even totally on a system LACKING that cache. XFS
might "play better" when we're cacheless; the (perhaps only fabled)
demerits of JFS being more than totally hidden if we add the cache.
What I find disappointing is that it isn't possible to get SSD cards
that are relatively inexpensive. A similarly fabulous performance
increase _ought_ to be attainable if you could stick pg_xlog and
pg_clog on a 256MB (or bigger!) battery-backed SSD, ideally one that
plugs into a PCI slot.
This should have the further benefit of diminishing the amount of
mechanical activity going on, as WAL activity would no longer involve
ANY i/o operations.
Unfortunately, while there are companies hawking SSDs, they are in the
"you'll have to talk to our salescritter for pricing" category, which
means that they must be ferociously expensive. :-(.
--
output = ("cbbrowne" "@" "libertyrms.info")
[PERFORM] Optimizing >= and <= for numbers and dates
Hi all, I haven't found any official documentation about the postgres sql optimizer on the web, so please forgive me if there is such a document and point me to the right direction. I've got the following problem: I cannot make the postgres SQL Optimizer use an index on a date field to filter out a date range, e.g. select * from mytable where mydate >= '2003-10-01'; Seq Scan on mytable (cost=0.00..2138.11 rows=12203 width=543) Filter: (mydate >= '2003-09-01'::date) the index is created as follows: create index query on mytable(mydate); Testing for equality gives me the index optimization: select * from mytable where mydate = '2003-10-01'; Index Scan using query on mytable (cost=0.00..54.93 rows=44 width=543) Index Cond: (mydate = '2003-09-01'::date) I have run vacuum analyze on the table. Also the table contains 25.000 records, so the index should be used in my opinion. Am I missing something ? The same seems to apply to integers. Thank you very much in advance Dimi PS The postgres version is as follows: PostgreSQL 7.3.2 on i386-redhat-linux-gnu, compiled by GCC i386-redhat-linux-gcc (GCC) 3.2.2 20030213 (Red Hat Linux 8.0 3.2.2-1) -- NEU FÜR ALLE - GMX MediaCenter - für Fotos, Musik, Dateien... Fotoalbum, File Sharing, MMS, Multimedia-Gruß, GMX FotoService Jetzt kostenlos anmelden unter http://www.gmx.net +++ GMX - die erste Adresse für Mail, Message, More! +++ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] TPC-R benchmarks
The output of the query should contain about 200 rows. So, I guess the planer is off assuming that the query should return 1 row. I will start EXPLAIN ANALYZE now. Thanks. Oleg -Original Message- From: scott.marlowe [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 7:23 AM To: Oleg Lebedev Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] TPC-R benchmarks On Tue, 30 Sep 2003, Oleg Lebedev wrote: > I continue struggling with the TPC-R benchmarks and wonder if anyone > could help me optimize the query below. ANALYZE statistics indicate > that the query should run relatively fast, but it takes hours to > complete. I attached the query plan to this posting. Thanks. What are the differences between estimated and real rows and such of an explain analyze on that query? Are there any estimates that are just way off? * This e-mail may contain privileged or confidential material intended for the named recipient only. If you are not the named recipient, delete this message and all attachments. Unauthorized reviewing, copying, printing, disclosing, or otherwise using information in this e-mail is prohibited. We reserve the right to monitor e-mail sent through our network. * ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Optimizing >= and <= for numbers and dates
On Wed, 2003-10-01 at 13:30, Dimitri Nagiev wrote: > Hi all, > > I haven't found any official documentation about the postgres sql optimizer > on the web, so please forgive me if there is such a document and point me to > the right direction. > > I've got the following problem: I cannot make the postgres SQL Optimizer use > an index on a date field to filter out a date range, e.g. > > select * from mytable where mydate >= '2003-10-01'; > > Seq Scan on mytable (cost=0.00..2138.11 rows=12203 width=543) >Filter: (mydate >= '2003-09-01'::date) EXPLAIN ANALYZE output please. signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Tuning/performance issue...
On Wed, 1 Oct 2003, Oleg Lebedev wrote: > Jeff, > I would really appreciate if you could send me that lengthy presentation > that you've written on pg/other dbs comparison. > Thanks. > After I give the presentation at work and collect comments from my coworkers (and remove some information you folks don't need to know :) I will be very willing to post it for people to see. -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 3: 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] [NOVICE] Ideal Hardware?
Jason, Your question is really suited to the PERFORMANCE list, not NOVICE, so I have cross-posted it there. I reccomend that you subscribe to performance, and drop novice from your replies. There are lots of hardware geeks on performance, but few on novice. > We have an opportunity to purchase a new, top-notch database server. I am > wondering what kind of hardware is recommended? We're on Linux platforms > and kernels though. I remember a comment from Tom about how he was spending > a lot of time debugging problems which turned out to be hardware-related. I > of course would like to avoid that. > > In terms of numbers, we expect have an average of 100 active connections > (most of which are idle 9/10ths of the time), with about 85% reading > traffic. I expect the database with flow average 10-20kBps under moderate > load. I hope to have one server host about 1000-2000 active databases, with > the largest being about 60 meg (no blobs). Inactive databases will only be > for reading (archival) purposes, and will seldom be accessed. Is that 100 concurrent connections *total*, or per-database? If the connections are idle 90% of the time, then are they open, or do they get re-established with each query? Have you considered connection pooling for the read-only queries? > Does any of this represent a problem for Postgres? The datasets are > typically not that large, only a few queries on a few databases ever return > over 1000 rows. I'm worried about being able to handle the times when there > will be spikes in the traffic. It's all possible, it just requires careful application design and lots of hardware. You should also cost things out; sometimes it's cheaper to have several good servers instead of one uber-server. The latter also helps with hardware replacement. > The configuration that is going on in my head is: > RAID 1, 200gig RAID 1+0 can be good for Postgres. However, if you have a budget, RAID 5 with 6 or more disks can be better some of the time, particularly when read queries are the vast majority of the load. There are, as yet, no difinitive statistics, but OSDL is working on it! More important than the RAID config is the RAID card; once again, with money, multi-channel RAID cards with a battery-backed write cache are your best bet; some cards even allow you to span RAID1 between cards of the same model. See the discussion about LSI MegaRaid in the PERFORMANCE list archives over the last 2 weeks. > 1 server, 4g ram > Linux 2.6 You're very brave. Me, I'm not adopting 2.6 in production until 2.6.03 is out, at least. > I was also wondering about storage units (IBM FAStT200) with giga-bit > Ethernet to rack mount computer(s)... But would I need more than 1 CPU? If > I did, how would I handle the file system? We only do a few joins, so I > think most of it would be I/O latency. PostgreSQL will make use of multiple processors. If you are worried about peak time loads, having 2-4 processors to distribute queries across would be very useful. Also, I'm concerned about the "we only do a few joins".What that says to me is "we don't really know how to write complex queries, so we pull a lot of redundant data." Simple queries can be far less efficient than complex ones if they result in you pulling entire tables across to the client. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: 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] Optimizing >= and <= for numbers and dates
here goes the EXPLAIN ANALYZE output: template1=# VACUUM analyze mytable; VACUUM template1=# explain analyze select * from mytable where mydate>='2003-09-01'; QUERY PLAN --- Seq Scan on mytable (cost=0.00..2209.11 rows=22274 width=562) (actual time=0.06..267.30 rows=22677 loops=1) Filter: (mydate >= '2003-09-01'::date) Total runtime: 307.71 msec (3 rows) template1=# explain analyze select * from mytable where mydate='2003-09-01'; QUERY PLAN -- Index Scan using mytable_query on mytable (cost=0.00..148.56 rows=43 width=562) (actual time=41.22..41.27 rows=4 loops=1) Index Cond: (mydate = '2003-09-01'::date) Total runtime: 41.34 msec (3 rows) > On Wed, 2003-10-01 at 13:30, Dimitri Nagiev wrote: > > Hi all, > > > > I haven't found any official documentation about the postgres sql > optimiz > er > > on the web, so please forgive me if there is such a document and point > me > to > > the right direction. > > > > I've got the following problem: I cannot make the postgres SQL Optimizer > use > > an index on a date field to filter out a date range, e.g. > > > > select * from mytable where mydate >= '2003-10-01'; > > > > Seq Scan on mytable (cost=0.00..2138.11 rows=12203 width=543) > >Filter: (mydate >= '2003-09-01'::date) > > EXPLAIN ANALYZE output please. > -- NEU FÜR ALLE - GMX MediaCenter - für Fotos, Musik, Dateien... Fotoalbum, File Sharing, MMS, Multimedia-Gruß, GMX FotoService Jetzt kostenlos anmelden unter http://www.gmx.net +++ GMX - die erste Adresse für Mail, Message, More! +++ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Tuning/performance issue...
That would be great! When do you think this would be ready for us to see ;?) -Original Message- From: Jeff [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 11:42 AM To: Oleg Lebedev Cc: [EMAIL PROTECTED] Subject: RE: [PERFORM] Tuning/performance issue... On Wed, 1 Oct 2003, Oleg Lebedev wrote: > Jeff, > I would really appreciate if you could send me that lengthy > presentation that you've written on pg/other dbs comparison. Thanks. > After I give the presentation at work and collect comments from my coworkers (and remove some information you folks don't need to know :) I will be very willing to post it for people to see. -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ * This e-mail may contain privileged or confidential material intended for the named recipient only. If you are not the named recipient, delete this message and all attachments. Unauthorized reviewing, copying, printing, disclosing, or otherwise using information in this e-mail is prohibited. We reserve the right to monitor e-mail sent through our network. * ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] TPC-R benchmarks
Oleg, > The output of the query should contain about 200 rows. So, I guess the > planer is off assuming that the query should return 1 row. Oh, also did you post the query before? Can you re-post it with the planner results? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] TPC-R benchmarks
Sure, below is the query. I attached the plan to this posting.
select
nation,
o_year,
sum(amount) as sum_profit
from
(
select
n_name as nation,
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) -
ps_supplycost * l_quantity as amount
from
part,
supplier,
lineitem,
partsupp,
orders,
nation
where
s_suppkey = l_suppkey
and ps_suppkey = l_suppkey
and ps_partkey = l_partkey
and p_partkey = l_partkey
and o_orderkey = l_orderkey
and s_nationkey = n_nationkey
and p_name like '%green%'
) as profit
group by
nation,
o_year
order by
nation,
o_year desc;
-Original Message-
From: Josh Berkus [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 01, 2003 11:42 AM
To: Oleg Lebedev; scott.marlowe
Cc: [EMAIL PROTECTED]
Subject: Re: [PERFORM] TPC-R benchmarks
Oleg,
> The output of the query should contain about 200 rows. So, I guess the
> planer is off assuming that the query should return 1 row.
Oh, also did you post the query before? Can you re-post it with the
planner
results?
--
Josh Berkus
Aglio Database Solutions
San Francisco
*
This e-mail may contain privileged or confidential material intended for the named
recipient only.
If you are not the named recipient, delete this message and all attachments.
Unauthorized reviewing, copying, printing, disclosing, or otherwise using information
in this e-mail is prohibited.
We reserve the right to monitor e-mail sent through our network.
*
-> Sort (cost=54597.49..54597.50 rows=1 width=121)
Sort Key: nation.n_name, date_part('year'::text,orders.o_orderdate)
-> Aggregate (cost=54597.45..54597.48 rows=1 width=121)
-> Group (cost=54597.45..54597.47 rows=3 width=121)
-> Sort (cost=54597.45..54597.46 rows=3 width=121)
Sort Key: nation.n_name, date_part('year'::text, orders.o_orderdate)
-> Hash Join (cost=54596.00..54597.42 rows=3 width=121)
Hash Cond: ("outer".n_nationkey = "inner".s_nationkey)
-> Seq Scan on nation (cost=0.00..1.25 rows=25 width=33)
-> Hash (cost=54596.00..54596.00 rows=3 width=88)
-> Nested Loop (cost=0.00..54596.00 rows=3 width=88)
Join Filter: ("inner".s_suppkey = "outer".l_suppkey)
-> Nested Loop (cost=0.00..54586.18 rows=3 width=80)
-> Nested Loop (cost=0.00..54575.47 rows=4 width=68)
Join Filter: ("outer".p_partkey = "inner".ps_partkey)
-> Nested Loop (cost=0.00..22753.33 rows=9343 width=49)
-> Seq Scan on part (cost=0.00..7868.00 rows=320 width=4)
Filter: (p_name ~~ '%green%'::text)
-> Index Scan using i_l_partkey on lineitem (cost=0.00..46.15 rows=29 width=45)
Index
Cond: ("outer".p_partkey = lineitem.l_partkey)
-> Index Scan using pk_partsupp on partsupp (cost=0.00..3.39 rows=1 width=19)
Index Cond: ((partsupp.ps_partkey = "outer".l_partkey) AND (partsupp.ps_suppkey =
"outer".l_suppkey))
-> Index Scan using pk_orders on orders (cost=0.00..3.01 rows=1 width=12)
Index Cond: (orders.o_orderkey = "outer".l_orderkey)
-> Index Scan using pk_supplier on supplier (cost=0.00..3.01 rows=1 width=8)
Index Cond: ("outer".ps_suppkey = supplier.s_suppkey) (27 rows)
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Optimizing >= and <= for numbers and dates
On Wed, 2003-10-01 at 13:45, Dimitri Nagiev wrote: > template1=# explain analyze select * from mytable where > mydate>='2003-09-01'; > QUERY PLAN > > > --- > Seq Scan on mytable (cost=0.00..2209.11 rows=22274 width=562) (actual > time=0.06..267.30 rows=22677 loops=1) >Filter: (mydate >= '2003-09-01'::date) > Total runtime: 307.71 msec > (3 rows) It may well be the case that a seqscan is faster than an index scan for this query. Try disabling sequential scans (SET enable_seqscan = false) and re-running EXPLAIN ANALYZE: see if the total runtime is smaller or larger. -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Optimizing >= and <= for numbers and dates
On Wed, 1 Oct 2003, Dimitri Nagiev wrote: > here goes the EXPLAIN ANALYZE output: > > > template1=# VACUUM analyze mytable; > VACUUM > template1=# explain analyze select * from mytable where > mydate>='2003-09-01'; > QUERY PLAN > > > --- > Seq Scan on mytable (cost=0.00..2209.11 rows=22274 width=562) (actual > time=0.06..267.30 rows=22677 loops=1) >Filter: (mydate >= '2003-09-01'::date) > Total runtime: 307.71 msec > (3 rows) How many rows are there in this table? If the number is only two or three times as many as the number of rows returned (22677) then a seq scan is preferable. The way to tune your random_page_cost is to keep making your range more selective until you get an index scan. Then, see what the difference is in speed between the two queries that sit on either side of that number, i.e. if a query that returns 1000 rows switches to index scan, and takes 100 msec, while one that returns 1050 uses seq scan and takes 200 msec, then you might want to lower your random page cost. Ideally, what should happen is that as the query returns more and more rows, the switch to seq scan should happen so that it's taking about the same amount of time as the index scan, maybe just a little more. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Optimizing >= and <= for numbers and dates
Oh, to followup on my previously sent post, make sure you've got effective_cache_size set right BEFORE you go trying to set random_page_cost, and you might wanna run a select * from table to load the table into kernel buffer cache before testing, then also test it with the cache cleared out (select * from a_different_really_huge_table will usually do that.) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Optimizing >= and <= for numbers and dates
On Wed, 1 Oct 2003 19:45:29 +0200 (MEST), "Dimitri Nagiev" <[EMAIL PROTECTED]> wrote: >template1=# explain analyze select * from mytable where >mydate>='2003-09-01'; > Seq Scan on mytable (cost=0.00..2209.11 rows=22274 width=562) (actual > time=0.06..267.30 rows=22677 loops=1) > Filter: (mydate >= '2003-09-01'::date) > Total runtime: 307.71 msec Didn't you say that there are 25000 rows in the table? I can't believe that for selecting 90% of all rows an index scan would be faster. Try SET enable_seqscan = 0; explain analyze select * from mytable where mydate>='2003-09-01'; If you find the index scan to be faster, there might be lots of dead tuples in which case you should VACUUM FULL mytable; Servus Manfred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] TPC-R benchmarks
All right, my query just finished running with EXPLAIN ANALYZE.
I show the plan below and also attached it as a file.
Any ideas?
-> Sort (cost=54597.49..54597.50 rows=1 width=121) (actual
time=6674562.03..6674562.15 rows=175 loops=1)
Sort Key: nation.n_name, date_part('year'::text,
orders.o_orderdate)
-> Aggregate (cost=54597.45..54597.48 rows=1 width=121)
(actual time=6668919.41..6674522.48 rows=175 loops=1)
-> Group (cost=54597.45..54597.47 rows=3 width=121)
(actual time=6668872.68..6672136.96 rows=348760 loops=1)
-> Sort (cost=54597.45..54597.46 rows=3
width=121) (actual time=6668872.65..6669499.95 rows=348760 loops=1)
Sort Key: nation.n_name,
date_part('year'::text, orders.o_orderdate)
-> Hash Join (cost=54596.00..54597.42
rows=3
width=121) (actual time=6632768.89..6650192.67 rows=348760 loops=1)
Hash Cond: ("outer".n_nationkey =
"inner".s_nationkey)
-> Seq Scan on nation
(cost=0.00..1.25 rows=25 width=33) (actual time=6.75..7.13 rows=25
loops=1)
-> Hash (cost=54596.00..54596.00
rows=3
width=88) (actual time=6632671.96..6632671.96 rows=0 loops=1)
-> Nested Loop
(cost=0.00..54596.00 rows=3 width=88) (actual time=482.41..6630601.46
rows=348760 loops=1)
Join Filter:
("inner".s_suppkey = "outer".l_suppkey)
-> Nested Loop
(cost=0.00..54586.18 rows=3 width=80) (actual time=383.87..6594984.40
rows=348760 loops=1)
-> Nested Loop
(cost=0.00..54575.47 rows=4 width=68) (actual time=199.95..3580882.07
rows=348760 loops=1)
Join Filter:
("outer".p_partkey = "inner".ps_partkey)
-> Nested Loop
(cost=0.00..22753.33 rows=9343 width=49) (actual time=146.85..3541433.10
rows=348760 loops=1)
-> Seq
Scan on part (cost=0.00..7868.00 rows=320 width=4) (actual
time=33.64..15651.90 rows=11637 loops=1)
Filter: (p_name ~~ '%green%'::text)
-> Index
Scan using i_l_partkey on lineitem (cost=0.00..46.15 rows=29 width=45)
(actual time=10.71..302.67 rows=30 loops=11637)
Index
Cond: ("outer".p_partkey = lineitem.l_partkey)
-> Index Scan
using pk_partsupp on partsupp (cost=0.00..3.39 rows=1 width=19) (actual
time=0.09..0.09 rows=1 loops=348760)
Index
Cond: ((partsupp.ps_partkey = "outer".l_partkey) AND
(partsupp.ps_suppkey =
"outer".l_suppkey))
-> Index Scan using
pk_orders on orders (cost=0.00..3.01 rows=1 width=12) (actual
time=8.62..8.62 rows=1 loops=348760)
Index Cond:
(orders.o_orderkey = "outer".l_orderkey)
-> Index Scan using
pk_supplier on supplier (cost=0.00..3.01 rows=1 width=8) (actual
time=0.08..0.08 rows=1 loops=348760)
Index Cond:
("outer".ps_suppkey = supplier.s_suppkey) Total runtime: 6674724.23
msec (28 rows)
-Original Message-
From: Oleg Lebedev
Sent: Wednesday, October 01, 2003 12:00 PM
To: Josh Berkus; scott.marlowe
Cc: [EMAIL PROTECTED]
Subject: Re: [PERFORM] TPC-R benchmarks
Importance: Low
Sure, below is the query. I attached the plan to this posting.
select
nation,
o_year,
sum(amount) as sum_profit
from
(
select
n_name as nation,
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) -
ps_supplycost * l_quantity as amount
from
part,
supplier,
lineitem,
partsupp,
orders,
nation
where
s_suppkey = l_suppkey
and ps_suppkey = l_suppkey
and ps_partkey = l_partkey
and p_partkey = l_partkey
and o_orderkey = l_orderkey
and s_nationkey = n_nationkey
and p_name like '%green%'
) as profit
group by
nation,
o_year
order by
nation,
o_year desc;
-Original Message-
From: Josh Berkus [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 01, 2003 11:42 AM
To: Oleg Lebedev; scott.marlowe
Cc: [EMAIL PROTECTED]
Subject: Re: [PERFORM
Re: [PERFORM] TPC-R benchmarks
For troubleshooting, can you try it with "set enable_nestloop = false" and
rerun the query and see how long it takes?
It looks like the estimates of rows returned is WAY off (estimate is too
low compared to what really comes back.)
Also, you might try to alter the table.column to have a higher target on
the rows p_partkey and ps_partkey and any others where the estimate is so
far off of the reality.
On Wed, 1 Oct 2003, Oleg Lebedev wrote:
> All right, my query just finished running with EXPLAIN ANALYZE.
> I show the plan below and also attached it as a file.
> Any ideas?
>
>-> Sort (cost=54597.49..54597.50 rows=1 width=121) (actual
> time=6674562.03..6674562.15 rows=175 loops=1)
> Sort Key: nation.n_name, date_part('year'::text,
> orders.o_orderdate)
> -> Aggregate (cost=54597.45..54597.48 rows=1 width=121)
> (actual time=6668919.41..6674522.48 rows=175 loops=1)
>-> Group (cost=54597.45..54597.47 rows=3 width=121)
> (actual time=6668872.68..6672136.96 rows=348760 loops=1)
> -> Sort (cost=54597.45..54597.46 rows=3
> width=121) (actual time=6668872.65..6669499.95 rows=348760 loops=1)
>Sort Key: nation.n_name,
> date_part('year'::text, orders.o_orderdate)
>-> Hash Join (cost=54596.00..54597.42
> rows=3
> width=121) (actual time=6632768.89..6650192.67 rows=348760 loops=1)
> Hash Cond: ("outer".n_nationkey =
> "inner".s_nationkey)
> -> Seq Scan on nation
> (cost=0.00..1.25 rows=25 width=33) (actual time=6.75..7.13 rows=25
> loops=1)
> -> Hash (cost=54596.00..54596.00
> rows=3
> width=88) (actual time=6632671.96..6632671.96 rows=0 loops=1)
>-> Nested Loop
> (cost=0.00..54596.00 rows=3 width=88) (actual time=482.41..6630601.46
> rows=348760 loops=1)
> Join Filter:
> ("inner".s_suppkey = "outer".l_suppkey)
> -> Nested Loop
> (cost=0.00..54586.18 rows=3 width=80) (actual time=383.87..6594984.40
> rows=348760 loops=1)
>-> Nested Loop
> (cost=0.00..54575.47 rows=4 width=68) (actual time=199.95..3580882.07
> rows=348760 loops=1)
> Join Filter:
> ("outer".p_partkey = "inner".ps_partkey)
> -> Nested Loop
> (cost=0.00..22753.33 rows=9343 width=49) (actual time=146.85..3541433.10
> rows=348760 loops=1)
>-> Seq
> Scan on part (cost=0.00..7868.00 rows=320 width=4) (actual
> time=33.64..15651.90 rows=11637 loops=1)
>
> Filter: (p_name ~~ '%green%'::text)
>-> Index
> Scan using i_l_partkey on lineitem (cost=0.00..46.15 rows=29 width=45)
> (actual time=10.71..302.67 rows=30 loops=11637)
>
> Index
> Cond: ("outer".p_partkey = lineitem.l_partkey)
> -> Index Scan
> using pk_partsupp on partsupp (cost=0.00..3.39 rows=1 width=19) (actual
> time=0.09..0.09 rows=1 loops=348760)
>Index
> Cond: ((partsupp.ps_partkey = "outer".l_partkey) AND
> (partsupp.ps_suppkey =
> "outer".l_suppkey))
>-> Index Scan using
> pk_orders on orders (cost=0.00..3.01 rows=1 width=12) (actual
> time=8.62..8.62 rows=1 loops=348760)
> Index Cond:
> (orders.o_orderkey = "outer".l_orderkey)
> -> Index Scan using
> pk_supplier on supplier (cost=0.00..3.01 rows=1 width=8) (actual
> time=0.08..0.08 rows=1 loops=348760)
>Index Cond:
> ("outer".ps_suppkey = supplier.s_suppkey) Total runtime: 6674724.23
> msec (28 rows)
>
>
> -Original Message-
> From: Oleg Lebedev
> Sent: Wednesday, October 01, 2003 12:00 PM
> To: Josh Berkus; scott.marlowe
> Cc: [EMAIL PROTECTED]
> Subject: Re: [PERFORM] TPC-R benchmarks
> Importance: Low
>
>
> Sure, below is the query. I attached the plan to this posting.
>
> select
> nation,
> o_year,
> sum(amount) as sum_profit
> from
> (
> select
> n_name as nation,
> extract(year from o_orderdate) as o_year,
> l_extendedprice * (1 - l_discount) -
> ps_supplycost * l_quantity as amount
> from
> part,
> supplier,
> lineitem,
> partsupp,
> orders,
> nation
> where
>
Re: [PERFORM] TPC-R benchmarks
Oleg Lebedev <[EMAIL PROTECTED]> writes: > All right, my query just finished running with EXPLAIN ANALYZE. > I show the plan below and also attached it as a file. > Any ideas? Uh, have you done an ANALYZE (or VACUUM ANALYZE) on this database? It sure looks like the planner thinks the tables are a couple of orders of magnitude smaller than they actually are. Certainly the estimated sizes of the joins are way off :-( If you did analyze, it might help to increase the statistics target and re-analyze. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] TPC-R benchmarks
Oleg,
> All right, my query just finished running with EXPLAIN ANALYZE.
> I show the plan below and also attached it as a file.
> Any ideas?
Yes. Your problem appears to be right here:
>-> Nested Loop
> (cost=0.00..54596.00 rows=3 width=88) (actual time=482.41..6630601.46
> rows=348760 loops=1)
> Join Filter:
> ("inner".s_suppkey = "outer".l_suppkey)
> -> Nested Loop
> (cost=0.00..54586.18 rows=3 width=80) (actual time=383.87..6594984.40
> rows=348760 loops=1)
>-> Nested Loop
> (cost=0.00..54575.47 rows=4 width=68) (actual time=199.95..3580882.07
> rows=348760 loops=1)
> Join Filter:
> ("outer".p_partkey = "inner".ps_partkey)
> -> Nested Loop
> (cost=0.00..22753.33 rows=9343 width=49) (actual time=146.85..3541433.10
> rows=348760 loops=1)
For some reason, the row estimate on the supplier --> lineitem join is bad, as
is the estimate on part --> partsupp.Let me first check two things:
1) You have an index on l_suppkey and on ps_partkey.
2) you have run ANALYZE on your whole database before the query
If both of those are true, I'd like to see the lines in pg_stats that apply to
ps_partkey and l_suppkey; please do a:
SELECT * FROM pg_stats WHERE attname = 'l_suppkey' or attname = 'ps_partkey'
--
-Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
