Message-ID: <49187BA7.8020403@archonet.com>
Date: Mon, 10 Nov 2008 18:21:27 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.17 (X11/20080914)
MIME-Version: 1.0
To: "Jim 'Decibel!' Nasby" <jnasby@cashnetusa.com>
CC: Tom Lane <tgl@sss.pgh.pa.us>,
	postgresql performance list <pgsql-performance@postgresql.org>
Subject: Re: Oddity with view
References: <3CF0CFC1-FDBA-4A61-ACAA-6AF556C4C6F4@cashnetusa.com>
	<3128.1226322412@sss.pgh.pa.us>
	<7B598355-07C0-450A-9914-B5979F48A5B3@cashnetusa.com>
In-Reply-To: <7B598355-07C0-450A-9914-B5979F48A5B3@cashnetusa.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Jim 'Decibel!' Nasby wrote:
> On Nov 10, 2008, at 7:06 AM, Tom Lane wrote:
>> "Jim 'Decibel!' Nasby" <jnasby@cashnetusa.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