Re: Oddity with view

On Nov 10, 2008, at 7:06 AM, Tom Lane wrote:
> "Jim 'Decibel!' Nasby" <jnasby(at)cashnetusa(dot)com> writes:
>> loan_tasks effectively does SELECT * FROM loan_tasks_committed UNION
>> ALL SELECT * FROM loan_tasks_pending;.
>
> You seem to have neglected to mention a join or two.

Yeah, though I did show them at the end of the message...

SELECT true AS "committed", loan_tasks_committed.id, ...,
loan_tasks_committed.task_amount
FROM loan_tasks_committed
UNION ALL
SELECT false AS "committed", ltp.id, ..., NULL::"unknown" AS
task_amount
FROM loan_tasks_pending ltp
JOIN loan_task_codes ltc ON ltp.loan_task_code_id = ltc.id;

Thing is, there's no data to be had on that side. All of the time is
going into the seqscan of loan_tasks_committed. But here's what's
really disturbing...

Aggregate (cost=994625.69..994625.70 rows=1 width=0) (actual
time=7432.306..7432.306 rows=1 loops=1)
-> Seq Scan on loan_tasks_committed (cost=0.00..929345.35
rows=26112135 width=0) (actual time=0.012..5116.776 rows=26115689
loops=1)

vs

Aggregate (cost=1516929.75..1516929.76 rows=1 width=0) (actual
time=60396.081..60396.082 rows=1 loops=1)
-> Append (cost=0.00..1190523.94 rows=26112465 width=240)
(actual time=0.023..57902.470 rows=26115689 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..1190466.70
rows=26112135 width=162) (actual time=0.023..54776.335 rows=26115689
loops=1)
-> Seq Scan on loan_tasks_committed
(cost=0.00..929345.35 rows=26112135 width=162) (actual
time=0.014..22531.902 rows=26115689 loops=1)
-> Subquery Scan "*SELECT* 2" (cost=36.10..57.24 rows=330
width=240) (actual time=0.003..0.003 rows=0 loops=1)

How on earth did the seqscan suddenly take 4x longer? And why is the
subquery scan then doubling the amount of time again?
--
Decibel! jnasby(at)cashnetusa(dot)com (512) 569-9461