The following query plans both result from the very same query run on
different servers. They obviously differ drastically, but I don't why
as one db is a slonied copy of the other with identical postgresql.conf
files.
Both databases are vacuum analyzed nightly.
Here is the query:
EXPLAIN ANALYZE
SELECT COUNT(DISTINCT(t.id)) FROM (
SELECT m_object_paper.id
FROM m_object_paper, m_assignment, m_class,
r_comment_rubric_user_object
WHERE m_object_paper.assignment=m_assignment.id
AND m_assignment.class=m_class.id
AND m_class.account IN (SELECT * FROM children_of(32660) as acts)
AND m_object_paper.id = r_comment_rubric_user_object.objectid
UNION
SELECT m_object_paper.id
FROM m_object_paper, m_assignment, m_class, r_quickmark_user_object
WHERE m_object_paper.assignment=m_assignment.id
AND m_assignment.class=m_class.id
AND m_class.account IN (SELECT * FROM children_of(32660) acts)
AND m_object_paper.id = r_quickmark_user_object.objectid)as t;
---
DB1 QUERY PLAN
--
Aggregate (cost=314616.49..314616.49 rows=1 width=4) (actual
time=853.483..853.484 rows=1 loops=1)
-> Subquery Scan t (cost=314568.97..314609.70 rows=2715 width=4)
(actual time=848.574..852.912 rows=354 loops=1)
-> Unique (cost=314568.97..314582.55 rows=2715 width=4)
(actual time=848.568..852.352 rows=354 loops=1)
-> Sort (cost=314568.97..314575.76 rows=2715 width=4)
(actual time=848.565..850.264 rows=2428 loops=1)
Sort Key: id
-> Append (cost=153181.39..314414.12 rows=2715
width=4) (actual time=224.984..844.714 rows=2428 loops=1)
-> Subquery Scan "*SELECT* 1"
(cost=153181.39..159900.66 rows=2250 width=4) (actual
time=224.981..700.687 rows=2116 loops=1)
-> Hash Join
(cost=153181.39..159878.16 rows=2250 width=4) (actual
time=224.975..696.639 rows=2116 loops=1)
Hash Cond: ("outer".objectid =
"inner".id)
-> Seq Scan on
r_comment_rubric_user_object (cost=0.00..5144.18 rows=306018 width=4)
(actual time=0.021..405.881 rows=306392 loops=1)
-> Hash
(cost=153072.40..153072.40 rows=43595 width=4) (actual
time=32.311..32.311 rows=0 loops=1)
-> Nested Loop
(cost=15.00..153072.40 rows=43595 width=4) (actual time=0.554..29.762
rows=2033 loops=1)
-> Nested Loop
(cost=15.00..16071.65 rows=3412 width=4) (actual time=0.512..3.657
rows=180 loops=1)
-> Nested
Loop (cost=15.00..3769.73 rows=1666 width=4) (actual time=0.452..0.943
rows=50 loops=1)
->
HashAggregate (cost=15.00..15.00 rows=200 width=4) (actual
time=0.388..0.394 rows=1 loops=1)
->
Function Scan on children_of acts (cost=0.00..12.50 rows=1000
width=4) (actual time=0.376..0.377 rows=1 loops=1)
->
Index Scan using m_class_account_idx on m_class (cost=0.00..18.67
rows=8 width=8) (actual time=0.057..0.416 rows=50 loops=1)
Index Cond: (m_class.account = "outer".acts)
-> Index Scan
using m_assignment_class_idx on m_assignment (cost=0.00..7.25 rows=11
width=8) (actual time=0.023..0.043 rows=4 loops=50)
Index
Cond: (m_assignment."class" = "outer".id)
-> Index Scan using
m_object_paper_assignment_idx on m_object_paper (cost=0.00..39.24
rows=73 width=8) (actual time=0.026..0.118 rows=11 loops=180)
Index Cond:
(m_object_paper."assignment" = "outer".id)
-> Subquery Scan "*SELECT* 2"
(cost=153181.39..154513.46 rows=465 width=4) (actual
time=54.883..140.747 rows=312 loops=1)
-> Hash Join
(cost=153181.39..154508.81 rows=465 width=4) (actual
time=54.875..140.161 rows=312 loops=1)
Hash Cond: ("outer".objectid =
"inner".id)
-> Seq Scan on
r_quickmark_user_object (cost=0.00..1006.85 rows=63185 width=4)
(actual time=0.007..70.446 rows=63268 loops=1)
-> Hash
(cost=153072.40..153072.40 rows=43595 width=4) (actual
time=17.633..17.
