Why is this view 9x slower than the base table?
cnuapp_prod@xxxxxxxxxxxxxx=# 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@xxxxxxxxxxxxxx=# 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@xxxxxxxxxxxxxx (512) 569-9461
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
