Re: Oddity with view

Jim 'Decibel!' Nasby wrote:
> 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...

> -> 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)

> -> 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)

It's the width - the view is fetching all the rows. Is the "true as
committed" bit confusing it?

--
Richard Huxton
Archonet Ltd