pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOddity with view
5+ messages / 3 participants
[nested] [flat]
* Oddity with view
@ 2008-11-10 08:27 Jim 'Decibel!' Nasby <jnasby@cashnetusa.com>
0 siblings, 1 reply; 5+ messages in thread
From: Jim 'Decibel!' Nasby @ 2008-11-10 08:27 UTC (permalink / raw)
To: pgsql-performance
Why is this view 9x slower than the base table?
cnuapp_prod@postgres06.nut=# explain analyze select count(*) from
loan_tasks_committed;
QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------
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)
Total runtime: 7432.360 ms
(3 rows)
Time: 7432.858 ms
loan_tasks effectively does SELECT * FROM loan_tasks_committed UNION
ALL SELECT * FROM loan_tasks_pending;. There's some lookup tables for
_pending, but as this explain shows there's no actual data there
right now.
cnuapp_prod@postgres06.nut=# explain analyze select count(*) from
loan_tasks;
QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
---------
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)
-> Hash Join (cost=36.10..53.94 rows=330 width=240)
(actual time=0.002..0.002 rows=0 loops=1)
Hash Cond: (ltp.loan_task_code_id = ltc.id)
-> Seq Scan on loan_tasks_pending ltp
(cost=0.00..13.30 rows=330 width=208) (actual time=0.001..0.001
rows=0 loops=1)
-> Hash (cost=21.60..21.60 rows=1160
width=36) (never executed)
-> Seq Scan on loan_task_codes ltc
(cost=0.00..21.60 rows=1160 width=36) (never executed)
Total runtime: 60396.174 ms
(11 rows)
Time: 60397.046 ms
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;
The stuff I omitted is just some fields and a few other NULLs. This
is 8.2.9.
--
Decibel! jnasby@cashnetusa.com (512) 569-9461
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Oddity with view
@ 2008-11-10 13:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jim 'Decibel!' Nasby <jnasby@cashnetusa.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2008-11-10 13:06 UTC (permalink / raw)
To: Jim 'Decibel!' Nasby <jnasby@cashnetusa.com>; +Cc: pgsql-performance
"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.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Oddity with view
@ 2008-11-10 18:16 Jim 'Decibel!' Nasby <jnasby@cashnetusa.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Jim 'Decibel!' Nasby @ 2008-11-10 18:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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...
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@cashnetusa.com (512) 569-9461
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Oddity with view
@ 2008-11-10 18:21 Richard Huxton <dev@archonet.com>
parent: Jim 'Decibel!' Nasby <jnasby@cashnetusa.com>
1 sibling, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2008-11-10 18:21 UTC (permalink / raw)
To: Jim 'Decibel!' Nasby <jnasby@cashnetusa.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Oddity with view
@ 2008-11-10 18:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jim 'Decibel!' Nasby <jnasby@cashnetusa.com>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2008-11-10 18:31 UTC (permalink / raw)
To: Jim 'Decibel!' Nasby <jnasby@cashnetusa.com>; +Cc: pgsql-performance
"Jim 'Decibel!' Nasby" <jnasby@cashnetusa.com> writes:
> How on earth did the seqscan suddenly take 4x longer? And why is the
> subquery scan then doubling the amount of time again?
Maybe the disk access is less sequential because of the need to fetch
the other table too?
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-11-10 18:31 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-11-10 08:27 Oddity with view Jim 'Decibel!' Nasby <jnasby@cashnetusa.com>
2008-11-10 13:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-11-10 18:16 ` Jim 'Decibel!' Nasby <jnasby@cashnetusa.com>
2008-11-10 18:21 ` Richard Huxton <dev@archonet.com>
2008-11-10 18:31 ` Tom Lane <tgl@sss.pgh.pa.us>
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