Re: Postgresql 8.1.4 - performance issues for select on view using max

On 10/18/06, Ioana Danes <ioanasoftware(at)yahoo(dot)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.