Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 3DE979FB2EB for ; Mon, 15 Jan 2007 11:54:06 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 51690-02-2 for ; Mon, 15 Jan 2007 11:52:50 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from lists.commandprompt.com (host-254.commandprompt.net [207.173.203.254]) by postgresql.org (Postfix) with ESMTP id 64D219FB436 for ; Mon, 15 Jan 2007 11:49:12 -0400 (AST) Received: from [192.168.1.50] (or-67-76-146-141.sta.embarqhsd.net [67.76.146.141]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l0FFmtaI030782 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Mon, 15 Jan 2007 07:49:00 -0800 Message-ID: <45ABA316.4060202@commandprompt.com> Date: Mon, 15 Jan 2007 07:51:50 -0800 From: "Joshua D. Drake" Organization: Command Prompt, Inc. User-Agent: Thunderbird 1.5.0.9 (X11/20070103) MIME-Version: 1.0 To: Luke Lonergan CC: Adam Rich , Tom Lane , "Craig A. James" , PostgreSQL Performance Subject: Re: max() versus order/limit (WAS: High update References: In-Reply-To: X-Enigmail-Version: 0.94.0.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: ClamAV version 0.88.5, clamav-milter version 0.88.5 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Mon, 15 Jan 2007 07:49:05 -0800 (PST) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/234 X-Sequence-Number: 22835 Luke Lonergan wrote: > Adam, > > This optimization would require teaching the planner to use an index for > MAX/MIN when available. It seems like an OK thing to do to me. Uhmmm I thought we did that already in 8.1? Joshua D. Drake > > - Luke > >> -----Original Message----- >> From: pgsql-performance-owner@postgresql.org >> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Adam Rich >> Sent: Sunday, January 14, 2007 8:52 PM >> To: 'Joshua D. Drake'; 'Tom Lane' >> Cc: 'Craig A. James'; 'PostgreSQL Performance' >> Subject: Re: [PERFORM] max() versus order/limit (WAS: High >> update activity, PostgreSQL vs BigDBMS) >> >> >> Did anybody get a chance to look at this? Is it expected behavior? >> Everyone seemed so incredulous, I hoped maybe this exposed a >> bug that would be fixed in a near release. >> >> >> -----Original Message----- >> From: Adam Rich [mailto:adam.r@sbcglobal.net] >> Sent: Sunday, January 07, 2007 11:53 PM >> To: 'Joshua D. Drake'; 'Tom Lane' >> Cc: 'Craig A. James'; 'PostgreSQL Performance' >> Subject: RE: [PERFORM] High update activity, PostgreSQL vs BigDBMS >> >> >> >> Here's another, more drastic example... Here the order by / limit >> version >> runs in less than 1/7000 the time of the MAX() version. >> >> >> select max(item_id) >> from events e, receipts r, receipt_items ri >> where e.event_id=r.event_id and r.receipt_id=ri.receipt_id >> >> Aggregate (cost=10850.84..10850.85 rows=1 width=4) (actual >> time=816.382..816.383 rows=1 loops=1) >> -> Hash Join (cost=2072.12..10503.30 rows=139019 width=4) (actual >> time=155.177..675.870 rows=147383 loops=1) >> Hash Cond: (ri.receipt_id = r.receipt_id) >> -> Seq Scan on receipt_items ri (cost=0.00..4097.56 >> rows=168196 width=8) (actual time=0.009..176.894 rows=168196 loops=1) >> -> Hash (cost=2010.69..2010.69 rows=24571 width=4) (actual >> time=155.146..155.146 rows=24571 loops=1) >> -> Hash Join (cost=506.84..2010.69 rows=24571 width=4) >> (actual time=34.803..126.452 rows=24571 loops=1) >> Hash Cond: (r.event_id = e.event_id) >> -> Seq Scan on receipts r (cost=0.00..663.58 >> rows=29728 width=8) (actual time=0.006..30.870 rows=29728 loops=1) >> -> Hash (cost=469.73..469.73 rows=14843 width=4) >> (actual time=34.780..34.780 rows=14843 loops=1) >> -> Seq Scan on events e (cost=0.00..469.73 >> rows=14843 width=4) (actual time=0.007..17.603 rows=14843 loops=1) >> Total runtime: 816.645 ms >> >> select item_id >> from events e, receipts r, receipt_items ri >> where e.event_id=r.event_id and r.receipt_id=ri.receipt_id >> order by item_id desc limit 1 >> >> >> Limit (cost=0.00..0.16 rows=1 width=4) (actual >> time=0.047..0.048 rows=1 >> loops=1) >> -> Nested Loop (cost=0.00..22131.43 rows=139019 width=4) (actual >> time=0.044..0.044 rows=1 loops=1) >> -> Nested Loop (cost=0.00..12987.42 rows=168196 width=8) >> (actual time=0.032..0.032 rows=1 loops=1) >> -> Index Scan Backward using receipt_items_pkey on >> receipt_items ri (cost=0.00..6885.50 rows=168196 width=8) (actual >> time=0.016..0.016 rows=1 loops=1) >> -> Index Scan using receipts_pkey on receipts r >> (cost=0.00..0.02 rows=1 width=8) (actual time=0.010..0.010 rows=1 >> loops=1) >> Index Cond: (r.receipt_id = ri.receipt_id) >> -> Index Scan using events_pkey on events e (cost=0.00..0.04 >> rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=1) >> Index Cond: (e.event_id = r.event_id) >> Total runtime: 0.112 ms >> >> >> >> >> >> -----Original Message----- >> From: pgsql-performance-owner@postgresql.org >> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Joshua D. >> Drake >> Sent: Sunday, January 07, 2007 9:10 PM >> To: Adam Rich >> Cc: 'Craig A. James'; 'Guy Rouillier'; 'PostgreSQL Performance' >> Subject: Re: [PERFORM] High update activity, PostgreSQL vs BigDBMS >> >> >> On Sun, 2007-01-07 at 20:26 -0600, Adam Rich wrote: >>> I'm using 8.2 and using order by & limit is still faster than MAX() >>> even though MAX() now seems to rewrite to an almost identical plan >>> internally. >> >> Gonna need you to back that up :) Can we get an explain analyze? >> >> >>> Count(*) still seems to use a full table scan rather than an index >> scan. >> There is a TODO out there to help this. Don't know if it will >> get done. >> >> Joshua D. Drake >> >> -- >> >> === The PostgreSQL Company: Command Prompt, Inc. === >> Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 >> Providing the most comprehensive PostgreSQL solutions since 1997 >> http://www.commandprompt.com/ >> >> Donate to the PostgreSQL Project: >> http://www.postgresql.org/about/donate >> >> >> >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 4: Have you searched our list archives? >> >> http://archives.postgresql.org >> >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 7: You can help support the PostgreSQL project by donating at >> >> http://www.postgresql.org/about/donate >> >> > > > ---------------------------(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 > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/