From: Richard Huxton <dev@archonet.com>
To: Jim 'Decibel!' Nasby <jnasby@cashnetusa.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: postgresql performance list <pgsql-performance@postgresql.org>
Subject: Re: Oddity with view
Date: Mon, 10 Nov 2008 18:21:27 +0000
Message-ID: <49187BA7.8020403@archonet.com> (raw)
In-Reply-To: <7B598355-07C0-450A-9914-B5979F48A5B3@cashnetusa.com>
References: <3CF0CFC1-FDBA-4A61-ACAA-6AF556C4C6F4@cashnetusa.com>
<3128.1226322412@sss.pgh.pa.us>
<7B598355-07C0-450A-9914-B5979F48A5B3@cashnetusa.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, jnasby@cashnetusa.com, tgl@sss.pgh.pa.us
Subject: Re: Oddity with view
In-Reply-To: <49187BA7.8020403@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox