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.