From: Joshua Marsh <icub3d@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Postgresql 8.1.4 - performance issues for select on view using max
Date: Wed, 18 Oct 2006 14:21:05 -0600
Message-ID: <38242de90610181321g4e550b0s18c8c7dbdf253f77@mail.gmail.com> (raw)
In-Reply-To: <20061018195134.72985.qmail@web55902.mail.re3.yahoo.com>
References: <20061018195134.72985.qmail@web55902.mail.re3.yahoo.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: icub3d@gmail.com
Subject: Re: Postgresql 8.1.4 - performance issues for select on view using max
In-Reply-To: <38242de90610181321g4e550b0s18c8c7dbdf253f77@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox