Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 6A8CF9FA4EE for ; Wed, 18 Oct 2006 17:21:12 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 09712-08 for ; Wed, 18 Oct 2006 17:21:08 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.186]) by postgresql.org (Postfix) with ESMTP id F34269FA6B2 for ; Wed, 18 Oct 2006 17:21:07 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id i2so839127nfe for ; Wed, 18 Oct 2006 13:21:06 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=TAGrEJ2dulR55+4Y9543ARfoTbSgIEeI/ndgvFPyZcHVWuDwkPEVXjWSOUgO1n2UXUvwnJrC7EcHgZD6ivYKG58KCORBKf6xcwQfiPilg9J9wkt5PuvS/gp/3t8XVUc3CAAoeuv66/SLSZX7w/JxobuD7N+aOyydoj5nfx7sepY= Received: by 10.49.10.3 with SMTP id n3mr4498103nfi; Wed, 18 Oct 2006 13:21:05 -0700 (PDT) Received: by 10.49.42.19 with HTTP; Wed, 18 Oct 2006 13:21:05 -0700 (PDT) Message-ID: <38242de90610181321g4e550b0s18c8c7dbdf253f77@mail.gmail.com> Date: Wed, 18 Oct 2006 14:21:05 -0600 From: "Joshua Marsh" To: pgsql-performance@postgresql.org Subject: Re: Postgresql 8.1.4 - performance issues for select on view using max In-Reply-To: <20061018195134.72985.qmail@web55902.mail.re3.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_164796_19019557.1161202865866" References: <20061018195134.72985.qmail@web55902.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.382 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_30_40, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200610/371 X-Sequence-Number: 21500 ------=_Part_164796_19019557.1161202865866 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 10/18/06, Ioana Danes wrote: > > # explain select max(transid) from public.transaction; > > QUERY > PLAN > > > -------------------------------------------------------------------------------- > > ---------------------- > Result (cost=0.04..0.05 rows=1 width=0) > InitPlan > -> Limit (cost=0.00..0.04 rows=1 width=8) > -> Index Scan Backward using > pk_transaction on transaction (cost=0.00..357870.46 > > rows=9698002 width=8) > Filter: (transid IS NOT NULL) > (5 rows) This works fine because i recognizes the index for that table and can simply use it to find the max. 2. Select from the view is doing a sequential scan: > --------------------------------------------------- > # explain analyze select max(transid) from > alltransaction; > > QUERY PLAN > > > --------------------------------------------------------------------------------------------------------------------------- > ----------------- > Aggregate (cost=200579993.70..200579993.71 rows=1 > width=8) (actual time=115778.101..115778.103 rows=1 > loops=1) > -> Append (cost=100000000.00..200447315.74 > rows=10614237 width=143) (actual time=0.082..95146.144 > rows=10622206 loops= 1) > -> Seq Scan transaction > (cost=100000000.00..100312397.02 rows=9698002 > width=143) (actual time=0.078..56002.778 rows= > 9706475 loops=1) > -> Seq Scan on transaction > (cost=100000000.00..100028776.35 rows=916235 > width=143) (actual time=8.822..2799.496 rows= > 915731 loops=1) > Total runtime: 115778.200 ms > (5 rows) > > Because this is a view, it cannot use the indexes from the other tables. Everytime you run a query against a view, it recreates itself based on the underlying data. From there it must sort the table based on the i and then return your max. It's probably not a great idea to make a view this way if you are planning on using queries like this regularly because you can't create an index for a view. You could try a query that pulls the max from each table and then grabs the max of these: select max (foo.transid) from (select max(transid) as id from public.transaction union select max(transid) from archive.transaction) as foo; -- This E-mail is covered by the Electronic Communications Privacy Act, 18 U.S.C. 2510-2521 and is legally privileged. This information is confidential information and is intended only for the use of the individual or entity named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. ------=_Part_164796_19019557.1161202865866 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

On 10/18/06, Ioana Danes <ioanasoftware@yahoo.ca> wrote:

# explain select max(transid) from public.transaction;

                                              QUERY
PLAN

--------------------------------------------------------------------------------

----------------------
Result  (cost=0.04..0.05 rows=1 width=0)
   InitPlan
     ->  Limit  (cost=0.00..0.04 rows=1 width=8)
           ->  Index Scan Backward using
pk_transaction on transaction (cost= 0.00..357870.46

rows=9698002 width=8)
                 Filter: (transid IS NOT NULL)
(5 rows)

This works fine because i recognizes the index for that table and can simply use it to find the max. 
 

2. Select from the view is doing a sequential scan:
---------------------------------------------------
# explain analyze select max(transid) from
alltransaction;

QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------
          -----------------
Aggregate  (cost=200579993.70..200579993.71 rows=1
width=8) (actual time=115778.101..115778.103 rows=1
loops=1)
   ->  Append  (cost=100000000.00..200447315.74
rows=10614237 width=143) (actual time=0.082..95146.144
rows=10622206 loops=           1)
         ->  Seq Scan transaction
(cost=100000000.00..100312397.02 rows=9698002
width=143) (actual time=0.078..56002.778 rows=
  9706475 loops=1)
         ->  Seq Scan on transaction
(cost=100000000.00..100028776.35 rows=916235
width=143) (actual time=8.822..2799.496 rows=
915731 loops=1)
Total runtime: 115778.200 ms
(5 rows)


Because this is a view, it cannot use the indexes from the other tables.  Everytime you run a query against a view, it recreates itself based on the underlying data.  From there it must sort the table based on the i and then return your max.

It's probably not a great idea to make a view this way if you are planning on using queries like this regularly because you can't create an index for a view.  You could try a query that pulls the max from each table and then grabs the max of these:

select max (foo.transid) from (select max(transid) as id from public.transaction union select max(transid) from archive.transaction) as foo;

--
This E-mail is covered by the Electronic Communications Privacy Act, 18 U.S.C. 2510-2521 and is legally privileged.

This information is confidential information and is intended only for the use of the individual or entity named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. ------=_Part_164796_19019557.1161202865866--